Debian Codegraph @

doc/src/sgml/ref/declare.sgml

raw
1 <!--
2 doc/src/sgml/ref/declare.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-declare">
7 <indexterm zone="sql-declare">
8 <primary>DECLARE</primary>
9 </indexterm>
10
11 <indexterm zone="sql-declare">
12 <primary>cursor</primary>
13 <secondary>DECLARE</secondary>
14 </indexterm>
15
16 <indexterm>
17 <primary>portal</primary>
18 <secondary>DECLARE</secondary>
19 </indexterm>
20
21 <refmeta>
22 <refentrytitle>DECLARE</refentrytitle>
23 <manvolnum>7</manvolnum>
24 <refmiscinfo>SQL - Language Statements</refmiscinfo>
25 </refmeta>
26
27 <refnamediv>
28 <refname>DECLARE</refname>
29 <refpurpose>define a cursor</refpurpose>
30 </refnamediv>
31
32 <refsynopsisdiv>
33 <synopsis>
34 DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ ASENSITIVE | INSENSITIVE ] [ [ NO ] SCROLL ]
35 CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
36 </synopsis>
37 </refsynopsisdiv>
38
39 <refsect1>
40 <title>Description</title>
41
42 <para>
43 <command>DECLARE</command> allows a user to create cursors, which
44 can be used to retrieve
45 a small number of rows at a time out of a larger query.
46 After the cursor is created, rows are fetched from it using
47 <link linkend="sql-fetch"><command>FETCH</command></link>.
48 </para>
49
50 <note>
51 <para>
52 This page describes usage of cursors at the SQL command level.
53 If you are trying to use cursors inside a <application>PL/pgSQL</application>
54 function, the rules are different &mdash;
55 see <xref linkend="plpgsql-cursors"/>.
56 </para>
57 </note>
58 </refsect1>
59
60 <refsect1>
61 <title>Parameters</title>
62
63 <variablelist>
64 <varlistentry>
65 <term><replaceable class="parameter">name</replaceable></term>
66 <listitem>
67 <para>
68 The name of the cursor to be created.
69 This must be different from any other active cursor name in the
70 session.
71 </para>
72 </listitem>
73 </varlistentry>
74
75 <varlistentry>
76 <term><literal>BINARY</literal></term>
77 <listitem>
78 <para>
79 Causes the cursor to return data in binary rather than in text format.
80 </para>
81 </listitem>
82 </varlistentry>
83
84 <varlistentry>
85 <term><literal>ASENSITIVE</literal></term>
86 <term><literal>INSENSITIVE</literal></term>
87 <listitem>
88 <para>
89 Cursor sensitivity determines whether changes to the data underlying the
90 cursor, done in the same transaction, after the cursor has been
91 declared, are visible in the cursor. <literal>INSENSITIVE</literal>
92 means they are not visible, <literal>ASENSITIVE</literal> means the
93 behavior is implementation-dependent. A third behavior,
94 <literal>SENSITIVE</literal>, meaning that such changes are visible in
95 the cursor, is not available in <productname>PostgreSQL</productname>.
96 In <productname>PostgreSQL</productname>, all cursors are insensitive;
97 so these key words have no effect and are only accepted for
98 compatibility with the SQL standard.
99 </para>
100
101 <para>
102 Specifying <literal>INSENSITIVE</literal> together with <literal>FOR
103 UPDATE</literal> or <literal>FOR SHARE</literal> is an error.
104 </para>
105 </listitem>
106 </varlistentry>
107
108 <varlistentry>
109 <term><literal>SCROLL</literal></term>
110 <term><literal>NO SCROLL</literal></term>
111 <listitem>
112 <para><literal>SCROLL</literal> specifies that the cursor can be used
113 to retrieve rows in a nonsequential fashion (e.g.,
114 backward). Depending upon the complexity of the query's
115 execution plan, specifying <literal>SCROLL</literal> might impose
116 a performance penalty on the query's execution time.
117 <literal>NO SCROLL</literal> specifies that the cursor cannot be
118 used to retrieve rows in a nonsequential fashion. The default is to
119 allow scrolling in some cases; this is not the same as specifying
120 <literal>SCROLL</literal>. See <xref linkend="sql-declare-notes"/>
121 below for details.
122 </para>
123 </listitem>
124 </varlistentry>
125
126 <varlistentry>
127 <term><literal>WITH HOLD</literal></term>
128 <term><literal>WITHOUT HOLD</literal></term>
129 <listitem>
130 <para><literal>WITH HOLD</literal> specifies that the cursor can
131 continue to be used after the transaction that created it
132 successfully commits. <literal>WITHOUT HOLD</literal> specifies
133 that the cursor cannot be used outside of the transaction that
134 created it. If neither <literal>WITHOUT HOLD</literal> nor
135 <literal>WITH HOLD</literal> is specified, <literal>WITHOUT
136 HOLD</literal> is the default.
137 </para>
138 </listitem>
139 </varlistentry>
140
141 <varlistentry>
142 <term><replaceable class="parameter">query</replaceable></term>
143 <listitem>
144 <para>
145 A <link linkend="sql-select"><command>SELECT</command></link> or
146 <link linkend="sql-values"><command>VALUES</command></link> command
147 which will provide the rows to be returned by the cursor.
148 </para>
149 </listitem>
150 </varlistentry>
151 </variablelist>
152
153 <para>
154 The key words <literal>ASENSITIVE</literal>, <literal>BINARY</literal>,
155 <literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can
156 appear in any order.
157 </para>
158 </refsect1>
159
160 <refsect1 id="sql-declare-notes" xreflabel="Notes">
161 <title>Notes</title>
162
163 <para>
164 Normal cursors return data in text format, the same as a
165 <command>SELECT</command> would produce. The <literal>BINARY</literal> option
166 specifies that the cursor should return data in binary format.
167 This reduces conversion effort for both the server and client,
168 at the cost of more programmer effort to deal with platform-dependent
169 binary data formats.
170 As an example, if a query returns a value of one from an integer column,
171 you would get a string of <literal>1</literal> with a default cursor,
172 whereas with a binary cursor you would get
173 a 4-byte field containing the internal representation of the value
174 (in big-endian byte order).
175 </para>
176
177 <para>
178 Binary cursors should be used carefully. Many applications,
179 including <application>psql</application>, are not prepared to
180 handle binary cursors and expect data to come back in the text
181 format.
182 </para>
183
184 <note>
185 <para>
186 When the client application uses the <quote>extended query</quote> protocol
187 to issue a <command>FETCH</command> command, the Bind protocol message
188 specifies whether data is to be retrieved in text or binary format.
189 This choice overrides the way that the cursor is defined. The concept
190 of a binary cursor as such is thus obsolete when using extended query
191 protocol &mdash; any cursor can be treated as either text or binary.
192 </para>
193 </note>
194
195 <para>
196 Unless <literal>WITH HOLD</literal> is specified, the cursor
197 created by this command can only be used within the current
198 transaction. Thus, <command>DECLARE</command> without <literal>WITH
199 HOLD</literal> is useless outside a transaction block: the cursor would
200 survive only to the completion of the statement. Therefore
201 <productname>PostgreSQL</productname> reports an error if such a
202 command is used outside a transaction block.
203 Use
204 <link linkend="sql-begin"><command>BEGIN</command></link> and
205 <link linkend="sql-commit"><command>COMMIT</command></link>
206 (or <link linkend="sql-rollback"><command>ROLLBACK</command></link>)
207 to define a transaction block.
208 </para>
209
210 <para>
211 If <literal>WITH HOLD</literal> is specified and the transaction
212 that created the cursor successfully commits, the cursor can
213 continue to be accessed by subsequent transactions in the same
214 session. (But if the creating transaction is aborted, the cursor
215 is removed.) A cursor created with <literal>WITH HOLD</literal>
216 is closed when an explicit <command>CLOSE</command> command is
217 issued on it, or the session ends. In the current implementation,
218 the rows represented by a held cursor are copied into a temporary
219 file or memory area so that they remain available for subsequent
220 transactions.
221 </para>
222
223 <para>
224 <literal>WITH HOLD</literal> may not be specified when the query
225 includes <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>.
226 </para>
227
228 <para>
229 The <literal>SCROLL</literal> option should be specified when defining a
230 cursor that will be used to fetch backwards. This is required by
231 the SQL standard. However, for compatibility with earlier
232 versions, <productname>PostgreSQL</productname> will allow
233 backward fetches without <literal>SCROLL</literal>, if the cursor's query
234 plan is simple enough that no extra overhead is needed to support
235 it. However, application developers are advised not to rely on
236 using backward fetches from a cursor that has not been created
237 with <literal>SCROLL</literal>. If <literal>NO SCROLL</literal> is
238 specified, then backward fetches are disallowed in any case.
239 </para>
240
241 <para>
242 Backward fetches are also disallowed when the query
243 includes <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal>; therefore
244 <literal>SCROLL</literal> may not be specified in this case.
245 </para>
246
247 <caution>
248 <para>
249 Scrollable cursors may give unexpected
250 results if they invoke any volatile functions (see <xref
251 linkend="xfunc-volatility"/>). When a previously fetched row is
252 re-fetched, the functions might be re-executed, perhaps leading to
253 results different from the first time. It's best to
254 specify <literal>NO SCROLL</literal> for a query involving volatile
255 functions. If that is not practical, one workaround
256 is to declare the cursor <literal>SCROLL WITH HOLD</literal> and commit the
257 transaction before reading any rows from it. This will force the
258 entire output of the cursor to be materialized in temporary storage,
259 so that volatile functions are executed exactly once for each row.
260 </para>
261 </caution>
262
263 <para>
264 If the cursor's query includes <literal>FOR UPDATE</literal> or <literal>FOR
265 SHARE</literal>, then returned rows are locked at the time they are first
266 fetched, in the same way as for a regular
267 <link linkend="sql-select"><command>SELECT</command></link> command with
268 these options.
269 In addition, the returned rows will be the most up-to-date versions.
270 </para>
271
272 <caution>
273 <para>
274 It is generally recommended to use <literal>FOR UPDATE</literal> if the cursor
275 is intended to be used with <command>UPDATE ... WHERE CURRENT OF</command> or
276 <command>DELETE ... WHERE CURRENT OF</command>. Using <literal>FOR UPDATE</literal>
277 prevents other sessions from changing the rows between the time they are
278 fetched and the time they are updated. Without <literal>FOR UPDATE</literal>,
279 a subsequent <literal>WHERE CURRENT OF</literal> command will have no effect if
280 the row was changed since the cursor was created.
281 </para>
282
283 <para>
284 Another reason to use <literal>FOR UPDATE</literal> is that without it, a
285 subsequent <literal>WHERE CURRENT OF</literal> might fail if the cursor query
286 does not meet the SQL standard's rules for being <quote>simply
287 updatable</quote> (in particular, the cursor must reference just one table
288 and not use grouping or <literal>ORDER BY</literal>). Cursors
289 that are not simply updatable might work, or might not, depending on plan
290 choice details; so in the worst case, an application might work in testing
291 and then fail in production. If <literal>FOR UPDATE</literal> is
292 specified, the cursor is guaranteed to be updatable.
293 </para>
294
295 <para>
296 The main reason not to use <literal>FOR UPDATE</literal> with <literal>WHERE
297 CURRENT OF</literal> is if you need the cursor to be scrollable, or to be
298 isolated from concurrent updates (that is, continue to show the old
299 data). If this is a requirement, pay close heed to the caveats shown
300 above.
301 </para>
302 </caution>
303
304 <para>
305 The SQL standard only makes provisions for cursors in embedded
306 <acronym>SQL</acronym>. The <productname>PostgreSQL</productname>
307 server does not implement an <command>OPEN</command> statement for
308 cursors; a cursor is considered to be open when it is declared.
309 However, <application>ECPG</application>, the embedded SQL
310 preprocessor for <productname>PostgreSQL</productname>, supports
311 the standard SQL cursor conventions, including those involving
312 <command>DECLARE</command> and <command>OPEN</command> statements.
313 </para>
314
315 <para>
316 The server data structure underlying an open cursor is called a
317 <firstterm>portal</firstterm>. Portal names are exposed in the
318 client protocol: a client can fetch rows directly from an open
319 portal, if it knows the portal name. When creating a cursor with
320 <command>DECLARE</command>, the portal name is the same as the
321 cursor name.
322 </para>
323
324 <para>
325 You can see all available cursors by querying the <link
326 linkend="view-pg-cursors"><structname>pg_cursors</structname></link>
327 system view.
328 </para>
329 </refsect1>
330
331 <refsect1>
332 <title>Examples</title>
333
334 <para>
335 To declare a cursor:
336 <programlisting>
337 DECLARE liahona CURSOR FOR SELECT * FROM films;
338 </programlisting>
339 See <xref linkend="sql-fetch"/> for more
340 examples of cursor usage.
341 </para>
342 </refsect1>
343
344 <refsect1>
345 <title>Compatibility</title>
346
347 <para>
348 The SQL standard allows cursors only in embedded
349 <acronym>SQL</acronym> and in modules. <productname>PostgreSQL</productname>
350 permits cursors to be used interactively.
351 </para>
352
353 <para>
354 According to the SQL standard, changes made to insensitive cursors by
355 <literal>UPDATE ... WHERE CURRENT OF</literal> and <literal>DELETE
356 ... WHERE CURRENT OF</literal> statements are visible in that same
357 cursor. <productname>PostgreSQL</productname> treats these statements like
358 all other data changing statements in that they are not visible in
359 insensitive cursors.
360 </para>
361
362 <para>
363 Binary cursors are a <productname>PostgreSQL</productname>
364 extension.
365 </para>
366 </refsect1>
367
368 <refsect1>
369 <title>See Also</title>
370
371 <simplelist type="inline">
372 <member><xref linkend="sql-close"/></member>
373 <member><xref linkend="sql-fetch"/></member>
374 <member><xref linkend="sql-move"/></member>
375 </simplelist>
376 </refsect1>
377 </refentry>