| From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Mark <nvpostgres(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Debugging --- ERROR: function does not exist |
| Date: | 2026-10-25 20:55:32 |
| Message-ID: | 9bc98c9a-f5f0-424b-9eac-4cfe18820fa1@aklaver.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
---------------
>
> regards, tom lane
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Durgamahesh Manne | 2026-10-26 04:55:06 | Re: Regarding huge logical slot lag between source and target |
| Previous Message | David G. Johnston | 2026-10-25 20:33:45 | Re: Debugging --- ERROR: function does not exist |