CODEWITHSUNDEEP
sqlpostgresqlsql-function
brewphone
brewphone

Reputation: 1366

Postgresql, one function calls another function and get 2 out values to caller

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;

Upvotes: 2

Views: 133

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19655

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.

Upvotes: 4

Related Questions