Re: Debugging --- ERROR: function does not exist

From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
To: Mark <nvpostgres(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Debugging --- ERROR: function does not exist
Date: 2026-10-25 15:49:43
Message-ID: 75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 10/25/26 8:34 AM, Mark wrote:
>
> On Sun, Oct 25, 2026 at 3:17 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 10/25/26 7:55 AM, Mark wrote:
> > Hi Adrian
> >
> > Thanks for the input
> >
> > On Sun, Oct 25, 2026 at 2:42 PM Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>> wrote:
>
> >
> >     And from here:
> >
> > https://www.postgresql.org/docs/current/plpgsql- <https://
> www.postgresql.org/docs/current/plpgsql->
> >     declarations.html#PLPGSQL-DECLARATION-PARAMETERS <https://
> > www.postgresql.org/docs/current/plpgsql-
> declarations.html#PLPGSQL- <http://www.postgresql.org/docs/current/
> plpgsql-declarations.html#PLPGSQL->
> >     DECLARATION-PARAMETERS>
> >
> >     "To call a function with OUT parameters, omit the output
> >     parameter(s) in
> >     the function call: ..."
> >
> >
> > In the second case with 3 parameters:  SELECT getSessDbKey
> (iSessionId
> > => '1234'::text, iUser => ....
> > This was called from the psql command line, the OUT values were
> returned
> > in the terminal, example:
> > getsessdbkey
> > ---------------
> > (3,,keyFound)
>
> Which is the correct process.
>
> When you are calling(SELECTing) the function inside the other function
> you are doing:
>
> SELECT getSessDbKey (jSessionId, jUser, 'functionName'::text,
> vSessDbKey, vSessRet, vGetSessDbKeyMess);
>
> There six arguments to to the function.
>
> You get this:
>
> ERROR:  function getsessdbkey(text, text, text, bigint, customType,
> text) does not exist
>
> because your functions signature is:
>
> getsessdbkey(text, text, text)
>
> The OUT parameters are not exposed, so you only get to use three
> arguments to the function.
>
> If you want a parameter to be dual purpose then you can declare it
> INOUT.
>
> When calling the function (and when I get the error) this is from
> another function using the syntax:
> SELECT getSessDbKey (jSessionId, jUser, 'functionName'::text,
> vSessDbKey, vSessRet, vGetSessDbKeyMess);
>
> The function signature according to \df getSessDbKey is:
> Schema |     Name     | Result data type |
>                   Argument data types
>               | Type
> --------+--------------+------------------
> +---------------------------------------------------------------------------------------------------------------------+------
>  public | getsessdbkey Argument data types| record           | isessionid text, iuser
> text, ifunction text, OUT osessdbkey bigint, OUT osessret retmessage,
> OUT retsessdbmess text | func
>
> with 6 parameters, which is why the call has 6 parameters. If you are
> aware of a way to find the function signature, do please let me know?

The easiest way is to ignore everything in the column 'Argument data
types' that is declared OUT, what is left is the signature.

>
> For the command line call only 3 parameters were provided and the
> terminal listed the OUT 3 parameters I showed in my previous response

Which as I stated before is entirely correct.

>
> I do not have / need / have built around INOUT parameters

Then when you SELECT the getsessdbkey in the other function do exactly
as you did in the psql case, use only (isessionid text, iuser text,
ifunction text).

OUT is a shortcut to doing a RETURN of the variable, it does not figure
into the input of arguments to the function body.

>
> >
> >
> > Thanks for the link, for the 1st case I had defined the IN and OUT
> > parameters in the parent function, I have also just tried the:
> > parameterName ALIAS FOR $x , but that did not make a difference
> >
> > Mark
> >
> > Edit I have just seen your follow up message, there are no INOUT
> parameters
> >
> >      >
> >      > I just tried to user CALL and the HINT was to use SELECT
> >      >
> >      > Mark
> >      >
> >      > On Sun, Oct 25, 2026 at 1:00 PM Tom Lane
> <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
> >     <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>
> >      > <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>>>> wrote:
> >      >
> >      >     Mark <nvpostgres(at)gmail(dot)com
> <mailto:nvpostgres(at)gmail(dot)com> <mailto:nvpostgres(at)gmail(dot)com
> <mailto:nvpostgres(at)gmail(dot)com>>
> >     <mailto:nvpostgres(at)gmail(dot)com <mailto:nvpostgres(at)gmail(dot)com>
> <mailto:nvpostgres(at)gmail(dot)com <mailto:nvpostgres(at)gmail(dot)com>>>> writes:
> >      >      > I am getting the following error which has been
> driving me
> >     crazy:
> >      >      > ERROR:  function getsessdbkey(text, text, text, bigint,
> >      >     customType, text)
> >      >      > does not exist
> >      >
> >      >     (1) search_path problem, perhaps?
> >      >     (2) are you using a CALL statement to call it?
> >      >
> >      >                              regards, tom lane
> >      >
> >
> >
> >     --
> >     Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2026-10-25 18:00:20 Re: Debugging --- ERROR: function does not exist
Previous Message Mark 2026-10-25 15:34:37 Re: Debugging --- ERROR: function does not exist