CODEWITHSUNDEEP
postgresql
FeoJun
FeoJun

Reputation: 153

Return multiple values from declared variables in postrgres function

I need to return multiple values that I stored in declared variables, how can I do this using postgres? Maybe I dont need to use variables, but I need to return values from 3 tables. Queries and tables in example simplified

My example:

CREATE OR REPLACE FUNCTION public.select_multiple_values(id_ bigint)
 RETURNS setof record
 LANGUAGE plpgsql
AS $function$
declare
id_child bigint;
id_parent bigint;
name_ varchar;
begin
    select id from public.req where id = id_ into id_child;
    select reg_id from public.req where id = id_ into id_parent;
    select "name" from public.reg where id = id_parent into name_;

    return query select id_child, id_parent, name_ ;
END;
$function$
;

Upvotes: 2

Views: 2790

Answers (1)

Satyanand bhardwaj
Satyanand bhardwaj

Reputation: 355

try this - you can use RETURN TABLE CONCEPT

CREATE OR REPLACE FUNCTION public.select_multiple_values(id_ bigint)
RETURNS TABLE(id_child bigint, id_parent bigint, name_ character varying ) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
    
AS $BODY$
declare p_id_child bigint;
declare p_id_parent bigint;
declare p_name_ character varying;
begin

    select id  into p_id_child from public.req where id = id_ ;
    select reg_id   into p_id_parent from public.req where id = id_;
    select "name"  into p_name_ from public.reg where id = id_parent ;

    
    return query select p_id_child as id_child, p_id_parent as id_parent, p_name_ as name_ ;
    
END
$BODY$;

Upvotes: 4

Related Questions