Return setof record (virtual table) from function

(This is all tested with postgresql 8.3.7-- do you have an earlier version? just looking at your use of "ALIAS FOR $1")

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(numeric) RETURNS SETOF RECORD AS $$DECLARE open_id ALIAS FOR $1; result RECORD;BEGIN RETURN QUERY SELECT '1', '2', '3'; RETURN QUERY SELECT '3', '4', '5'; RETURN QUERY SELECT '3', '4', '5';END$$;

If you have a record or row variable to return (instead of a query result), use "RETURN NEXT" rather than "RETURN QUERY".

To invoke the function you need to do something like:

select * from storeopeninghours_tostring(1) f(a text, b text, c text);

So you have to define what you expect the output row schema of the function to be in the query. To avoid that, you can specify output variables in the function definition:

CREATE OR REPLACE FUNCTION storeopeninghours_tostring(open_id numeric, a OUT text, b OUT text, c OUT text) RETURNS SETOF RECORD LANGUAGE 'plpgsql' STABLE STRICT AS $$BEGIN RETURN QUERY SELECT '1'::text, '2'::text, '3'::text; RETURN QUERY SELECT '3'::text, '4'::text, '5'::text; RETURN QUERY SELECT '3'::text, '4'::text, '5'::text;END$$;

(not quite sure why the extra ::text casts are required... '1' is a varchar by default maybe?)

^{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$BEGINRETURN 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;

I use temporary tables quite a bit in my functions. You need to create a return type on the database and then create a variable of that type to return. Below is sample code that does just that.

CREATE TYPE storeopeninghours_tostring_rs AS(colone text, coltwo text, colthree text);CREATE OR REPLACE FUNCTION "public"."storeopeninghours_tostring" () RETURNS setof storeopeninghours_tostring_rs AS$BODY$DECLARE  returnrec storeopeninghours_tostring_rs;BEGIN    BEGIN         CREATE TEMPORARY TABLE tmpopeninghours (            colone text,            coltwo text,            colthree text        );    EXCEPTION WHEN OTHERS THEN        TRUNCATE TABLE tmpopeninghours; -- TRUNCATE if the table already exists within the session.    END;    insert into tmpopeninghours VALUES ('1', '2', '3');    insert into tmpopeninghours VALUES ('3', '4', '5');    insert into tmpopeninghours VALUES ('3', '4', '5');    FOR returnrec IN SELECT * FROM tmpopeninghours LOOP        RETURN NEXT returnrec;    END LOOP;END;$BODY$LANGUAGE 'plpgsql' VOLATILE;select * from storeopeninghours_tostring()