DECLARE

An SQL command for creating a cursor

DECLARE is a utility command for creating a cursor.

DECLARE has always been present in PostgreSQL.

Usage

DECLARE creates a cursor with an associated query. The commands FETCH and MOVE can be used to retrieve data and update the cursor's location within the query.

DECLARE must be executed within a transaction.

An ERROR will be raised if an invalid query is specified.

Change history

DECLARE has been present in every PostgreSQL version.

Examples

Basic usage example for DECLARE:

postgres=# BEGIN;
BEGIN

postgres=*# DECLARE foo_cursor CURSOR
                FOR SELECT * FROM foo;

postgres=*# SELECT * FROM pg_cursors\gx
-[ RECORD 1 ]-+------------------------------
name          | foo_cursor
statement     | DECLARE foo_cursor CURSOR    +
              |     FOR SELECT * FROM foo;
is_holdable   | f
is_binary     | f
is_scrollable | t
creation_time | 2022-10-25 08:23:26.890898+01

A cursor can only be declared in a transaction:

postgres=# DECLARE foo_cursor CURSOR
               FOR SELECT * FROM foo;
ERROR:  DECLARE CURSOR can only be used in transaction blocks

The specified query myst be valid and reference visible objects:

postgres=# DECLARE foo_cursor CURSOR
               FOR SELECT * FROM bar;
ERROR:  relation "bar" does not exist
LINE 2:     FOR SELECT * FROM bar;
  • PostgreSQL documentation: DECLARE

Categories

Cursors, SQL command

See also

FETCH, MOVE, CLOSE, pg_cursors