CLOSE

This page documents a preview version. v2.23 Preview
Preview includes features under active development and is for development and testing only.
For production, use the latest stable version (v2024.1).

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

close ::= CLOSE { name | ALL }

close

CLOSEnameALL

Semantics

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 of CLOSE 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

See also