1<!-- 2doc/src/sgml/ref/declare.sgml 3PostgreSQL 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 <refmeta> 17 <refentrytitle>DECLARE</refentrytitle> 18 <manvolnum>7</manvolnum> 19 <refmiscinfo>SQL - Language Statements</refmiscinfo> 20 </refmeta> 21 22 <refnamediv> 23 <refname>DECLARE</refname> 24 <refpurpose>define a cursor</refpurpose> 25 </refnamediv> 26 27 <refsynopsisdiv> 28<synopsis> 29DECLARE <replaceable class="parameter">name</replaceable> [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] 30 CURSOR [ { WITH | WITHOUT } HOLD ] FOR <replaceable class="parameter">query</replaceable> 31</synopsis> 32 </refsynopsisdiv> 33 34 <refsect1> 35 <title>Description</title> 36 37 <para> 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">. 43 </para> 44 45 <note> 46 <para> 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">. 51 </para> 52 </note> 53 </refsect1> 54 55 <refsect1> 56 <title>Parameters</title> 57 58 <variablelist> 59 <varlistentry> 60 <term><replaceable class="parameter">name</replaceable></term> 61 <listitem> 62 <para> 63 The name of the cursor to be created. 64 </para> 65 </listitem> 66 </varlistentry> 67 68 <varlistentry> 69 <term><literal>BINARY</literal></term> 70 <listitem> 71 <para> 72 Causes the cursor to return data in binary rather than in text format. 73 </para> 74 </listitem> 75 </varlistentry> 76 77 <varlistentry> 78 <term><literal>INSENSITIVE</literal></term> 79 <listitem> 80 <para> 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. 86 </para> 87 </listitem> 88 </varlistentry> 89 90 <varlistentry> 91 <term><literal>SCROLL</literal></term> 92 <term><literal>NO SCROLL</literal></term> 93 <listitem> 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. 104 </para> 105 </listitem> 106 </varlistentry> 107 108 <varlistentry> 109 <term><literal>WITH HOLD</literal></term> 110 <term><literal>WITHOUT HOLD</literal></term> 111 <listitem> 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. 119 </para> 120 </listitem> 121 </varlistentry> 122 123 <varlistentry> 124 <term><replaceable class="parameter">query</replaceable></term> 125 <listitem> 126 <para> 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. 130 </para> 131 </listitem> 132 </varlistentry> 133 </variablelist> 134 135 <para> 136 The key words <literal>BINARY</literal>, 137 <literal>INSENSITIVE</literal>, and <literal>SCROLL</literal> can 138 appear in any order. 139 </para> 140 </refsect1> 141 142 <refsect1 id="sql-declare-notes"> 143 <title id="sql-declare-notes-title">Notes</title> 144 145 <para> 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 151 binary data formats. 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). 157 </para> 158 159 <para> 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 163 format. 164 </para> 165 166 <note> 167 <para> 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. 174 </para> 175 </note> 176 177 <para> 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. 185 Use 186 <xref linkend="sql-begin"> and 187 <xref linkend="sql-commit"> 188 (or <xref linkend="sql-rollback">) 189 to define a transaction block. 190 </para> 191 192 <para> 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 202 transactions. 203 </para> 204 205 <para> 206 <literal>WITH HOLD</literal> may not be specified when the query 207 includes <literal>FOR UPDATE</> or <literal>FOR SHARE</>. 208 </para> 209 210 <para> 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. 221 </para> 222 223 <para> 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. 227 </para> 228 229 <caution> 230 <para> 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. 240 </para> 241 </caution> 242 243 <para> 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 248 these options. 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.) 253 </para> 254 255 <caution> 256 <para> 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. 264 </para> 265 266 <para> 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. If <literal>FOR UPDATE</literal> is 275 specified, the cursor is guaranteed to be updatable. 276 </para> 277 278 <para> 279 The main reason not to use <literal>FOR UPDATE</> with <literal>WHERE 280 CURRENT OF</> is if you need the cursor to be scrollable, or to be 281 insensitive to the subsequent updates (that is, continue to show the old 282 data). If this is a requirement, pay close heed to the caveats shown 283 above. 284 </para> 285 </caution> 286 287 <para> 288 The SQL standard only makes provisions for cursors in embedded 289 <acronym>SQL</acronym>. The <productname>PostgreSQL</productname> 290 server does not implement an <command>OPEN</command> statement for 291 cursors; a cursor is considered to be open when it is declared. 292 However, <application>ECPG</application>, the embedded SQL 293 preprocessor for <productname>PostgreSQL</productname>, supports 294 the standard SQL cursor conventions, including those involving 295 <command>DECLARE</command> and <command>OPEN</command> statements. 296 </para> 297 298 <para> 299 You can see all available cursors by querying the <link 300 linkend="view-pg-cursors"><structname>pg_cursors</structname></link> 301 system view. 302 </para> 303 </refsect1> 304 305 <refsect1> 306 <title>Examples</title> 307 308 <para> 309 To declare a cursor: 310<programlisting> 311DECLARE liahona CURSOR FOR SELECT * FROM films; 312</programlisting> 313 See <xref linkend="sql-fetch"> for more 314 examples of cursor usage. 315 </para> 316 </refsect1> 317 318 <refsect1> 319 <title>Compatibility</title> 320 321 <para> 322 The SQL standard says that it is implementation-dependent whether cursors 323 are sensitive to concurrent updates of the underlying data by default. In 324 <productname>PostgreSQL</productname>, cursors are insensitive by default, 325 and can be made sensitive by specifying <literal>FOR UPDATE</>. Other 326 products may work differently. 327 </para> 328 329 <para> 330 The SQL standard allows cursors only in embedded 331 <acronym>SQL</acronym> and in modules. <productname>PostgreSQL</> 332 permits cursors to be used interactively. 333 </para> 334 335 <para> 336 Binary cursors are a <productname>PostgreSQL</productname> 337 extension. 338 </para> 339 </refsect1> 340 341 <refsect1> 342 <title>See Also</title> 343 344 <simplelist type="inline"> 345 <member><xref linkend="sql-close"></member> 346 <member><xref linkend="sql-fetch"></member> 347 <member><xref linkend="sql-move"></member> 348 </simplelist> 349 </refsect1> 350</refentry> 351