and a correction for Tom! 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 could not use the method suggest by David Johnston above (as the customType was not a scalar), but I may have been able to use the example from Adiran above On Mon, Oct 26, 2026 at 10:56 AM Mark <[email protected]> wrote: > 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] >>> >>
