pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Debugging --- ERROR: function does not exist
25+ messages / 5 participants
[nested] [flat]

* Debugging --- ERROR: function does not exist
@ 2026-10-25 11:50 Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:33 ` Re: Debugging --- ERROR: function does not exist David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 2 replies; 25+ messages in thread

From: Mark @ 2026-10-25 11:50 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Hi there

I am getting the following error which has been driving me crazy:
ERROR:  function getsessdbkey(text, text, text, bigint, customType, text)
does not exist

This function is being called within another function, where I have defined
the "OUT variables" within the DECLARE block
I have also tried reassigning variables so that the "IN variables" (so the
input variables to the child function getsessdbkey" are also within the
DECLARE block [I read something about passing memory locations])

The function does exist and has the top level creation syntax as:
CREATE OR REPLACE FUNCTION getSessDbKey (IN iSessionId text, IN iUser text,
IN iFunction text, OUT oSessDbKey BIGINT, OUT oSessRet customType, OUT
retSessDbMess TEXT )
RETURNS record

If I run \df getsSessDbKey I get:
Schema |     Name     | Result data type |
                                                Argument data types
                                                | Type
--------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------+------
public | getsessdbkey | record           | isessionid text, iuser text,
ifunction text, OUT osessdbkey bigint, OUT osessret customtype, OUT
retsessdbmess text | func

I have have been able to run this getsessdbkey function from the psql
command line using the following syntax:
SELECT getSessDbKey (iSessionId => '1234'::text, iUser => 'cmd test'::text,
iFunction => 'cmd test'::text);

and when calling this function within the parent function I have tried
CASTING all the variables, but I still get the ERROR function does not
exist error message

Any pointers into how I can find out more about this error or hints on how
to resolve this would be heavily appreciated?

Many thanks in advance

Mark

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-25 13:00 ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 05:18   ` Re: Debugging --- ERROR: function does not exist rob stone <floriparob@tpg.com.au>
  1 sibling, 2 replies; 25+ messages in thread

From: Tom Lane @ 2026-10-25 13:00 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-25 13:26   ` Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Mark @ 2026-10-25 13:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

Thanks for the input Tom

1) SHOW search_path; -- produces the following and the function was in the
public schema (and both created by the same user)

  search_path
-----------------
"$user", public

2) I was calling it with:
SELECT getSessDbKey (jSessionId, jUser, 'functionName'::text, vSessDbKey,
vSessRet, vGetSessDbKeyMess);

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

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

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-25 14:42     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:54       ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  0 siblings, 2 replies; 25+ messages in thread

From: Adrian Klaver @ 2026-10-25 14:42 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

On 10/25/26 6:26 AM, Mark wrote:
> Thanks for the input Tom
> 
> 1) SHOW search_path; -- produces the following and the function was in 
> the public schema (and both created by the same user)
> 
>    search_path
> -----------------
> "$user", public
> 
> 2) I was calling it with:
> SELECTgetSessDbKey (jSessionId, jUser, 'functionName'::text, vSessDbKey, 
> vSessRet, vGetSessDbKeyMess);

This is not the same as:

SELECT getSessDbKey (iSessionId => '1234'::text, iUser => 'cmd 
test'::text, iFunction => 'cmd test'::text);

Note 3 parameters vs 6 in the 2).

And from here:

https://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: ..."

> 
> 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>> wrote:
> 
>     Mark <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





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-25 14:54       ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 25+ messages in thread

From: Adrian Klaver @ 2026-10-25 14:54 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general@lists.postgresql.org

On 10/25/26 7:42 AM, Adrian Klaver wrote:
> On 10/25/26 6:26 AM, Mark wrote:
>> Thanks for the input Tom
>>
>> 1) SHOW search_path; -- produces the following and the function was in 
>> the public schema (and both created by the same user)
>>
>>    search_path
>> -----------------
>> "$user", public
>>
>> 2) I was calling it with:
>> SELECTgetSessDbKey (jSessionId, jUser, 'functionName'::text, 
>> vSessDbKey, vSessRet, vGetSessDbKeyMess);
> 
> This is not the same as:
> 
> SELECT getSessDbKey (iSessionId => '1234'::text, iUser => 'cmd 
> test'::text, iFunction => 'cmd test'::text);
> 
> Note 3 parameters vs 6 in the 2).
> 
> And from here:
> 
> https://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: ..."
> 
Forgot to add there is INOUT if you want to use the parameters either 
way, see:

https://www.postgresql.org/docs/current/sql-createfunction.html

"argmode

     The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, 
the default is IN. Only OUT arguments can follow a VARIADIC one. Also, 
OUT and INOUT arguments cannot be used together with the RETURNS TABLE 
notation.
"

-- 
Adrian Klaver
adrian.klaver@aklaver.com





^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-25 14:55       ` Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Mark @ 2026-10-25 14:55 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

Hi Adrian

Thanks for the input

On Sun, Oct 25, 2026 at 2:42 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 10/25/26 6:26 AM, Mark wrote:
> > Thanks for the input Tom
> >
> > 1) SHOW search_path; -- produces the following and the function was in
> > the public schema (and both created by the same user)
> >
> >    search_path
> > -----------------
> > "$user", public
> >
> > 2) I was calling it with:
> > SELECTgetSessDbKey (jSessionId, jUser, 'functionName'::text, vSessDbKey,
> > vSessRet, vGetSessDbKeyMess);
>
> This is not the same as:
>
> SELECT getSessDbKey (iSessionId => '1234'::text, iUser => 'cmd
> test'::text, iFunction => 'cmd test'::text);
>
> Note 3 parameters vs 6 in the 2).
>
> And from here:
>
>
> https://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)


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>> wrote:
> >
> >     Mark <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
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-25 15:17         ` Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Adrian Klaver @ 2026-10-25 15:17 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

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

> 
>     And from here:
> 
>     https://www.postgresql.org/docs/current/plpgsql-
>     declarations.html#PLPGSQL-DECLARATION-PARAMETERS <https://
>     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.


> 
> 
> 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>>> wrote:
>      >
>      >     Mark <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>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-25 15:34           ` Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Mark @ 2026-10-25 15:34 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

On Sun, Oct 25, 2026 at 3:17 PM Adrian Klaver <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>> wrote:
>
> >
> >     And from here:
> >
> >     https://www.postgresql.org/docs/current/plpgsql-
> >     declarations.html#PLPGSQL-DECLARATION-PARAMETERS <https://
> >     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 | 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?

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

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


> >
> >
> > 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>>> wrote:
> >      >
> >      >     Mark <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>
> >
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-25 15:49             ` Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 25+ messages in thread

From: Adrian Klaver @ 2026-10-25 15:49 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-25 18:00               ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Tom Lane @ 2026-10-25 18:00 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Mark <nvpostgres@gmail.com>; pgsql-general@lists.postgresql.org

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/25/26 8:34 AM, Mark wrote:
>      "To call a function with OUT parameters, omit the output
>      parameter(s) in
>      the function call: ..."

That's what you do in SELECT, but in CALL you write all six
arguments (and the ones corresponding to OUT parameters
have to be variables).

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-25 19:55                 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 25+ messages in thread

From: Adrian Klaver @ 2026-10-25 19:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark <nvpostgres@gmail.com>; pgsql-general@lists.postgresql.org

On 10/25/26 11:00 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 10/25/26 8:34 AM, Mark wrote:
>>       "To call a function with OUT parameters, omit the output
>>       parameter(s) in
>>       the function call: ..."
> 
> That's what you do in SELECT, but in CALL you write all six
> arguments (and the ones corresponding to OUT parameters
> have to be variables).

 From here:

https://www.postgresql.org/docs/current/sql-call.html

"CALL executes a procedure."

"To call a function (not a procedure), use SELECT instead."

So is the problem down to the OP defining a function not a procedure and 
therefore not being able to use CALL to get at the OUT parameters?

> 
> 			regards, tom lane


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-25 20:19                   ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Tom Lane @ 2026-10-25 20:19 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Mark <nvpostgres@gmail.com>; pgsql-general@lists.postgresql.org

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> So is the problem down to the OP defining a function not a procedure and 
> therefore not being able to use CALL to get at the OUT parameters?

Yeah.  I was thinking that plpgsql's version of CALL was more
forgiving of that, but I was wrong: it too wants the target to be
declared as a procedure.

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-25 20:55                     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Adrian Klaver @ 2026-10-25 20:55 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark <nvpostgres@gmail.com>; pgsql-general@lists.postgresql.org

On 10/25/26 1:19 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> So is the problem down to the OP defining a function not a procedure and
>> therefore not being able to use CALL to get at the OUT parameters?
> 
> Yeah.  I was thinking that plpgsql's version of CALL was more
> forgiving of that, but I was wrong: it too wants the target to be
> declared as a procedure.

Alright to get it straight in my head and maybe of use to OP:

CREATE OR REPLACE PROCEDURE public.call_test(
IN a character varying, IN b character varying,
OUT x integer, OUT y integer)
  LANGUAGE plpgsql
AS $procedure$
BEGIN
     x = a::int + b::int;
     y = a::int - b::int;
END;
$procedure$
;


CREATE OR REPLACE FUNCTION public.select_test(
a character varying, b character varying,
OUT x integer, OUT y integer)
  RETURNS record
  LANGUAGE plpgsql
AS $function$
BEGIN
     x = a::int + b::int;
     y = a::int - b::int;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.combined_test(
a character varying, b character varying)
  RETURNS void
  LANGUAGE plpgsql
AS $function$
DECLARE
     x int;
     y int;
     r record;
BEGIN
     SELECT INTO r  * FROM select_test(a, b);
     RAISE NOTICE '%', r;
     --CALL call_test(a, b, x, y);
     --RAISE NOTICE '%, %', x, y;

END;
$function$
;

When combined_test is run as above using the SELECT then:

select combined_test('1', '2');
NOTICE:  (3,-1)
  combined_test
---------------

When run as CALL:

CREATE OR REPLACE FUNCTION public.combined_test(
a character varying, b character varying)
  RETURNS void
  LANGUAGE plpgsql
AS $function$
DECLARE
     x int;
     y int;
     r record;
BEGIN
     --SELECT INTO r  * FROM select_test(a, b);
     --RAISE NOTICE '%', r;
     CALL call_test(a, b, x, y);
     RAISE NOTICE '%, %', x, y;

END;
$function$
;


select combined_test('1', '2');
NOTICE:  3, -1
  combined_test
---------------

> 
> 			regards, tom lane


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-26 10:49                       ` Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Mark @ 2026-10-26 10:49 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

On Sun, Oct 25, 2026 at 8:55 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 10/25/26 1:19 PM, Tom Lane wrote:
> > Adrian Klaver <adrian.klaver@aklaver.com> writes:
> >> So is the problem down to the OP defining a function not a procedure and
> >> therefore not being able to use CALL to get at the OUT parameters?
> >
> > Yeah.  I was thinking that plpgsql's version of CALL was more
> > forgiving of that, but I was wrong: it too wants the target to be
> > declared as a procedure.
>
> Alright to get it straight in my head and maybe of use to OP:
>
> CREATE OR REPLACE PROCEDURE public.call_test(
> IN a character varying, IN b character varying,
> OUT x integer, OUT y integer)
>   LANGUAGE plpgsql
> AS $procedure$
> BEGIN
>      x = a::int + b::int;
>      y = a::int - b::int;
> END;
> $procedure$
> ;
>
>
> CREATE OR REPLACE FUNCTION public.select_test(
> a character varying, b character varying,
> OUT x integer, OUT y integer)
>   RETURNS record
>   LANGUAGE plpgsql
> AS $function$
> BEGIN
>      x = a::int + b::int;
>      y = a::int - b::int;
> END;
> $function$
> ;
>
> CREATE OR REPLACE FUNCTION public.combined_test(
> a character varying, b character varying)
>   RETURNS void
>   LANGUAGE plpgsql
> AS $function$
> DECLARE
>      x int;
>      y int;
>      r record;
> BEGIN
>      SELECT INTO r  * FROM select_test(a, b);
>      RAISE NOTICE '%', r;
>      --CALL call_test(a, b, x, y);
>      --RAISE NOTICE '%, %', x, y;
>
> END;
> $function$
> ;
>
> When combined_test is run as above using the SELECT then:
>
> select combined_test('1', '2');
> NOTICE:  (3,-1)
>   combined_test
> ---------------
>
> When run as CALL:
>
> CREATE OR REPLACE FUNCTION public.combined_test(
> a character varying, b character varying)
>   RETURNS void
>   LANGUAGE plpgsql
> AS $function$
> DECLARE
>      x int;
>      y int;
>      r record;
> BEGIN
>      --SELECT INTO r  * FROM select_test(a, b);
>      --RAISE NOTICE '%', r;
>      CALL call_test(a, b, x, y);
>      RAISE NOTICE '%, %', x, y;
>
> END;
> $function$
> ;
>
>
> select combined_test('1', '2');
> NOTICE:  3, -1
>   combined_test
> ---------------
>
>
Yes thanks Tom and Adrian

Adrian

I eventually got what you were hinting at, I should INOUT to supply the
parameters at part of the CALL, though I feel like this is not a good idea
unless actually required from a security perspective (though I acknowledge
this will be a trivial difference)

Tom

I reimplemented as a PROCEDURE and used CALL and that worked, many thanks

A sort of tangential question, how do I test a PROCEDURE with OUT
parameters from the psql command line?
Do I have to create in session variables and then CALL the proc, and then
select the variable?

I had a scaler (the custom type so had to use this method)

I could not use the method suggest by David Johnston above, but I may have
been able to use the example from Adiran above

Mark


> >
> >                       regards, tom lane
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-26 10:56                         ` Mark <nvpostgres@gmail.com>
  2026-10-26 11:30                           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 14:43                           ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 2 replies; 25+ messages in thread

From: Mark @ 2026-10-26 10:56 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

Correction for anybody who reads this later:

Adrian

I eventually got what you were hinting at, I should INOUT to supply the
parameters at part of the SELECT, though I feel like this is not a good
idea unless actually required from a security perspective (though I
acknowledge this will be a trivial difference)


On Mon, Oct 26, 2026 at 10:49 AM Mark <nvpostgres@gmail.com> wrote:

>
>
> On Sun, Oct 25, 2026 at 8:55 PM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> On 10/25/26 1:19 PM, Tom Lane wrote:
>> > Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> >> So is the problem down to the OP defining a function not a procedure
>> and
>> >> therefore not being able to use CALL to get at the OUT parameters?
>> >
>> > Yeah.  I was thinking that plpgsql's version of CALL was more
>> > forgiving of that, but I was wrong: it too wants the target to be
>> > declared as a procedure.
>>
>> Alright to get it straight in my head and maybe of use to OP:
>>
>> CREATE OR REPLACE PROCEDURE public.call_test(
>> IN a character varying, IN b character varying,
>> OUT x integer, OUT y integer)
>>   LANGUAGE plpgsql
>> AS $procedure$
>> BEGIN
>>      x = a::int + b::int;
>>      y = a::int - b::int;
>> END;
>> $procedure$
>> ;
>>
>>
>> CREATE OR REPLACE FUNCTION public.select_test(
>> a character varying, b character varying,
>> OUT x integer, OUT y integer)
>>   RETURNS record
>>   LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>>      x = a::int + b::int;
>>      y = a::int - b::int;
>> END;
>> $function$
>> ;
>>
>> CREATE OR REPLACE FUNCTION public.combined_test(
>> a character varying, b character varying)
>>   RETURNS void
>>   LANGUAGE plpgsql
>> AS $function$
>> DECLARE
>>      x int;
>>      y int;
>>      r record;
>> BEGIN
>>      SELECT INTO r  * FROM select_test(a, b);
>>      RAISE NOTICE '%', r;
>>      --CALL call_test(a, b, x, y);
>>      --RAISE NOTICE '%, %', x, y;
>>
>> END;
>> $function$
>> ;
>>
>> When combined_test is run as above using the SELECT then:
>>
>> select combined_test('1', '2');
>> NOTICE:  (3,-1)
>>   combined_test
>> ---------------
>>
>> When run as CALL:
>>
>> CREATE OR REPLACE FUNCTION public.combined_test(
>> a character varying, b character varying)
>>   RETURNS void
>>   LANGUAGE plpgsql
>> AS $function$
>> DECLARE
>>      x int;
>>      y int;
>>      r record;
>> BEGIN
>>      --SELECT INTO r  * FROM select_test(a, b);
>>      --RAISE NOTICE '%', r;
>>      CALL call_test(a, b, x, y);
>>      RAISE NOTICE '%, %', x, y;
>>
>> END;
>> $function$
>> ;
>>
>>
>> select combined_test('1', '2');
>> NOTICE:  3, -1
>>   combined_test
>> ---------------
>>
>>
> Yes thanks Tom and Adrian
>
> Adrian
>
> I eventually got what you were hinting at, I should INOUT to supply the
> parameters at part of the CALL, though I feel like this is not a good idea
> unless actually required from a security perspective (though I acknowledge
> this will be a trivial difference)
>
> Tom
>
> I reimplemented as a PROCEDURE and used CALL and that worked, many thanks
>
> A sort of tangential question, how do I test a PROCEDURE with OUT
> parameters from the psql command line?
> Do I have to create in session variables and then CALL the proc, and then
> select the variable?
>
> I had a scaler (the custom type so had to use this method)
>
> I could not use the method suggest by David Johnston above, but I may have
> been able to use the example from Adiran above
>
> Mark
>
>
>> >
>> >                       regards, tom lane
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-26 11:30                           ` Mark <nvpostgres@gmail.com>
  2026-10-26 13:47                             ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 25+ messages in thread

From: Mark @ 2026-10-26 11:30 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

and a correction for Tom!

Tom

I reimplemented as a PROCEDURE and used CALL and that worked, many thanks

A sort of tangential question, how do I test a PROCEDURE with OUT
parameters from the psql command line?
Do I have to create in session variables and then CALL the proc, and then
select the variable?

I could not use the method suggest by David Johnston above (as the
customType was not a scalar), but I may have been able to use the example
from Adiran above


On Mon, Oct 26, 2026 at 10:56 AM Mark <nvpostgres@gmail.com> wrote:

> Correction for anybody who reads this later:
>
> Adrian
>
> I eventually got what you were hinting at, I should INOUT to supply the
> parameters at part of the SELECT, though I feel like this is not a good
> idea unless actually required from a security perspective (though I
> acknowledge this will be a trivial difference)
>
>
> On Mon, Oct 26, 2026 at 10:49 AM Mark <nvpostgres@gmail.com> wrote:
>
>>
>>
>> On Sun, Oct 25, 2026 at 8:55 PM Adrian Klaver <adrian.klaver@aklaver.com>
>> wrote:
>>
>>> On 10/25/26 1:19 PM, Tom Lane wrote:
>>> > Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>> >> So is the problem down to the OP defining a function not a procedure
>>> and
>>> >> therefore not being able to use CALL to get at the OUT parameters?
>>> >
>>> > Yeah.  I was thinking that plpgsql's version of CALL was more
>>> > forgiving of that, but I was wrong: it too wants the target to be
>>> > declared as a procedure.
>>>
>>> Alright to get it straight in my head and maybe of use to OP:
>>>
>>> CREATE OR REPLACE PROCEDURE public.call_test(
>>> IN a character varying, IN b character varying,
>>> OUT x integer, OUT y integer)
>>>   LANGUAGE plpgsql
>>> AS $procedure$
>>> BEGIN
>>>      x = a::int + b::int;
>>>      y = a::int - b::int;
>>> END;
>>> $procedure$
>>> ;
>>>
>>>
>>> CREATE OR REPLACE FUNCTION public.select_test(
>>> a character varying, b character varying,
>>> OUT x integer, OUT y integer)
>>>   RETURNS record
>>>   LANGUAGE plpgsql
>>> AS $function$
>>> BEGIN
>>>      x = a::int + b::int;
>>>      y = a::int - b::int;
>>> END;
>>> $function$
>>> ;
>>>
>>> CREATE OR REPLACE FUNCTION public.combined_test(
>>> a character varying, b character varying)
>>>   RETURNS void
>>>   LANGUAGE plpgsql
>>> AS $function$
>>> DECLARE
>>>      x int;
>>>      y int;
>>>      r record;
>>> BEGIN
>>>      SELECT INTO r  * FROM select_test(a, b);
>>>      RAISE NOTICE '%', r;
>>>      --CALL call_test(a, b, x, y);
>>>      --RAISE NOTICE '%, %', x, y;
>>>
>>> END;
>>> $function$
>>> ;
>>>
>>> When combined_test is run as above using the SELECT then:
>>>
>>> select combined_test('1', '2');
>>> NOTICE:  (3,-1)
>>>   combined_test
>>> ---------------
>>>
>>> When run as CALL:
>>>
>>> CREATE OR REPLACE FUNCTION public.combined_test(
>>> a character varying, b character varying)
>>>   RETURNS void
>>>   LANGUAGE plpgsql
>>> AS $function$
>>> DECLARE
>>>      x int;
>>>      y int;
>>>      r record;
>>> BEGIN
>>>      --SELECT INTO r  * FROM select_test(a, b);
>>>      --RAISE NOTICE '%', r;
>>>      CALL call_test(a, b, x, y);
>>>      RAISE NOTICE '%, %', x, y;
>>>
>>> END;
>>> $function$
>>> ;
>>>
>>>
>>> select combined_test('1', '2');
>>> NOTICE:  3, -1
>>>   combined_test
>>> ---------------
>>>
>>>
>> Yes thanks Tom and Adrian
>>
>> Adrian
>>
>> I eventually got what you were hinting at, I should INOUT to supply the
>> parameters at part of the CALL, though I feel like this is not a good idea
>> unless actually required from a security perspective (though I acknowledge
>> this will be a trivial difference)
>>
>> Tom
>>
>> I reimplemented as a PROCEDURE and used CALL and that worked, many thanks
>>
>> A sort of tangential question, how do I test a PROCEDURE with OUT
>> parameters from the psql command line?
>> Do I have to create in session variables and then CALL the proc, and then
>> select the variable?
>>
>> I had a scaler (the custom type so had to use this method)
>>
>> I could not use the method suggest by David Johnston above, but I may
>> have been able to use the example from Adiran above
>>
>> Mark
>>
>>
>>> >
>>> >                       regards, tom lane
>>>
>>>
>>> --
>>> Adrian Klaver
>>> adrian.klaver@aklaver.com
>>>
>>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 11:30                           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-26 13:47                             ` Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-26 15:21                               ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Tom Lane @ 2026-10-26 13:47 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org

Mark <nvpostgres@gmail.com> writes:
> A sort of tangential question, how do I test a PROCEDURE with OUT
> parameters from the psql command line?

See the CALL man page, but IIRC it's "write NULL for each OUT
parameter".

			regards, tom lane






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 11:30                           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 13:47                             ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-26 15:21                               ` Mark <nvpostgres@gmail.com>
  0 siblings, 0 replies; 25+ messages in thread

From: Mark @ 2026-10-26 15:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org

On Mon, Oct 26, 2026 at 1:47 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Mark <nvpostgres@gmail.com> writes:
> > A sort of tangential question, how do I test a PROCEDURE with OUT
> > parameters from the psql command line?
>
> See the CALL man page, but IIRC it's "write NULL for each OUT
> parameter".
>
>                         regards, tom lane
>

Thanks Tom,

Yes you were right NULL must supplied for OUT parameters when CALLing
PROCEDURES

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-26 14:43                           ` Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 15:26                             ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  1 sibling, 1 reply; 25+ messages in thread

From: Adrian Klaver @ 2026-10-26 14:43 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

On 10/26/26 3:56 AM, Mark wrote:
> Correction for anybody who reads this later:
> 
> Adrian
> 
> I eventually got what you were hinting at, I should INOUT to supply the 
> parameters at part of the SELECT, though I feel like this is not a good 
> idea unless actually required from a security perspective (though I 
> acknowledge this will be a trivial difference)

Given that OUT is basically an alternate form of RETURNS why do you want 
to supply the OUT values to the function/procedure SELECT/CALL in the 
first place?

That would be something the function/procedure does internally using the 
IN values, where IN is either explicitly stated or implied by a lack of 
an IN/OUT/INOUT declaration.



-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 14:43                           ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
@ 2026-10-26 15:26                             ` Mark <nvpostgres@gmail.com>
  2026-10-26 16:06                               ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 25+ messages in thread

From: Mark @ 2026-10-26 15:26 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

> Given that OUT is basically an alternate form of RETURNS why do you want
> to supply the OUT values to the function/procedure SELECT/CALL in the
> first place?
>
> That would be something the function/procedure does internally using the
> IN values, where IN is either explicitly stated or implied by a lack of
> an IN/OUT/INOUT declaration.
>
>
Honestly I don't know at the moment, guess it is a stylistic choice, along
with the choice to use a PROCEDURE vs a FUNCTION (which I do not know that
much about, other than what I have learned in this thread)

I guess with the RETURNS it make the DECLARE section cleaner and with the
PROCEDURE it make the body of the code cleaner

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 13:26   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 14:42     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 14:55       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:17         ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 15:34           ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 15:49             ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 18:00               ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 19:55                 ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-25 20:19                   ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-25 20:55                     ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 10:49                       ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 10:56                         ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-26 14:43                           ` Re: Debugging --- ERROR: function does not exist Adrian Klaver <adrian.klaver@aklaver.com>
  2026-10-26 15:26                             ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-26 16:06                               ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 25+ messages in thread

From: Adrian Klaver @ 2026-10-26 16:06 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org



On 10/26/26 8:26 AM, Mark wrote:
> 
>     Given that OUT is basically an alternate form of RETURNS why do you
>     want
>     to supply the OUT values to the function/procedure SELECT/CALL in the
>     first place?
> 
>     That would be something the function/procedure does internally using
>     the
>     IN values, where IN is either explicitly stated or implied by a lack of
>     an IN/OUT/INOUT declaration.
> 
> 
> Honestly I don't know at the moment, guess it is a stylistic choice, 
> along with the choice to use a PROCEDURE vs a FUNCTION (which I do not 
> know that much about, other than what I have learned in this thread)

Which is only part of the story. For a more complete picture see:

https://www.postgresql.org/docs/current/xproc.html

> 
> I guess with the RETURNS it make the DECLARE section cleaner and with 
> the PROCEDURE it make the body of the code cleaner

Well the important part from link above is:

"Procedures do not return a function value; hence CREATE PROCEDURE lacks 
a RETURNS clause. However, procedures can instead return data to their 
callers via output parameters."










^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2026-10-26 05:18   ` rob stone <floriparob@tpg.com.au>
  2026-10-26 10:38     ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  1 sibling, 1 reply; 25+ messages in thread

From: rob stone @ 2026-10-26 05:18 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Mark <nvpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Hello Mark,
On Sun, 2026-10-25 at 09:00 -0400, Tom Lane wrote:
> Mark <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
> 

ERROR:  function getsessdbkey(text, text, text, bigint, customType,
text) does not exist

When you created customType does it use any functions, and if so are
they in the public schema?

Regards,
Rob







^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 13:00 ` Re: Debugging --- ERROR: function does not exist Tom Lane <tgl@sss.pgh.pa.us>
  2026-10-26 05:18   ` Re: Debugging --- ERROR: function does not exist rob stone <floriparob@tpg.com.au>
@ 2026-10-26 10:38     ` Mark <nvpostgres@gmail.com>
  0 siblings, 0 replies; 25+ messages in thread

From: Mark @ 2026-10-26 10:38 UTC (permalink / raw)
  To: rob stone <floriparob@tpg.com.au>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org

On Mon, Oct 26, 2026 at 5:18 AM rob stone <floriparob@tpg.com.au> wrote:

> Hello Mark,
> On Sun, 2026-10-25 at 09:00 -0400, Tom Lane wrote:
> > Mark <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
> >
>
> ERROR:  function getsessdbkey(text, text, text, bigint, customType,
> text) does not exist
>
> When you created customType does it use any functions, and if so are
> they in the public schema?
>
> The customType is a number, text, and a few JSONs. There are no special
functions and it is in the public schema, I think I have now found the root
of my problems (which will appear elsewhere in this thread), thanks for
your comments

I like to separate code and data (except for non trivial examples) where
possible



> Regards,
> Rob
>
>

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
@ 2026-10-25 20:33 ` David G. Johnston <david.g.johnston@gmail.com>
  2026-10-26 10:52   ` Re: Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  1 sibling, 1 reply; 25+ messages in thread

From: David G. Johnston @ 2026-10-25 20:33 UTC (permalink / raw)
  To: Mark <nvpostgres@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Sun, Oct 25, 2026 at 4:50 AM Mark <nvpostgres@gmail.com> wrote:

> If I run \df getsSessDbKey I get:
> Schema |     Name     | Result data type |
>                                                 Argument data types
>                                                 | Type
>
> --------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------+------
> public | getsessdbkey | record           | isessionid text, iuser text,
> ifunction text, OUT osessdbkey bigint, OUT osessret customtype, OUT
> retsessdbmess text | func
>

> Any pointers into how I can find out more about this error or hints on how
> to resolve this would be heavily appreciated?
>

Show the code you actually wrote.  If you cannot do that, write similar
code that produces the same error and show that.

For that function you'd probably write something like:

select osessdbkey, osessret, retsessdbmess
into var1, var2, var3
from getsSessDbKey(isessionid, iuser, ifunction);

to execute the function and capture the three output fields.

David J.

P.S. As for the 'memory locations' comment - that seems like something you
misunderstood and thus acted upon unnecessarily.

^ permalink  raw  reply  [nested|flat] 25+ messages in thread

* Re: Debugging --- ERROR: function does not exist
  2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
  2026-10-25 20:33 ` Re: Debugging --- ERROR: function does not exist David G. Johnston <david.g.johnston@gmail.com>
@ 2026-10-26 10:52   ` Mark <nvpostgres@gmail.com>
  0 siblings, 0 replies; 25+ messages in thread

From: Mark @ 2026-10-26 10:52 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Sun, Oct 25, 2026 at 8:34 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Sun, Oct 25, 2026 at 4:50 AM Mark <nvpostgres@gmail.com> wrote:
>
>> If I run \df getsSessDbKey I get:
>> Schema |     Name     | Result data type |
>>                                                 Argument data types
>>                                                 | Type
>>
>> --------+--------------+------------------+--------------------------------------------------------------------------------------------------------------------+------
>> public | getsessdbkey | record           | isessionid text, iuser text,
>> ifunction text, OUT osessdbkey bigint, OUT osessret customtype, OUT
>> retsessdbmess text | func
>>
>
>> Any pointers into how I can find out more about this error or hints on
>> how to resolve this would be heavily appreciated?
>>
>
> Show the code you actually wrote.  If you cannot do that, write similar
> code that produces the same error and show that.
>
> For that function you'd probably write something like:
>
> select osessdbkey, osessret, retsessdbmess
> into var1, var2, var3
> from getsSessDbKey(isessionid, iuser, ifunction);
>
> to execute the function and capture the three output fields.
>
> David J.
>
> P.S. As for the 'memory locations' comment - that seems like something you
> misunderstood and thus acted upon unnecessarily.
>
>
> Thanks David for your input

I got it working from the comments by Tom and Adrian, I could not use the
example you provided due to the customType not being a scalar (it is a
number, text field, and a few JSONs)

Thanks for the comment on memory locations, yes that seems to be a red
herring, do you have any thing you can point to on memory locations (in
this context) that I can read, or it is just not necessary to consider
overall

Mark

^ permalink  raw  reply  [nested|flat] 25+ messages in thread


end of thread, other threads:[~2026-10-26 16:06 UTC | newest]

Thread overview: 25+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-25 11:50 Debugging --- ERROR: function does not exist Mark <nvpostgres@gmail.com>
2026-10-25 13:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-25 13:26   ` Mark <nvpostgres@gmail.com>
2026-10-25 14:42     ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-25 14:54       ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-25 14:55       ` Mark <nvpostgres@gmail.com>
2026-10-25 15:17         ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-25 15:34           ` Mark <nvpostgres@gmail.com>
2026-10-25 15:49             ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-25 18:00               ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-25 19:55                 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-25 20:19                   ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-25 20:55                     ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-26 10:49                       ` Mark <nvpostgres@gmail.com>
2026-10-26 10:56                         ` Mark <nvpostgres@gmail.com>
2026-10-26 11:30                           ` Mark <nvpostgres@gmail.com>
2026-10-26 13:47                             ` Tom Lane <tgl@sss.pgh.pa.us>
2026-10-26 15:21                               ` Mark <nvpostgres@gmail.com>
2026-10-26 14:43                           ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-26 15:26                             ` Mark <nvpostgres@gmail.com>
2026-10-26 16:06                               ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-10-26 05:18   ` rob stone <floriparob@tpg.com.au>
2026-10-26 10:38     ` Mark <nvpostgres@gmail.com>
2026-10-25 20:33 ` David G. Johnston <david.g.johnston@gmail.com>
2026-10-26 10:52   ` Mark <nvpostgres@gmail.com>

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