Return result of query from function in postgresql

Question

I have the query for example "select id, field1, field2 from table1" (id and field1 are integer and field2 is varchar) and I need call the query from function and return result as is.

CREATE OR REPLACE FUNCTION get_all_record()
  RETURNS TABLE(id integer , field2 varchar(20)) AS
$$                                                  
BEGIN
    EXECUTE 'SELECT table1.id, table1.field2 FROM table1' INTO id, field2;
    RETURN;
END;
$$
  LANGUAGE plpgsql

but this solution returns only one record in one field. How can I correct it?

You'll want to use RETURN QUERY instead of EXECUTE '...' INTO. — JNevill
– JNevill, Commented Feb 26, 2016 at 17:28
JNevill, but if I use RETURN QUERY instead of EXECUTE '...' INTO the function return all records in one field like text "(value1, value2)" — Viacheslav Kolybelkin
– Viacheslav Kolybelkin, Commented Feb 26, 2016 at 18:18

Peter Eisentraut · Accepted Answer · 2016-02-26 18:56:01Z

2

Try this:

CREATE OR REPLACE FUNCTION get_all_record(OUT id integer, OUT field2 varchar(20))
RETURNS SETOF record
AS $$
BEGIN
    RETURN QUERY SELECT table1.id, table1.field2 FROM tiers;
END;
$$
LANGUAGE plpgsql;

answered Feb 26, 2016 at 18:56

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

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

2 Comments

Viacheslav Kolybelkin Over a year ago

I need function without parameters

Abelisto Over a year ago

@ViKo Function without parameters returning rowset called view ;) Seriously out parameters just provides result structure and you should use this function like select * from get_all_record() without parameters specifying. @PeterEisentraut your example can be simplified a bit using language sql instead of plpgsql.

Collectives™ on Stack Overflow

Return result of query from function in postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related