Return multiple resultset from a function in postgres using cursor

Question

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;

Daniel Vérité · Accepted Answer · 2022-02-10 20:41:46Z

3

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

answered Feb 10, 2022 at 20:41

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

subhrendu Over a year ago

Correct. I ran the commands separately and was able to view the result in pgadmin

Collectives™ on Stack Overflow

Return multiple resultset from a function in postgres using cursor

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related