YSQL currently supports only fetching rows from a cursor consecutively in the forward direction.
See the subsection Beware Issue #6514 in the generic section Cursors.Synopsis
Use the CLOSE statement to "drop" a cursor. See the generic section Cursors. The CLOSE statement is used jointly with the DECLARE, MOVE, and FETCH statements.
Syntax
Grammar
DiagramSemantics
CLOSE drops a cursor. Use this statement so that you can shorten the lifetime a cursor—typically in order to save resources.
CLOSE is outside the scope of rolling back to a savepoint.
If a cursor is closed after a savepoint to which you later roll back, the effect ofCLOSE is not rolled back—in other words the closed cursor continues no longer to exist.
name
A cursor is identified only by an unqualified name and is visible only in the session that declares it. This determines the uniqueness scope for its name. (The name of a cursor is like that of a prepared statement in this respect.)
Using the keyword ALL in place of the name of an extant cursor closes every extant cursor.
Simple example
close all;
start transaction;
declare "Cur-One" no scroll cursor without hold for
select 17 as v;
declare "Cur-Two" no scroll cursor with hold for
select 42 as v;
select name, is_holdable::text, is_scrollable::text
from pg_cursors
order by name;
close "Cur-One";
commit;
select name, is_holdable::text, is_scrollable::text
from pg_cursors
order by name;
fetch all from "Cur-Two";
This is the result from the first pg_cursors query:
name | is_holdable | is_scrollable
---------+-------------+---------------
Cur-One | false | false
Cur-Two | true | false
This is the result from the second pg_cursors query:
name | is_holdable | is_scrollable
---------+-------------+---------------
Cur-Two | true | false
And this is the result from fetch all from "Cur-Two":
v
----
42