DECLARE ​

Create a cursor within a transaction block.

DECLARE name [ INSENSITIVE ] [ NO SCROLL | SCROLL ] CURSOR  [ { WITH | WITHOUT } HOLD ] FOR query

See also CLOSE, FETCH, and MOVE (related cursor commands).

INSENSITIVE

By default, cursors are insensitive to any changes in the data that are made after the cursor is declared. Data is fetched based on the results of the query when the cursor was declared. The INSENSITIVE keyword need not be specified and has no effect. Sensitive cursors are not supported.

NO SCROLL | SCROLL

NO SCROLL is the default and need not be specified. Scrollable (SCROLL) cursors are intended for use with backward moves and fetches, which are not supported. However, a cursor may be created with SCROLL but used for forward moves and fetches only.

WITH HOLD | WITHOUT HOLD

WITHOUT HOLD, the default, specifies that the cursor cannot be used outside of the transaction that created it. WITH HOLD specifies that a cursor can continue to be used after the transaction in which it was created commits. This distinction means that you can declare a cursor WITH HOLD without enclosing the DECLARE command in an explicit begin/end transaction block. The default type of cursor (WITHOUT HOLD) can only be used within the current transaction and must be preceded by an explicit BEGIN statement.

FOR query

Specify any valid Yellowbrick SELECT query.

Note: If the query does not have FROM clause references to Yellowbrick tables, the cursor may be declared according to the rules defined in the PostgreSQL documentation. For example, backward moves and fetches will be allowed. (However, note that in some cases the query planner may add a FROM sys.const clause to the query that you submit, in which case the PostgreSQL rules will not apply.)

Interaction with Savepoints ​

Savepoints keep track of open cursors. If a cursor is invalidated by an error that occurs within a transaction, rolling back to a savepoint in that transaction makes the cursor valid again (except when the error results from a DECLARE, FETCH, or MOVE of the cursor itself). However, the cursor is not moved back to its position when the SAVEPOINT command was issued; the cursor maintains the last position it held.

Examples ​

The DECLARE command must be used within an explicit transaction block for default WITHOUT HOLD cursors. For example:

premdb=# begin;
BEGIN
premdb=# declare match cursor for select * from match order by matchday;
DECLARE CURSOR
...
premdb=# end;

You cannot access such a cursor after the current transaction has ended:

premdb=# begin;
BEGIN
premdb=# declare season cursor for select * from season order by 1;
DECLARE CURSOR
premdb=# end;
COMMIT
premdb=# fetch next from season;
ERROR:  cursor "season" does not exist

A WITH HOLD cursor does not require an explicit BEGIN statement when it is declared and is usable in subsequent transactions:

premdb=# declare season cursor with hold for select * from season order by 1;
DECLARE CURSOR
premdb=# fetch next from season;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       1 | 1992-1993   |       22 | Manchester United
(1 row)

premdb=# fetch next from season;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       2 | 1993-1994   |       22 | Manchester United
(1 row)

premdb=# begin;
BEGIN
premdb=# fetch next from season;
 seasonid | season_name | numteams |     winners      
----------+-------------+----------+------------------
       3 | 1994-1995   |       22 | Blackburn Rovers
(1 row)

premdb=# end;
COMMIT
premdb=# fetch next from season;
 seasonid | season_name | numteams |      winners      
----------+-------------+----------+-------------------
       4 | 1995-1996   |       20 | Manchester United
(1 row)
...