DECLARE

Synopsis

Use the DECLARE statement to create a cursor. See the generic section Cursors. The DECLARE statement is used jointly with the MOVE, FETCH, and CLOSE statements.

The term cursor is a SQL keyword that's used in the DECLARE statement (but in no other SQL statement).

There's also a PL/pgSQL API for explicit cursor management.

Syntax

• Grammar
• Diagram

declare ::= DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]  
            CURSOR [ { WITH | WITHOUT } HOLD ] FOR subquery

Semantics

DECLARE creates a cursor. (There's no notion of so-called "opening" a cursor in top-level SQL.) A cursor's duration is limited to the duration of the session that declares it. Notice the critical maximum lifetime difference between a holdable, and a non-holdable, cursor. See the section Transactional behavior — holdable and non-holdable cursors. (The CLOSE statement drops a cursor so that you can shorten its lifetime if you want to—typically in order to save resources.)

The pg_cursors catalog view lists all the currently existing cursors in the current session.

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

BINARY

Specifies that the cursor returns data in binary rather than in text format.

Usually, a cursor is specified to return data in text format, the same as a SELECT statement would produce. This binary format reduces conversion effort for both the server and client at the cost of more programmer effort to deal with platform-dependent binary data formats. For example, if a query returns a value of one from an integer column, you would get the string value 1 with the default choice. But with a binary cursor you would get a 4-byte field containing the internal representation of the value (in big-endian byte order).

Use binary cursors carefully. Many applications, including ysqlsh (and psql) are not prepared to handle binary cursors and expect data to come back in the text format.

INSENSITIVE

This indicates that data retrieved from the cursor should be unaffected by updates to the table(s) underlying the cursor that occur after the cursor has been declared. In PostgreSQL, and therefore in YSQL, this is the only behavior. This key word therefore has no effect and is accepted only for compatibility with the SQL standard.

SCROLL and NO SCROLL

SCROLL specifies that you can use the flavors of FETCH and MOVE to access the current row and rows in the cursor's result set that lie before the current row (i.e. including and before the row that FETCH RELATIVE 0 accesses). In simple cases, the execution plan is intrinsically reversible: it allows backwards fetching just as easily as it allows forwards fetching. But not all execution plans are reversible; and when a plan is not reversible, specifying SCROLL implies creating a cache of the rows that the cursor's subquery defines at the moment that the first MOVE or FETCH statement is executed (or on demand as new rows are accessed). This implies both a performance cost and a resource consumption cost.

NO SCROLL specifies that the cursor cannot be used to retrieve the current row or rows that lie before it.

When you specify neither SCROLL nor NO SCROLL, then allow scrolling is allowed in only some cases—and this is therefore different from specifying SCROLL explicitly.

WITHOUT HOLD and WITH HOLD

WITHOUT HOLD specifies that the cursor cannot be used after the transaction that created it ends (even if it ends with a successful commit).

WITH HOLD specifies that the cursor can continue to be used after the transaction that created it successfully commits. (Of course, it vanishes if the transaction that created it rolls back.)

Specifying neither WITHOUT HOLD nor WITH HOLD is the same as specifying WITHOUT HOLD.

Simple example

drop table if exists t cascade;
create table t(k, v) as
select g.val, g.val*100
from generate_series(1, 22) as g(val);

start transaction;
  declare cur scroll cursor without hold for
  select k, v
  from t
  where (k <> all (array[1, 3, 5, 7, 11, 13, 17, 19]))
  order by k;
  
  select
    statement,
    is_holdable::text,
    is_scrollable::text
  from pg_cursors where name = 'cur'
  and not is_binary
  and creation_time < (transaction_timestamp() + make_interval(secs=>0.05));

  fetch all from cur;
  
  close cur;
rollback;

This is the result from "select... from pg_cursors...":

                       statement                        | is_holdable | is_scrollable 
--------------------------------------------------------+-------------+---------------
 declare cur scroll cursor without hold for            +| false       | true
   select k, v                                         +|             | 
   from t                                              +|             | 
   where (k <> all (array[1, 3, 5, 7, 11, 13, 17, 19]))+|             | 
   order by k;                                          |             |

And this is the result from FETCH ALL:

 k  |  v   
----+------
  2 |  200
  4 |  400
  6 |  600
  8 |  800
  9 |  900
 10 | 1000
 12 | 1200
 14 | 1400
 15 | 1500
 16 | 1600
 18 | 1800
 20 | 2000
 21 | 2100
 22 | 2200

See also

• MOVE
• FETCH
• CLOSE