| 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 — |
| 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 — 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> |