Re: Debugging --- ERROR: function does not exist

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 10:56:31
Message-ID: CAAm-KOvwfZVNLLSWfZe1LgmhSe96SJfChPuukG0Z6JxoGo95zw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mark 2026-10-26 11:30:49 Re: Debugging --- ERROR: function does not exist
Previous Message Mark 2026-10-26 10:52:50 Re: Debugging --- ERROR: function does not exist