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 <[email protected]> wrote: > > > 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] >> >
