info > DECLARE

man perldoc info pydoc3 ri search

📖 NAME

DECLARE - define a cursor

🚀 Quick Reference

Use Case	Command	Description
Declare a simple cursor	DECLARE name CURSOR FOR query;	Create a cursor for a SELECT or VALUES query
Declare a binary cursor	DECLARE name BINARY CURSOR FOR query;	Return data in binary format (platform-dependent)
Declare a scrollable cursor	DECLARE name SCROLL CURSOR FOR query;	Allow nonsequential row retrieval (backward fetches)
Declare a cursor with hold	DECLARE name CURSOR WITH HOLD FOR query;	Cursor survives transaction commit
Declare an insensitive cursor	DECLARE name INSENSITIVE CURSOR FOR query;	Changes to underlying data are not visible (default in PostgreSQL)

📋 SYNOPSIS

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

📝 DESCRIPTION

DECLARE allows a user to create cursors, which can be used to retrieve a small number of rows at a time out of a larger query. After the cursor is created, rows are fetched from it using FETCH(7).

💡 Note: This page describes usage of cursors at the SQL command level. If you are trying to use cursors inside a PL/pgSQL function, the rules are different — see Section 43.7.

⚙️ PARAMETERS

• name — The name of the cursor to be created.
• BINARY — Causes the cursor to return data in binary rather than in text format.
• ASENSITIVE / INSENSITIVE — Cursor sensitivity determines whether changes to the data underlying the cursor, done in the same transaction after the cursor has been declared, are visible in the cursor. INSENSITIVE means they are not visible, ASENSITIVE means the behavior is implementation-dependent. A third behavior, SENSITIVE, is not available in PostgreSQL. All cursors are insensitive; these key words have no effect and are only accepted for compatibility. Specifying INSENSITIVE together with FOR UPDATE or FOR SHARE is an error.
• SCROLL / NO SCROLL — SCROLL specifies that the cursor can be used to retrieve rows in a nonsequential fashion (e.g., backward). Depending on query complexity, specifying SCROLL may impose a performance penalty. NO SCROLL forbids nonsequential retrieval. The default allows scrolling in some cases (see Notes).
• WITH HOLD / WITHOUT HOLD — WITH HOLD allows the cursor to continue after the creating transaction commits. WITHOUT HOLD restricts it to the transaction. Default is WITHOUT HOLD.
• query — A SELECT or VALUES command providing the rows to be returned.

The key words ASENSITIVE, BINARY, INSENSITIVE, and SCROLL can appear in any order.

📌 NOTES

Normal cursors return data in text format. The BINARY option returns data in binary format, reducing conversion effort at the cost of platform-dependent data handling. For example, an integer column value 1 is returned as a string "1" with a default cursor, but as a 4-byte internal representation (big-endian) with a binary cursor.

⚠️ Caution: Binary cursors should be used carefully. Many applications (including psql) are not prepared to handle binary cursors and expect text format.

💡 Note: When using the “extended query” protocol, the Bind message overrides the cursor's binary definition. The concept of a binary cursor is obsolete with extended query protocol — any cursor can be treated as either text or binary.

Unless WITH HOLD is specified, the cursor is only usable within the current transaction. DECLARE without WITH HOLD outside a transaction block is useless. PostgreSQL reports an error in such cases. Use BEGIN and COMMIT (or ROLLBACK) to define a transaction block.

If WITH HOLD is specified and the transaction commits successfully, the cursor can be accessed by subsequent transactions in the same session. (Aborting the transaction removes the cursor.) A held cursor is closed by an explicit CLOSE command or at session end. The rows are copied into a temporary file or memory area.

WITH HOLD cannot be used with FOR UPDATE or FOR SHARE.

SCROLL should be specified for backward fetches. PostgreSQL allows backward fetches without SCROLL for simple query plans, but application developers should not rely on this. NO SCROLL disallows backward fetches entirely. Backward fetches are also disallowed with FOR UPDATE or FOR SHARE; SCROLL cannot be specified in that case.

⚠️ Caution: Scrollable cursors may give unexpected results if they invoke volatile functions (see Section 38.7). Re-fetching a row may re-execute functions, leading to different results. Specify NO SCROLL for queries involving volatile functions. Alternatively, declare SCROLL WITH HOLD and commit before reading rows to materialize the entire result.

If the cursor's query includes FOR UPDATE or FOR SHARE, returned rows are locked at first fetch, as with a regular SELECT. The rows are the most up-to-date versions.

⚠️ Caution: Use FOR UPDATE if the cursor will be used with UPDATE ... WHERE CURRENT OF or DELETE ... WHERE CURRENT OF. FOR UPDATE prevents other sessions from changing rows between fetch and update. Without FOR UPDATE, WHERE CURRENT OF may have no effect if the row changed. Also, without FOR UPDATE, WHERE CURRENT OF may fail if the cursor query does not meet SQL standard's “simply updatable” rules (single table, no grouping or ORDER BY). Using FOR UPDATE guarantees updatability. The main reason not to use FOR UPDATE is if you need scrollability or isolation from concurrent updates.

The SQL standard only provides for cursors in embedded SQL. The PostgreSQL server does not implement an OPEN statement; a cursor is considered open when declared. ECPG, the embedded SQL preprocessor, supports standard cursor conventions. You can see all available cursors by querying the pg_cursors system view.

💡 EXAMPLES

DECLARE liahona CURSOR FOR SELECT * FROM films;

See FETCH(7) for more examples.

🔗 COMPATIBILITY

The SQL standard allows cursors only in embedded SQL and modules. PostgreSQL permits interactive use.

According to the SQL standard, changes made to insensitive cursors by UPDATE ... WHERE CURRENT OF and DELETE ... WHERE CURRENT OF are visible in that same cursor. PostgreSQL treats these statements like all other data-changing statements — they are not visible in insensitive cursors.

Binary cursors are a PostgreSQL extension.

📚 SEE ALSO

CLOSE(7), FETCH(7), MOVE(7)

PostgreSQL 14.23 2026 DECLARE(7)

📖 NAME 🚀 Quick Reference 📋 SYNOPSIS 📝 DESCRIPTION ⚙️ PARAMETERS 📌 NOTES 💡 EXAMPLES 🔗 COMPATIBILITY 📚 SEE ALSO