Return multiple resultset from a function in postgres using cursor-CodePudding

I want to write a function in postgres which will return multiple resultset from different tables. I found that we can achieve that using ref cursors. So i created one function supposed to return 2 results from different tables. When I call the cursors under a transaction , no results are returned. It just says query executed successfully. Postgres version --> 14.X. I am running this query in pgadmin 4.

CREATE OR REPLACE FUNCTION multipleresultset()
RETURNS SETOF refcursor AS
$BODY$
DECLARE
ref1 refcursor := 'cursor1';
ref2 refcursor := 'cursor2';
BEGIN
open ref1 FOR
SELECT * FROM table1;
RETURN NEXT ref1;

open ref2 FOR
SELECT * FROM table2;
RETURN NEXT ref2;
--RETURN;
END;$BODY$
LANGUAGE 'plpgsql';

begin;
select * from multipleresultset();
FETCH ALL in "cursor1";
FETCH ALL in "cursor2";
commit;

CodePudding user response：

This SQL code is fine by itself, but your SQL client is probably sending the whole block in one go as a multi-query string. Then if shows only the result of the last instruction of that sequence, which is the result of commit.

If you tried this in in psql (the primary command-line interface for postgresql), it would show results, since psql parses the SQL buffer to identify queries between ; and sends them as separate statements (use \; to group them).