PostgreSQL function returning multiple result sets

postgresql plpgsql
Markus picture Markus · Apr 16, 2009 · Viewed 31.3k times · Source

Is it possible to return multiple result sets from a Postgres function, like in MSSQL:

CREATE PROCEDURE test

AS

SELECT * FROM first_table

SELECT * FROM second_table

Answer

Erwin Brandstetter picture Erwin Brandstetter · Oct 13, 2011

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.

Related questions

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …

Read More
PostgreSQL IF statement

How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);

Read More
Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the …

Read More