PostgreSQL function returning multiple result sets

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

Solution 1:

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.

Solution 2:

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 :)

Solution 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";