1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/declare.sgml,v 1.41 2007/06/11 01:16:21 tgl Exp $
PostgreSQL documentation
-->
<refentry id="SQL-DECLARE">
<refmeta>
<refentrytitle id="SQL-DECLARE-TITLE">DECLARE</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>DECLARE</refname>
<refpurpose>define a cursor</refpurpose>
</refnamediv>
<indexterm zone="sql-declare">
<primary>DECLARE</primary>
</indexterm>
<indexterm zone="sql-declare">
<primary>cursor</primary>
<secondary>DECLARE</secondary>
</indexterm>
<refsynopsisdiv>
<synopsis>
DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable>
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>DECLARE</command> 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
<xref linkend="sql-fetch" endterm="sql-fetch-title">.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
The name of the cursor to be created.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>BINARY</literal></term>
<listitem>
<para>
Causes the cursor to return data in binary rather than in text format.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>INSENSITIVE</literal></term>
<listitem>
<para>
Indicates that data retrieved from the cursor should be
unaffected by updates to the table(s) underlying the cursor that occur
after the cursor is created. In <productname>PostgreSQL</productname>,
this is the default behavior; so this key word has no
effect and is only accepted for compatibility with the SQL standard.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>SCROLL</literal></term>
<term><literal>NO SCROLL</literal></term>
<listitem>
<para>
<literal>SCROLL</literal> specifies that the cursor can be used
to retrieve rows in a nonsequential fashion (e.g.,
backward). Depending upon the complexity of the query's
execution plan, specifying <literal>SCROLL</literal> might impose
a performance penalty on the query's execution time.
<literal>NO SCROLL</literal> specifies that the cursor cannot be
used to retrieve rows in a nonsequential fashion. The default is to
allow scrolling in some cases; this is not the same as specifying
<literal>SCROLL</literal>. See <xref linkend="sql-declare-notes"
endterm="sql-declare-notes-title"> for details.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>WITH HOLD</literal></term>
<term><literal>WITHOUT HOLD</literal></term>
<listitem>
<para>
<literal>WITH HOLD</literal> specifies that the cursor can
continue to be used after the transaction that created it
successfully commits. <literal>WITHOUT HOLD</literal> specifies
that the cursor cannot be used outside of the transaction that
created it. If neither <literal>WITHOUT HOLD</literal> nor
<literal>WITH HOLD</literal> is specified, <literal>WITHOUT
HOLD</literal> is the default.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">query</replaceable></term>
<listitem>
<para>
A <xref linkend="sql-select" endterm="sql-select-title"> or
<xref linkend="sql-values" endterm="sql-values-title"> command
which will provide the rows to be returned by the cursor.
</para>
</listitem>
</varlistentry>
</variablelist>
<para>
The key words <literal>BINARY</literal>,
<literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can
appear in any order.
</para>
</refsect1>
<refsect1 id="sql-declare-notes">
<title id="sql-declare-notes-title">Notes</title>
<para>
Normal cursors return data in text format, the same as a
<command>SELECT</> would produce. The <literal>BINARY</> option
specifies that the cursor should return data in binary format.
This reduces conversion effort for both the server and client,
at the cost of more programmer effort to deal with platform-dependent
binary data formats.
As an example, if a query returns a value of one from an integer column,
you would get a string of <literal>1</> with a default cursor,
whereas with a binary cursor you would get
a 4-byte field containing the internal representation of the value
(in big-endian byte order).
</para>
<para>
Binary cursors should be used carefully. Many applications,
including <application>psql</application>, are not prepared to
handle binary cursors and expect data to come back in the text
format.
</para>
<note>
<para>
When the client application uses the <quote>extended query</> protocol
to issue a <command>FETCH</> command, the Bind protocol message
specifies whether data is to be retrieved in text or binary format.
This choice overrides the way that the cursor is defined. The concept
of a binary cursor as such is thus obsolete when using extended query
protocol — any cursor can be treated as either text or binary.
</para>
</note>
<para>
Unless <literal>WITH HOLD</literal> is specified, the cursor
created by this command can only be used within the current
transaction. Thus, <command>DECLARE</> without <literal>WITH
HOLD</literal> is useless outside a transaction block: the cursor would
survive only to the completion of the statement. Therefore
<productname>PostgreSQL</productname> reports an error if this
command is used outside a transaction block.
Use
<xref linkend="sql-begin" endterm="sql-begin-title">,
<xref linkend="sql-commit" endterm="sql-commit-title">
and
<xref linkend="sql-rollback" endterm="sql-rollback-title">
to define a transaction block.
</para>
<para>
If <literal>WITH HOLD</literal> is specified and the transaction
that created the cursor successfully commits, the cursor can
continue to be accessed by subsequent transactions in the same
session. (But if the creating transaction is aborted, the cursor
is removed.) A cursor created with <literal>WITH HOLD</literal>
is closed when an explicit <command>CLOSE</command> command is
issued on it, or the session ends. In the current implementation,
the rows represented by a held cursor are copied into a temporary
file or memory area so that they remain available for subsequent
transactions.
</para>
<para>
<literal>WITH HOLD</literal> may not be specified when the query
includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>.
</para>
<para>
The <literal>SCROLL</> option should be specified when defining a
cursor that will be used to fetch backwards. This is required by
the SQL standard. However, for compatibility with earlier
versions, <productname>PostgreSQL</productname> will allow
backward fetches without <literal>SCROLL</>, if the cursor's query
plan is simple enough that no extra overhead is needed to support
it. However, application developers are advised not to rely on
using backward fetches from a cursor that has not been created
with <literal>SCROLL</literal>. If <literal>NO SCROLL</> is
specified, then backward fetches are disallowed in any case.
</para>
<para>
If the cursor's query includes <literal>FOR UPDATE</> or <literal>FOR
SHARE</>, then returned rows are locked at the time they are first
fetched, in the same way as for a regular
<xref linkend="sql-select" endterm="sql-select-title"> command with
these options.
In addition, the returned rows will be the most up-to-date versions;
therefore these options provide the equivalent of what the SQL standard
calls a <quote>sensitive cursor</>. It is often wise to use <literal>FOR
UPDATE</> if the cursor is intended to be used with <command>UPDATE
... WHERE CURRENT OF</> or <command>DELETE ... WHERE CURRENT OF</>,
since this will prevent other sessions from changing the rows between
the time they are fetched and the time they are updated. Without
<literal>FOR UPDATE</>, a subsequent <literal>WHERE CURRENT OF</> command
will have no effect if the row was changed meanwhile.
</para>
<para>
The SQL standard only makes provisions for cursors in embedded
<acronym>SQL</acronym>. The <productname>PostgreSQL</productname>
server does not implement an <command>OPEN</command> statement for
cursors; a cursor is considered to be open when it is declared.
However, <application>ECPG</application>, the embedded SQL
preprocessor for <productname>PostgreSQL</productname>, supports
the standard SQL cursor conventions, including those involving
<command>DECLARE</command> and <command>OPEN</command> statements.
</para>
<para>
You can see all available cursors by querying the <link
linkend="view-pg-cursors"><structname>pg_cursors</structname></link>
system view.
</para>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
To declare a cursor:
<programlisting>
DECLARE liahona CURSOR FOR SELECT * FROM films;
</programlisting>
See <xref linkend="sql-fetch" endterm="sql-fetch-title"> for more
examples of cursor usage.
</para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
The SQL standard specifies that by default, cursors are sensitive to
concurrent updates of the underlying data. In
<productname>PostgreSQL</productname>, cursors are insensitive by default,
and can be made sensitive by specifying <literal>FOR UPDATE</>.
</para>
<para>
The SQL standard allows cursors only in embedded
<acronym>SQL</acronym> and in modules. <productname>PostgreSQL</>
permits cursors to be used interactively.
</para>
<para>
Binary cursors are a <productname>PostgreSQL</productname>
extension.
</para>
</refsect1>
<refsect1>
<title>See Also</title>
<simplelist type="inline">
<member><xref linkend="sql-close" endterm="sql-close-title"></member>
<member><xref linkend="sql-fetch" endterm="sql-fetch-title"></member>
<member><xref linkend="sql-move" endterm="sql-move-title"></member>
</simplelist>
</refsect1>
</refentry>