1DECLARE(7)               PostgreSQL 13.3 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 and WITH HOLD cursors may give unexpected results if
118           they invoke any volatile functions (see Section 37.7). When a
119           previously fetched row is re-fetched, the functions might be
120           re-executed, perhaps leading to results different from the first
121           time. One workaround for such cases is to declare the cursor WITH
122           HOLD and commit the transaction before reading any rows from it.
123           This will force the entire output of the cursor to be materialized
124           in temporary storage, so that volatile functions are executed
125           exactly once for each row.
126
127       If the cursor's query includes FOR UPDATE or FOR SHARE, then returned
128       rows are locked at the time they are first fetched, in the same way as
129       for a regular SELECT(7) command with these options. In addition, the
130       returned rows will be the most up-to-date versions; therefore these
131       options provide the equivalent of what the SQL standard calls a
132       “sensitive cursor”. (Specifying INSENSITIVE together with FOR UPDATE or
133       FOR SHARE is an error.)
134
135           Caution
136           It is generally recommended to use FOR UPDATE if the cursor is
137           intended to be used with UPDATE ... WHERE CURRENT OF or DELETE ...
138           WHERE CURRENT OF. Using FOR UPDATE prevents other sessions from
139           changing the rows between the time they are fetched and the time
140           they are updated. Without FOR UPDATE, a subsequent WHERE CURRENT OF
141           command will have no effect if the row was changed since the cursor
142           was created.
143
144           Another reason to use FOR UPDATE is that without it, a subsequent
145           WHERE CURRENT OF might fail if the cursor query does not meet the
146           SQL standard's rules for being “simply updatable” (in particular,
147           the cursor must reference just one table and not use grouping or
148           ORDER BY). Cursors that are not simply updatable might work, or
149           might not, depending on plan choice details; so in the worst case,
150           an application might work in testing and then fail in production.
151           If FOR UPDATE is specified, the cursor is guaranteed to be
152           updatable.
153
154           The main reason not to use FOR UPDATE with WHERE CURRENT OF is if
155           you need the cursor to be scrollable, or to be insensitive to the
156           subsequent updates (that is, continue to show the old data). If
157           this is a requirement, pay close heed to the caveats shown above.
158
159       The SQL standard only makes provisions for cursors in embedded SQL. The
160       PostgreSQL server does not implement an OPEN statement for cursors; a
161       cursor is considered to be open when it is declared. However, ECPG, the
162       embedded SQL preprocessor for PostgreSQL, supports the standard SQL
163       cursor conventions, including those involving DECLARE and OPEN
164       statements.
165
166       You can see all available cursors by querying the pg_cursors system
167       view.
168

EXAMPLES

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

COMPATIBILITY

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

SEE ALSO

189       CLOSE(7), FETCH(7), MOVE(7)
190
191
192
193PostgreSQL 13.3                      2021                           DECLARE(7)
Impressum