Search code examples
postgresqlcursor

Cursors in Postgres

Are cursors stored permanently in Postgres? The reason I am asking is I have the following:

BEGIN;
DECLARE cpc CURSOR for select * from project.car order by rego, date;
FETCH  1 IN cpc;
CLOSE cpc;
COMMIT;

Then I start a new transaction:

BEGIN;
OPEN cpc;
fetch 1 in cpc;
CLOSE cpc;
COMMIT;

But get the error: "ERROR: cursor "cpc" does not exist SQL state: 34000".

Is there a way to get a cursor to be persisted between transactions?

Solution

  • Cursors are local to a transaction and cease to exist when the transaction commits or rolls back.

    You can declare WITH HOLD cursors that are persistent after commit, though they remain visible only within the session that created them.