1DECLARE(7)               PostgreSQL 13.4 Documentation              DECLARE(7)
2
3
4

NAME

6       DECLARE - define a cursor
7

SYNOPSIS

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

DESCRIPTION

13       DECLARE allows a user to create cursors, which can be used to retrieve
14       a small number of rows at a time out of a larger query. After the
15       cursor is created, rows are fetched from it using FETCH(7).
16
17           Note
18           This page describes usage of cursors at the SQL command level. If
19           you are trying to use cursors inside a PL/pgSQL function, the rules
20           are different — see Section 42.7.
21

PARAMETERS

23       name
24           The name of the cursor to be created.
25
26       BINARY
27           Causes the cursor to return data in binary rather than in text
28           format.
29
30       INSENSITIVE
31           Indicates that data retrieved from the cursor should be unaffected
32           by updates to the table(s) underlying the cursor that occur after
33           the cursor is created. In PostgreSQL, this is the default behavior;
34           so this key word has no effect and is only accepted for
35           compatibility with the SQL standard.
36
37       SCROLL
38       NO SCROLL
39           SCROLL specifies that the cursor can be used to retrieve rows in a
40           nonsequential fashion (e.g., backward). Depending upon the
41           complexity of the query's execution plan, specifying SCROLL might
42           impose a performance penalty on the query's execution time.  NO
43           SCROLL specifies that the cursor cannot be used to retrieve rows in
44           a nonsequential fashion. The default is to allow scrolling in some
45           cases; this is not the same as specifying SCROLL. See Notes below
46           for details.
47
48       WITH HOLD
49       WITHOUT HOLD
50           WITH HOLD specifies that the cursor can continue to be used after
51           the transaction that created it successfully commits.  WITHOUT HOLD
52           specifies that the cursor cannot be used outside of the transaction
53           that created it. If neither WITHOUT HOLD nor WITH HOLD is
54           specified, WITHOUT HOLD is the default.
55
56       query
57           A SELECT(7) or VALUES(7) command which will provide the rows to be
58           returned by the cursor.
59
60       The key words BINARY, INSENSITIVE, and SCROLL can appear in any order.
61

NOTES

63       Normal cursors return data in text format, the same as a SELECT would
64       produce. The BINARY option specifies that the cursor should return data
65       in binary format. This reduces conversion effort for both the server
66       and client, at the cost of more programmer effort to deal with
67       platform-dependent binary data formats. As an example, if a query
68       returns a value of one from an integer column, you would get a string
69       of 1 with a default cursor, whereas with a binary cursor you would get
70       a 4-byte field containing the internal representation of the value (in
71       big-endian byte order).
72
73       Binary cursors should be used carefully. Many applications, including
74       psql, are not prepared to handle binary cursors and expect data to come
75       back in the text format.
76
77           Note
78           When the client application uses the “extended query” protocol to
79           issue a FETCH command, the Bind protocol message specifies whether
80           data is to be retrieved in text or binary format. This choice
81           overrides the way that the cursor is defined. The concept of a
82           binary cursor as such is thus obsolete when using extended query
83           protocol — any cursor can be treated as either text or binary.
84
85       Unless WITH HOLD is specified, the cursor created by this command can
86       only be used within the current transaction. Thus, DECLARE without WITH
87       HOLD is useless outside a transaction block: the cursor would survive
88       only to the completion of the statement. Therefore PostgreSQL reports
89       an error if such a command is used outside a transaction block. Use
90       BEGIN(7) and COMMIT(7) (or ROLLBACK(7)) to define a transaction block.
91
92       If WITH HOLD is specified and the transaction that created the cursor
93       successfully commits, the cursor can continue to be accessed by
94       subsequent transactions in the same session. (But if the creating
95       transaction is aborted, the cursor is removed.) A cursor created with
96       WITH HOLD is closed when an explicit CLOSE command is issued on it, or
97       the session ends. In the current implementation, the rows represented
98       by a held cursor are copied into a temporary file or memory area so
99       that they remain available for subsequent transactions.
100
101       WITH HOLD may not be specified when the query includes FOR UPDATE or
102       FOR SHARE.
103
104       The SCROLL option should be specified when defining a cursor that will
105       be used to fetch backwards. This is required by the SQL standard.
106       However, for compatibility with earlier versions, PostgreSQL will allow
107       backward fetches without SCROLL, if the cursor's query plan is simple
108       enough that no extra overhead is needed to support it. However,
109       application developers are advised not to rely on using backward
110       fetches from a cursor that has not been created with SCROLL. If NO
111       SCROLL is specified, then backward fetches are disallowed in any case.
112
113       Backward fetches are also disallowed when the query includes FOR UPDATE
114       or FOR SHARE; therefore SCROLL may not be specified in this case.
115
116           Caution
117           Scrollable cursors may give unexpected results if they invoke any
118           volatile functions (see Section 37.7). When a previously fetched
119           row is re-fetched, the functions might be re-executed, perhaps
120           leading to results different from the first time. It's best to
121           specify NO SCROLL for a query involving volatile functions. If that
122           is not practical, one workaround is to declare the cursor SCROLL
123           WITH HOLD and commit the transaction before reading any rows from
124           it. This will force the entire output of the cursor to be
125           materialized in temporary storage, so that volatile functions are
126           executed exactly once for each row.
127
128       If the cursor's query includes FOR UPDATE or FOR SHARE, then returned
129       rows are locked at the time they are first fetched, in the same way as
130       for a regular SELECT(7) command with these options. In addition, the
131       returned rows will be the most up-to-date versions; therefore these
132       options provide the equivalent of what the SQL standard calls a
133       “sensitive cursor”. (Specifying INSENSITIVE together with FOR UPDATE or
134       FOR SHARE is an error.)
135
136           Caution
137           It is generally recommended to use FOR UPDATE if the cursor is
138           intended to be used with UPDATE ... WHERE CURRENT OF or DELETE ...
139           WHERE CURRENT OF. Using FOR UPDATE prevents other sessions from
140           changing the rows between the time they are fetched and the time
141           they are updated. Without FOR UPDATE, a subsequent WHERE CURRENT OF
142           command will have no effect if the row was changed since the cursor
143           was created.
144
145           Another reason to use FOR UPDATE is that without it, a subsequent
146           WHERE CURRENT OF might fail if the cursor query does not meet the
147           SQL standard's rules for being “simply updatable” (in particular,
148           the cursor must reference just one table and not use grouping or
149           ORDER BY). Cursors that are not simply updatable might work, or
150           might not, depending on plan choice details; so in the worst case,
151           an application might work in testing and then fail in production.
152           If FOR UPDATE is specified, the cursor is guaranteed to be
153           updatable.
154
155           The main reason not to use FOR UPDATE with WHERE CURRENT OF is if
156           you need the cursor to be scrollable, or to be insensitive to the
157           subsequent updates (that is, continue to show the old data). If
158           this is a requirement, pay close heed to the caveats shown above.
159
160       The SQL standard only makes provisions for cursors in embedded SQL. The
161       PostgreSQL server does not implement an OPEN statement for cursors; a
162       cursor is considered to be open when it is declared. However, ECPG, the
163       embedded SQL preprocessor for PostgreSQL, supports the standard SQL
164       cursor conventions, including those involving DECLARE and OPEN
165       statements.
166
167       You can see all available cursors by querying the pg_cursors system
168       view.
169

EXAMPLES

171       To declare a cursor:
172
173           DECLARE liahona CURSOR FOR SELECT * FROM films;
174
175       See FETCH(7) for more examples of cursor usage.
176

COMPATIBILITY

178       The SQL standard says that it is implementation-dependent whether
179       cursors are sensitive to concurrent updates of the underlying data by
180       default. In PostgreSQL, cursors are insensitive by default, and can be
181       made sensitive by specifying FOR UPDATE. Other products may work
182       differently.
183
184       The SQL standard allows cursors only in embedded SQL and in modules.
185       PostgreSQL permits cursors to be used interactively.
186
187       Binary cursors are a PostgreSQL extension.
188

SEE ALSO

190       CLOSE(7), FETCH(7), MOVE(7)
191
192
193
194PostgreSQL 13.4                      2021                           DECLARE(7)
Impressum