23

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
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Markus
  • 3,547
  • 10
  • 39
  • 55

5 Answers5

19

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 8
    what for different row types? is there any solution? other than cursor. I am looking to get two recordsets on one call. – Udit Solanki Apr 27 '17 at 12:01
  • 3
    @UdeetSolanki: Not possible as direct result from a function. There are various ways around it: with cursors, temp tables, document types like `json`. I suggest you ask a new question, comments are not the place. – Erwin Brandstetter Apr 27 '17 at 14:24
  • Any improvement as of Postgres11? – Maulik Modi Aug 30 '21 at 13:35
  • @MaulikModi: This cannot be "improved" as it's an inherent restriction of the SQL language. Consider asking a new question with details of your case. – Erwin Brandstetter Aug 30 '21 at 20:48
9
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 :)

Frans Bouma
  • 8,259
  • 1
  • 27
  • 28
  • 6
    Fitting this to my tables I get: `SELECT [* FROM] "pr_GetCustomersAndOrders"(); pr_GetCustomersAndOrders -------------------------- ` Using psql 9.1. – valid Mar 01 '14 at 01:31
2
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";
Eric Aya
  • 69,473
  • 35
  • 181
  • 253
Mayur Patel
  • 61
  • 2
0

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.]

j_random_hacker
  • 50,331
  • 10
  • 105
  • 169
  • 3
    Nitpicking, but UNION ALL would be quicker (there is no "| sort | uniq"), but will return duplicates if there are any. – tommym Apr 16 '09 at 15:47
  • 1
    @null: Good point; updated. (I realise your name probably isn't "null" -- seems a recent SO bug causes this. I was able to fix it by editing the top field on my profile page.) – j_random_hacker Apr 16 '09 at 17:37
  • 4
    Yes but what about queries that don't return the same layouts ? It quite limitative if we need to get many sets. I use to have Stored Procedure in SQL Server which returns 10+ result sets. Anything like that in PostgreSQL ? – MaxiWheat Oct 02 '09 at 20:16
  • 2
    @MaxiWheat: Frans Bouma's answer (returning REFCURSORs) covers that case, provided you remain within plpgsql. http://www.postgresql.org/docs/8.4/interactive/plpgsql-cursors.html explains how cursors can be passed back outside of plpgsql (e.g. to be used with a client library). – j_random_hacker Oct 03 '09 at 14:11
-1

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

tommym
  • 2,230
  • 15
  • 11
  • 4
    You only use 1 table, the table "customer". The question is about multiple result sets! – llouk Mar 09 '16 at 20:26