CLOSE statement [YSQL]

{{< warning title="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. {{< /warning >}}

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

{{%ebnf%}} close {{%/ebnf%}}

Semantics

CLOSE drops a cursor. Use this statement so that you can shorten the lifetime a cursor—typically in order to save resources.

{{< note title="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 of CLOSE is not rolled back—in other words the closed cursor continues no longer to exist. {{< /note >}}

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

See also

• DECLARE
• MOVE
• FETCH