PostgreSQL function returning multiple result sets

by

A simpler way has been around since PostgreSQL 8.3:

CREATE FUNCTION test()
  RETURNS SETOF first_table AS
$func$
BEGIN

RETURN QUERY
SELECT * FROM first_table;

RETURN QUERY
SELECT * FROM second_table;   -- has to return same rowtype as first_table!

END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM test();

Both result sets are appended to a single set returned from the function.
See the manual for RETURN QUERY.

More Related Contents:

  1. What are ‘$$’ used for in PL/pgSQL
  2. INSERT with dynamic table name in trigger function
  3. The forgotten assignment operator “=” and the commonplace “:=”
  4. Truncating all tables in a Postgres database
  5. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  6. How to set value of composite variable field using dynamic SQL
  7. How do I do large non-blocking updates in PostgreSQL?
  8. Execute a dynamic crosstab query
  9. Is there a way to disable function overloading in Postgres
  10. Can I make a plpgsql function return an integer without using a variable?
  11. Dynamic SQL (EXECUTE) as condition for IF statement
  12. Returning from a function with OUT parameter
  13. SELECT raises exception in PL/pgSQL function
  14. Test for null in function with varying parameters
  15. Return SETOF rows from PostgreSQL function
  16. Error when setting n_distinct using a plpgsql variable
  17. Postgres FOR LOOP
  18. Dynamically generate columns for crosstab in PostgreSQL
  19. Function to loop through and select data from multiple tables
  20. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  21. SQL Sub queries in check constraint
  22. Cursor based records in PostgreSQL
  23. ERROR: unterminated quoted string at or near
  24. How can I send some http request from postgresql function or trigger
  25. Select from a table variable
  26. Hashing a String to a Numeric Value in PostgreSQL
  27. Loop on tables with PL/pgSQL in Postgres 9.0+
  28. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment