On Sun, Oct 25, 2026 at 8:55 PM Adrian Klaver <[email protected]> wrote:
> 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 > --------------- > > 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 > [email protected] >
