pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Mark <nvpostgres@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Debugging --- ERROR: function does not exist
Date: Sun, 25 Oct 2026 08:49:43 -0700
Message-ID: <75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com> (raw)
In-Reply-To: <CAAm-KOtD7AC-L-=zjnz-0qpGVZKQcshEJ0kMdnJPFscqGsAHxA@mail.gmail.com>
References: <CAAm-KOsbEWVBjv6C80g-WgDF-87Wc90_4X+AWvSO=Fw1ps5=6g@mail.gmail.com>
	<322082.1792933209@sss.pgh.pa.us>
	<CAAm-KOvOnCw8YEMKh8NLvJoHzm2R3A3_xW=9ecf4+mdMV=xPEg@mail.gmail.com>
	<a34cf760-8d6c-49bb-be80-43c5b4a499ee@aklaver.com>
	<CAAm-KOtDssDv-qQ=FCP9A6J95k56x=VhQi=1mo5bBEkrStr3qg@mail.gmail.com>
	<ef3c60f8-f21b-4b30-bd2a-140b36fd3954@aklaver.com>
	<CAAm-KOtD7AC-L-=zjnz-0qpGVZKQcshEJ0kMdnJPFscqGsAHxA@mail.gmail.com>

On 10/25/26 8:34 AM, Mark wrote:
> 
> On Sun, Oct 25, 2026 at 3:17 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.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@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
>      >     <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>
>      >      > <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
>     <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>> wrote:
>      >      >
>      >      >     Mark <nvpostgres@gmail.com
>     <mailto:nvpostgres@gmail.com> <mailto:nvpostgres@gmail.com
>     <mailto:nvpostgres@gmail.com>>
>      >     <mailto:nvpostgres@gmail.com <mailto:nvpostgres@gmail.com>
>     <mailto:nvpostgres@gmail.com <mailto:nvpostgres@gmail.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com






view thread (25+ messages)  latest in thread

Message-ID: <75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com>
Permalink:  ../75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com/
Also on:    postgresql.org/message-id/75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: adrian.klaver@aklaver.com, nvpostgres@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: Debugging --- ERROR: function does not exist
  In-Reply-To: <75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com>
  References: <CAAm-KOsbEWVBjv6C80g-WgDF-87Wc90_4X+AWvSO=Fw1ps5=6g@mail.gmail.com> <322082.1792933209@sss.pgh.pa.us> <CAAm-KOvOnCw8YEMKh8NLvJoHzm2R3A3_xW=9ecf4+mdMV=xPEg@mail.gmail.com> <a34cf760-8d6c-49bb-be80-43c5b4a499ee@aklaver.com> <CAAm-KOtDssDv-qQ=FCP9A6J95k56x=VhQi=1mo5bBEkrStr3qg@mail.gmail.com> <ef3c60f8-f21b-4b30-bd2a-140b36fd3954@aklaver.com> <CAAm-KOtD7AC-L-=zjnz-0qpGVZKQcshEJ0kMdnJPFscqGsAHxA@mail.gmail.com> <75654f90-b8a6-42f7-b71a-2591c4e9bc0d@aklaver.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox