sql - 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?

question from:https://stackoverflow.com/questions/955167/return-setof-record-virtual-table-from-function

Answer 1

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