Debugging --- ERROR: function does not exist

Started by Mark28 days ago25 messagesgeneral
Jump to latest
#1Mark
nvpostgres@gmail.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Mark (#1)
Re: Debugging --- ERROR: function does not exist

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

#3Mark
nvpostgres@gmail.com
In reply to: Tom Lane (#2)
Re: Debugging --- ERROR: function does not exist

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:

Show quoted text

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

#4Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Mark (#3)
Re: Debugging --- ERROR: function does not exist

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

#5Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Adrian Klaver (#4)
Re: Debugging --- ERROR: function does not exist

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

#6Mark
nvpostgres@gmail.com
In reply to: Adrian Klaver (#4)
Re: Debugging --- ERROR: function does not exist

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

Show quoted text

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

#7Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Mark (#6)
Re: Debugging --- ERROR: function does not exist

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

#8Mark
nvpostgres@gmail.com
In reply to: Adrian Klaver (#7)
Re: Debugging --- ERROR: function does not exist

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

Show quoted text

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

#9Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Mark (#8)
Re: Debugging --- ERROR: function does not exist

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

#10Tom Lane
tgl@sss.pgh.pa.us
In reply to: Adrian Klaver (#9)
Re: Debugging --- ERROR: function does not exist

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

#11Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Tom Lane (#10)
Re: Debugging --- ERROR: function does not exist

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

#12Tom Lane
tgl@sss.pgh.pa.us
In reply to: Adrian Klaver (#11)
Re: Debugging --- ERROR: function does not exist

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

#13David G. Johnston
david.g.johnston@gmail.com
In reply to: Mark (#1)
Re: Debugging --- ERROR: function does not exist

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.

#14Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Tom Lane (#12)
Re: Debugging --- ERROR: function does not exist

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

#15rob stone
floriparob@tpg.com.au
In reply to: Tom Lane (#2)
Re: Debugging --- ERROR: function does not exist

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

#16Mark
nvpostgres@gmail.com
In reply to: rob stone (#15)
Re: Debugging --- ERROR: function does not exist

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

Show quoted text

Regards,
Rob

#17Mark
nvpostgres@gmail.com
In reply to: Adrian Klaver (#14)
Re: Debugging --- ERROR: function does not exist

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

Show quoted text

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

#18Mark
nvpostgres@gmail.com
In reply to: David G. Johnston (#13)
Re: Debugging --- ERROR: function does not exist

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

#19Mark
nvpostgres@gmail.com
In reply to: Mark (#17)
Re: Debugging --- ERROR: function does not exist

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:

Show quoted text

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

#20Mark
nvpostgres@gmail.com
In reply to: Mark (#19)
Re: Debugging --- ERROR: function does not exist

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:

Show quoted text

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

#21Tom Lane
tgl@sss.pgh.pa.us
In reply to: Mark (#20)
#22Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Mark (#19)
#23Mark
nvpostgres@gmail.com
In reply to: Tom Lane (#21)
#24Mark
nvpostgres@gmail.com
In reply to: Adrian Klaver (#22)
#25Adrian Klaver
adrian.klaver@aklaver.com
In reply to: Mark (#24)