Debugging --- ERROR: function does not exist
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
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
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
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", public2) 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
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", public2) 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
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", public2) 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
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 differenceMark
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
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 existbecause 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 differenceMark
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
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:
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 werereturned
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 existbecause 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 | funcwith 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 differenceMark
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
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
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
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
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.
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
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
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 existWhen 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
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
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 | funcAny 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
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 aboveMark
regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com
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 aboveMark
regards, tom lane
--
Adrian Klaver
adrian.klaver@aklaver.com