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;