Return setof record (virtual table) from function

Solution 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;

Solution 2:

(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?)