| 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
| 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 |