| 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:49:42 |
| Message-ID: | CAAm-KOuHoffo5c8LhqB-fwX0NUpsYbbWqJQLS2hwV7M_T+ACtw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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 | Christophe Pettus | 2026-10-26 10:50:52 | Re: PostgreSQL 17 — Native Password Policy Support |
| Previous Message | Mark | 2026-10-26 10:38:04 | Re: Debugging --- ERROR: function does not exist |