Creating an anonymous DO block

Question

Following Sergey's example here, https://stackoverflow.com/a/60834163/1513027
I'm trying to create an anonymous DO block rather than a function.

If I have the FETCH inside the block, it gives a syntax error, possibly wanting an INTO clause. And it doesn't matter whether the name of the cursor is in quotes.

DO 
$$
DECLARE  _query TEXT;
DECLARE  _cursor CONSTANT refcursor := _cursor;

BEGIN
   _query := 'select "Port", "Version", "AddDate" from "LatestLogEntry";';

   OPEN _cursor FOR EXECUTE _query;

   FETCH ALL FROM _cursor;   -- syntax error at ;
END
$$;

If I have it outside, as in Sergey's example, then it can't see the cursor declared inside the block. And it does matter whether the name of the cursor is in quotes.

DO 
$$
DECLARE  _query TEXT;
DECLARE  _cursor CONSTANT refcursor := '_cursor';

BEGIN
   _query := 'select "Port", "Version", "AddDate" from "LatestLogEntry";';

   OPEN _cursor FOR EXECUTE _query;
END
$$;

FETCH ALL FROM _cursor -- ERROR:  cursor "_cursor" does not exist

Your second example seems to work fine. https://dbfiddle.uk/iR50ki5o — EdmCoff, Dec 16 '22 at 22:50
where do you run the code in pgadmin it works and with postgres 15 — nbk, Dec 16 '22 at 23:17
Note that `DECLARE` starts a _block_ where all variables can be defined. There is no need to start a new block for each variable. — , Dec 17 '22 at 10:18

score 1 · Answer 1 · answered Dec 16 '22 at 23:18

The answer was hidden in a comment in one of the examples.
-- need to be in a transaction to use cursors.
Wrapping it in a transaction works.

BEGIN;
DO 
$$
DECLARE  _query TEXT;
DECLARE  _cursor CONSTANT refcursor := '_cursor';

BEGIN
   _query := 'select "Port", "Version", "AddDate" from "LatestLogEntry";';

   OPEN _cursor FOR EXECUTE _query;
END
$$;

FETCH ALL FROM _cursor -- ERROR:  cursor "_cursor" does not exist

COMMIT

score 0 · Accepted Answer · answered Dec 16 '22 at 23:18

0

In pgadmin this does work see screen below run postgres 15

as Sergey pointed out this has to be in a transaction, else you get the error message you get.

you can try to use it in an tranction

answered Dec 16 '22 at 23:18

nbk

45,398
8
30
47

And there it is, right there in the original. "Cursors are visible in the transaction scope so you should use this within one transaction." – BWhite Dec 16 '22 at 23:52

Creating an anonymous DO block

2 Answers2