Question 90495 in PostgreSQL Question posted in
The official documentation can be found here.

Creating an anonymous DO block – Postgresql

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
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

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

    enter image description here

    you can try to use it in an tranction

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search