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