On 10/25/26 1:19 PM, Tom Lane wrote:
Adrian Klaver <[email protected]> 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
[email protected]
