2

Postgresql 12. Want to call function testA() from another function testB(), and assign two "out" values to the 2 local variables. Don't know how to do the assignment, please help.

create or replace function testA(
out outA int,
out outB varchar)
returns record as $$
begin
    outA := 100;
    outB := '1234';
end; $$ language plpgsql;

create or replace function testB()
returns void as $$
declare aa int; bb varchar;
begin
-- select * from testA(aa, bb);
end; $$ language plpgsql;
GMB
  • 216,147
  • 25
  • 84
  • 135
brewphone
  • 1,316
  • 4
  • 24
  • 32

1 Answers1

4

Like this:

CREATE OR REPLACE FUNCTION public.testa(OUT outa integer, OUT outb character varying)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
begin
    outA := 100;
    outB := '1234';
end; $function$
;

CREATE OR REPLACE FUNCTION public.testb()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
    aa int; bb varchar;
begin
    select into aa, bb * from testa();
    RAISE NOTICE 'a: %, b: %', aa, bb;
end; $function$
;

select * from testb();
NOTICE:  a: 100, b: 1234
 testb 
-------
 
(1 row)

The SELECT INTO will assign the output of testa to the variables you declare in testb. See here for more information.

Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28
  • Works perfectly. Never knew "select into a, b * from ..." is possible with asterisk. Thank you very much Adrian. – brewphone Sep 18 '20 at 03:10
  • Well that worked because I knew there was only two values being returned from `testa`. This was a quick and dirty example. Prudent behavior would be to explicitly name the values you want to `SELECT INTO aa, bb` . So: `select into aa, bb outa, outb from testa();` – Adrian Klaver Sep 18 '20 at 14:26