PostgreSQL function returning multiple result sets

Question

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

CREATE FUNCTION test
    
AS
    
SELECT * FROM first_table
    
SELECT * FROM second_table

Answer 1

For example, you can use multiple RETURN NEXT or RETURN QUERY statements in my_func() which returns SETOF RECORD type as shown below:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
DECLARE 
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT row; -- Here
  END LOOP;
  FOR row IN VALUES ('Robert','Wilson'), ('Mark','Taylor') LOOP
    RETURN NEXT row; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
BEGIN
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller'); -- Here
  RETURN QUERY VALUES ('Robert','Wilson'), ('Mark','Taylor'); -- Here
END;
$$ LANGUAGE plpgsql;

*Memos:

• A RETURN NEXT and RETURN QUERY statement cannot exit a function while a RETURN statement can.

• You can also use a RETURN NEXT and RETURN QUERY statement together in my_func() as I explain it in my answer:

Then, calling my_func() returns 4 rows as shown below:

postgres=# SELECT * FROM my_func() AS (first_name TEXT, last_name TEXT);
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
 Robert     | Wilson
 Mark       | Taylor
(4 rows)

And, you can use multiple RETURN NEXT or RETURN QUERY statements in my_func() which returns TABLE() type as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(first_name TEXT, last_name TEXT) AS $$
BEGIN
  FOR first_name, last_name IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT; -- Here
  END LOOP;
  FOR first_name, last_name IN VALUES ('Robert','Wilson'), ('Mark','Taylor') LOOP
    RETURN NEXT; -- Here
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS TABLE(first_name TEXT, last_name TEXT) AS $$
BEGIN
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller'); -- Here
  RETURN QUERY VALUES ('Robert','Wilson'), ('Mark','Taylor'); -- Here
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 4 rows as shown below:

postgres=# SELECT * FROM my_func();
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
 Robert     | Wilson
 Mark       | Taylor
(4 rows)

postgres=# SELECT my_func();
     my_func
-----------------
 (John,Smith)
 (David,Miller)
 (Robert,Wilson)
 (Mark,Taylor)
(4 rows)

Answer 2

Return single result set from multiple queries

A simpler way has been around since PostgreSQL 8.3:

CREATE FUNCTION test()
  RETURNS SETOF first_table
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT * FROM first_table;

   RETURN QUERY
   SELECT * FROM second_table;  -- same row type as first_table!
END
$func$;

Call:

SELECT * FROM test();

Both result sets are appended to a single set returned from the function. The row type has to match, of course.
See the manual for RETURN QUERY.

Return multiple cursors

You could return multiple cursors, which is not the same thing, exactly, as you have to FETCH from each cursor in turn. And you have to do it all within the same transaction. There is a code example in the manual. It might be useful to return large row sets from a function. In most cases, I would rather just run each SELECT statement in turn.

Answer 3

CREATE OR REPLACE FUNCTION public.TestReturnMultipleTales
( 
 param_coid integer, 
 ref1 refcursor,
 ref2 refcursor
)
RETURNS SETOF refcursor 
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000

AS $BODY$
DECLARE
            
BEGIN
  OPEN ref1 FOR SELECT * FROM dbo.tbl1 WHERE coid = param_coid;
  RETURN NEXT ref1;

  OPEN ref2 FOR SELECT * FROM dbo.tbl2 LIMIT 5;
  RETURN NEXT ref2;
END;
$BODY$;

USE IN pgSQL Query:- 

BEGIN;
    SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
    FETCH ALL IN "Ref1";
    FETCH ALL IN "Ref2";
COMMIT;

SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
FETCH ALL IN "Ref1";

SELECT football_players.show_cities_multiple(123456, 'Ref1', 'Ref2');
FETCH ALL IN "Ref2";

Answer 4

If first_table and second_table have the same layout, you can also just use

SELECT * FROM first_table WHERE ...
UNION ALL
SELECT * FROM second_table WHERE ...

[EDIT: Thanks to a commenter (whose name is probably not "null" :) ) for pointing out that UNION ALL is faster than UNION.]

Answer 5

Yes.

Example:

test=# create function x () returns setof integer language plpgsql as $$ begin return next 1; return next 2; end $$;
CREATE FUNCTION
test=# select * from x();
 x 
---
 1
 2
(2 rows)

You can of course use an existing table/view or a custom type for the returned type.

Example using language SQL:

test=# create table customer (name varchar, birth_date date);
CREATE TABLE
test=# create function y () returns setof customer language sql as $$ 
select * from customer
union all
select * from customer
$$;
CREATE FUNCTION
test=# insert into customer values ('joe', now()::date);
INSERT 0 1
test=# insert into customer values ('jill', now()::date);
INSERT 0 1
test=# select * from y();
 name | birth_date 
------+------------
 joe  | 2009-04-16
 jill | 2009-04-16
 joe  | 2009-04-16
 jill | 2009-04-16
(4 rows)

See here for doc

Answer 6

CREATE OR REPLACE FUNCTION "pr_GetCustomersAndOrders"()
RETURNS SETOF refcursor AS
$BODY$DECLARE
customerRC refcursor;
orderRC refcursor;
BEGIN
open customerRC FOR
SELECT * FROM customers;
RETURN NEXT customerRC;

open orderRC FOR
SELECT * FROM orders;
RETURN NEXT orderRC;
RETURN;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION "pr_GetCustomersAndOrders"() OWNER TO postgres;

I.o.w. using refcursors :)