Return setof record (virtual table) from function

Question

I need a Postgres function to return a virtual table (like in Oracle) with custom content. The table would have 3 columns and an unknown number of rows.

I just couldn't find the correct syntax on the internet.

Imagine this:

CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" (numeric)   RETURNS setof record AS DECLARE   open_id ALIAS FOR $1;   returnrecords setof record; BEGIN   insert into returnrecords('1', '2', '3');   insert into returnrecords('3', '4', '5');   insert into returnrecords('3', '4', '5');   RETURN returnrecords; END; 

How is this written correctly?

Answer 1

^{All previously existing answers are outdated or were inefficient to begin with.}

Assuming you want to return three integer columns.

PL/pgSQL function

Here's how you do it with modern PL/pgSQL (PostgreSQL 8.4 or later):

CREATE OR REPLACE FUNCTION f_foo() -- (open_id numeric) -- parameter not used   RETURNS TABLE (a int, b int, c int) AS $func$ BEGIN RETURN QUERY VALUES   (1,2,3) , (3,4,5) , (3,4,5) ; END $func$  LANGUAGE plpgsql IMMUTABLE ROWS 3; 

In Postgres 9.6 or later you can also add PARALLEL SAFE.

Call:

SELECT * FROM f_foo(); 

Major points

• Use RETURNS TABLE to define an ad-hoc row type to return.
  Or RETURNS SETOF mytbl to use a pre-defined row type.

• Use RETURN QUERY to return multiple rows with one command.

• Use a VALUES expression to enter multiple rows manually. This is standard SQL and has been around for ever.

• If you actually need a parameter, use a parameter name (open_id numeric) instead of ALIAS, which is discouraged. In the example the parameter wasn't used and just noise ...

• No need for double-quoting perfectly legal identifiers. Double-quotes are only needed to force otherwise illegal names (mixed-case, illegal characters or reserved words).

• Function volatility can be IMMUTABLE, since the result never changes.

• ROWS 3 is optional, but since we know how many rows are returned, we might as well declare it to Postgres. Can help the query planner to pick the best plan.

Simple SQL

For a simple case like this, you can use a plain SQL statement instead:

VALUES (1,2,3), (3,4,5), (3,4,5) 

Or, if you want (or have) to define specific column names and types:

SELECT * FROM  (    VALUES (1::int, 2::int, 3::int)         , (3, 4, 5)         , (3, 4, 5)    ) AS t(a, b, c); 

SQL function

You can wrap it into a simple SQL function instead:

CREATE OR REPLACE FUNCTION f_foo()    RETURNS TABLE (a int, b int, c int) AS $func$    VALUES (1, 2, 3)         , (3, 4, 5)         , (3, 4, 5); $func$  LANGUAGE sql IMMUTABLE ROWS 3;