I have an old MSSQL procedure that needs to be ported to a PostgreSQL function. Basically the SQL procedure consist in a CURSOR over a select statement. For each cursor entity i have three select statements based on the current cursor output.
FETCH NEXT FROM @cursor INTO @entityId
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM table1 WHERE col1 = @entityId
SELECT * FROM table2 WHERE col2 = @entityId
SELECT * FROM table3 WHERE col3 = @entityId
END
The tables from the SELECT statements have different columns.
I know that the PostgreSQL use refcursor in order to return multiple result sets but the question is if is possible to open and return multiple dynamic refcursors inside of a loop?
The Npgsql .NET data provider is used for handling the results.
Postgres test code with only 1 cursor inside loop:
CREATE OR REPLACE FUNCTION "TestCursor"(refcursor)
RETURNS SETOF refcursor AS
$BODY$
DECLARE
entity_id integer;
BEGIN
FOR entity_id IN SELECT "FolderID" from "Folder"
LOOP
OPEN $1 FOR SELECT * FROM "FolderInfo" WHERE "FolderID" = entity_id;
RETURN NEXT $1;
CLOSE $1;
END LOOP;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Then the test code:
BEGIN;
SELECT * FROM "TestCursor"('c');
FETCH ALL IN c;
COMMIT;
The SELECT * FROM "TestCursor"('c'); output is like on screenshot:
Then when i try to fetch data i get the error: ERROR: cursor "c" does not exist
