pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedDebugging --- 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>
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 13:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark <nvpostgres@gmail.com>
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 13:26 Mark <nvpostgres@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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 14:42 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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 14:54 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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 14:55 Mark <nvpostgres@gmail.com>
parent: 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 15:17 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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 15:34 Mark <nvpostgres@gmail.com>
parent: 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 15:49 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Mark <nvpostgres@gmail.com>
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 18:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 19:55 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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 20:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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 20:33 David G. Johnston <david.g.johnston@gmail.com>
parent: 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 20:55 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-26 05:18 rob stone <floriparob@tpg.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-26 10:38 Mark <nvpostgres@gmail.com>
parent: rob stone <floriparob@tpg.com.au>
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-26 10:49 Mark <nvpostgres@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.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-26 10:52 Mark <nvpostgres@gmail.com>
parent: David G. Johnston <david.g.johnston@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
* Re: Debugging --- ERROR: function does not exist
@ 2026-10-26 10:56 Mark <nvpostgres@gmail.com>
parent: Mark <nvpostgres@gmail.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-26 11:30 Mark <nvpostgres@gmail.com>
parent: Mark <nvpostgres@gmail.com>
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-26 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: 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-26 14:43 Adrian Klaver <adrian.klaver@aklaver.com>
parent: 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-26 15:21 Mark <nvpostgres@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-26 15:26 Mark <nvpostgres@gmail.com>
parent: 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-26 16:06 Adrian Klaver <adrian.klaver@aklaver.com>
parent: Mark <nvpostgres@gmail.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
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