| From: | Mark <nvpostgres(at)gmail(dot)com> |
|---|---|
| To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Debugging --- ERROR: function does not exist |
| Date: | 2026-10-26 11:30:49 |
| Message-ID: | CAAm-KOt69JY5te-peoC3mhiiP4K6KKanw-y6VS3UskotR2K_sA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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(at)gmail(dot)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(at)gmail(dot)com> wrote:
>
>>
>>
>> On Sun, Oct 25, 2026 at 8:55 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> wrote:
>>
>>> On 10/25/26 1:19 PM, Tom Lane wrote:
>>> > Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>>
>>
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2026-10-26 13:47:05 | Re: Debugging --- ERROR: function does not exist |
| Previous Message | Mark | 2026-10-26 10:56:31 | Re: Debugging --- ERROR: function does not exist |