2 doc/src/sgml/ref/declare.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-DECLARE">
8 <refentrytitle>DECLARE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>DECLARE</refname>
15 <refpurpose>define a cursor</refpurpose>
18 <indexterm zone="sql-declare">
19 <primary>DECLARE</primary>
22 <indexterm zone="sql-declare">
23 <primary>cursor</primary>
24 <secondary>DECLARE</secondary>
29 DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
30 CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
35 <title>Description</title>
38 <command>DECLARE</command> allows a user to create cursors, which
39 can be used to retrieve
40 a small number of rows at a time out of a larger query.
41 After the cursor is created, rows are fetched from it using
42 <xref linkend="sql-fetch">.
47 This page describes usage of cursors at the SQL command level.
48 If you are trying to use cursors inside a <application>PL/pgSQL</>
49 function, the rules are different —
50 see <xref linkend="plpgsql-cursors">.
56 <title>Parameters</title>
60 <term><replaceable class="parameter">name</replaceable></term>
63 The name of the cursor to be created.
69 <term><literal>BINARY</literal></term>
72 Causes the cursor to return data in binary rather than in text format.
78 <term><literal>INSENSITIVE</literal></term>
81 Indicates that data retrieved from the cursor should be
82 unaffected by updates to the table(s) underlying the cursor that occur
83 after the cursor is created. In <productname>PostgreSQL</productname>,
84 this is the default behavior; so this key word has no
85 effect and is only accepted for compatibility with the SQL standard.
91 <term><literal>SCROLL</literal></term>
92 <term><literal>NO SCROLL</literal></term>
94 <para><literal>SCROLL</literal> specifies that the cursor can be used
95 to retrieve rows in a nonsequential fashion (e.g.,
96 backward). Depending upon the complexity of the query's
97 execution plan, specifying <literal>SCROLL</literal> might impose
98 a performance penalty on the query's execution time.
99 <literal>NO SCROLL</literal> specifies that the cursor cannot be
100 used to retrieve rows in a nonsequential fashion. The default is to
101 allow scrolling in some cases; this is not the same as specifying
102 <literal>SCROLL</literal>. See <xref linkend="sql-declare-notes"
103 endterm="sql-declare-notes-title"> for details.
109 <term><literal>WITH HOLD</literal></term>
110 <term><literal>WITHOUT HOLD</literal></term>
112 <para><literal>WITH HOLD</literal> specifies that the cursor can
113 continue to be used after the transaction that created it
114 successfully commits. <literal>WITHOUT HOLD</literal> specifies
115 that the cursor cannot be used outside of the transaction that
116 created it. If neither <literal>WITHOUT HOLD</literal> nor
117 <literal>WITH HOLD</literal> is specified, <literal>WITHOUT
118 HOLD</literal> is the default.
124 <term><replaceable class="parameter">query</replaceable></term>
127 A <xref linkend="sql-select"> or
128 <xref linkend="sql-values"> command
129 which will provide the rows to be returned by the cursor.
136 The key words <literal>BINARY</literal>,
137 <literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can
142 <refsect1 id="sql-declare-notes">
143 <title id="sql-declare-notes-title">Notes</title>
146 Normal cursors return data in text format, the same as a
147 <command>SELECT</> would produce. The <literal>BINARY</> option
148 specifies that the cursor should return data in binary format.
149 This reduces conversion effort for both the server and client,
150 at the cost of more programmer effort to deal with platform-dependent
152 As an example, if a query returns a value of one from an integer column,
153 you would get a string of <literal>1</> with a default cursor,
154 whereas with a binary cursor you would get
155 a 4-byte field containing the internal representation of the value
156 (in big-endian byte order).
160 Binary cursors should be used carefully. Many applications,
161 including <application>psql</application>, are not prepared to
162 handle binary cursors and expect data to come back in the text
168 When the client application uses the <quote>extended query</> protocol
169 to issue a <command>FETCH</> command, the Bind protocol message
170 specifies whether data is to be retrieved in text or binary format.
171 This choice overrides the way that the cursor is defined. The concept
172 of a binary cursor as such is thus obsolete when using extended query
173 protocol — any cursor can be treated as either text or binary.
178 Unless <literal>WITH HOLD</literal> is specified, the cursor
179 created by this command can only be used within the current
180 transaction. Thus, <command>DECLARE</> without <literal>WITH
181 HOLD</literal> is useless outside a transaction block: the cursor would
182 survive only to the completion of the statement. Therefore
183 <productname>PostgreSQL</productname> reports an error if such a
184 command is used outside a transaction block.
186 <xref linkend="sql-begin"> and
187 <xref linkend="sql-commit">
188 (or <xref linkend="sql-rollback">)
189 to define a transaction block.
193 If <literal>WITH HOLD</literal> is specified and the transaction
194 that created the cursor successfully commits, the cursor can
195 continue to be accessed by subsequent transactions in the same
196 session. (But if the creating transaction is aborted, the cursor
197 is removed.) A cursor created with <literal>WITH HOLD</literal>
198 is closed when an explicit <command>CLOSE</command> command is
199 issued on it, or the session ends. In the current implementation,
200 the rows represented by a held cursor are copied into a temporary
201 file or memory area so that they remain available for subsequent
206 <literal>WITH HOLD</literal> may not be specified when the query
207 includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.
211 The <literal>SCROLL</> option should be specified when defining a
212 cursor that will be used to fetch backwards. This is required by
213 the SQL standard. However, for compatibility with earlier
214 versions, <productname>PostgreSQL</productname> will allow
215 backward fetches without <literal>SCROLL</>, if the cursor's query
216 plan is simple enough that no extra overhead is needed to support
217 it. However, application developers are advised not to rely on
218 using backward fetches from a cursor that has not been created
219 with <literal>SCROLL</literal>. If <literal>NO SCROLL</> is
220 specified, then backward fetches are disallowed in any case.
224 Backward fetches are also disallowed when the query
225 includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>; therefore
226 <literal>SCROLL</literal> may not be specified in this case.
231 Scrollable and <literal>WITH HOLD</literal> cursors may give unexpected
232 results if they invoke any volatile functions (see <xref
233 linkend="xfunc-volatility">). When a previously fetched row is
234 re-fetched, the functions might be re-executed, perhaps leading to
235 results different from the first time. One workaround for such cases
236 is to declare the cursor <literal>WITH HOLD</literal> and commit the
237 transaction before reading any rows from it. This will force the
238 entire output of the cursor to be materialized in temporary storage,
239 so that volatile functions are executed exactly once for each row.
244 If the cursor's query includes <literal>FOR UPDATE</> or <literal>FOR
245 SHARE</>, then returned rows are locked at the time they are first
246 fetched, in the same way as for a regular
247 <xref linkend="sql-select"> command with
249 In addition, the returned rows will be the most up-to-date versions;
250 therefore these options provide the equivalent of what the SQL standard
251 calls a <quote>sensitive cursor</>. (Specifying <literal>INSENSITIVE</>
252 together with <literal>FOR UPDATE</> or <literal>FOR SHARE</> is an error.)
257 It is generally recommended to use <literal>FOR UPDATE</> if the cursor
258 is intended to be used with <command>UPDATE ... WHERE CURRENT OF</> or
259 <command>DELETE ... WHERE CURRENT OF</>. Using <literal>FOR UPDATE</>
260 prevents other sessions from changing the rows between the time they are
261 fetched and the time they are updated. Without <literal>FOR UPDATE</>,
262 a subsequent <literal>WHERE CURRENT OF</> command will have no effect if
263 the row was changed since the cursor was created.
267 Another reason to use <literal>FOR UPDATE</> is that without it, a
268 subsequent <literal>WHERE CURRENT OF</> might fail if the cursor query
269 does not meet the SQL standard's rules for being <quote>simply
270 updatable</> (in particular, the cursor must reference just one table
271 and not use grouping or <literal>ORDER BY</>). Cursors
272 that are not simply updatable might work, or might not, depending on plan
273 choice details; so in the worst case, an application might work in testing
274 and then fail in production.
278 The main reason not to use <literal>FOR UPDATE</> with <literal>WHERE
279 CURRENT OF</> is if you need the cursor to be scrollable, or to be
280 insensitive to the subsequent updates (that is, continue to show the old
281 data). If this is a requirement, pay close heed to the caveats shown
287 The SQL standard only makes provisions for cursors in embedded
288 <acronym>SQL</acronym>. The <productname>PostgreSQL</productname>
289 server does not implement an <command>OPEN</command> statement for
290 cursors; a cursor is considered to be open when it is declared.
291 However, <application>ECPG</application>, the embedded SQL
292 preprocessor for <productname>PostgreSQL</productname>, supports
293 the standard SQL cursor conventions, including those involving
294 <command>DECLARE</command> and <command>OPEN</command> statements.
298 You can see all available cursors by querying the <link
299 linkend="view-pg-cursors"><structname>pg_cursors</structname></link>
305 <title>Examples</title>
310 DECLARE liahona CURSOR FOR SELECT * FROM films;
312 See <xref linkend="sql-fetch"> for more
313 examples of cursor usage.
318 <title>Compatibility</title>
321 The SQL standard says that it is implementation-dependent whether cursors
322 are sensitive to concurrent updates of the underlying data by default. In
323 <productname>PostgreSQL</productname>, cursors are insensitive by default,
324 and can be made sensitive by specifying <literal>FOR UPDATE</>. Other
325 products may work differently.
329 The SQL standard allows cursors only in embedded
330 <acronym>SQL</acronym> and in modules. <productname>PostgreSQL</>
331 permits cursors to be used interactively.
335 Binary cursors are a <productname>PostgreSQL</productname>
341 <title>See Also</title>
343 <simplelist type="inline">
344 <member><xref linkend="sql-close"></member>
345 <member><xref linkend="sql-fetch"></member>
346 <member><xref linkend="sql-move"></member>