CODEWITHSUNDEEP
postgresql
Viacheslav Kolybelkin
Viacheslav Kolybelkin

Reputation: 55

Return result of query from function in postgresql

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?

Upvotes: 1

Views: 13728

Answers (1)

Peter Eisentraut
Peter Eisentraut

Reputation: 36719

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;

Upvotes: 2

Related Questions