Return multiple values from declared variables in postrgres function

Question

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$
;

You don't want RETURN setof record, but rather a single record? — Bergi
– Bergi, Commented Aug 16, 2021 at 10:46
Does this answer your question? Return multiple fields as a record in PostgreSQL with PL/pgSQL Or Returning multiple values in a function — Bergi
– Bergi, Commented Aug 16, 2021 at 10:46
I can't create self type so it doesn't answer my question. @Satyanand bhardwaj answer like your second link, but more detailed — FeoJun
– FeoJun, Commented Aug 16, 2021 at 11:33

FeoJun · Accepted Answer · 2021-08-16 11:38:38Z

4

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$;

edited Aug 16, 2021 at 11:38

FeoJun

1531 gold badge6 silver badges23 bronze badges

answered Aug 16, 2021 at 10:41

Satyanand bhardwaj

3653 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return multiple values from declared variables in postrgres function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related