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</application> 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 below 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" xreflabel="Notes"> 143 <title>Notes</title> 144 145 <para> 146 Normal cursors return data in text format, the same as a 147 <command>SELECT</command> would produce. The <literal>BINARY</literal> 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</literal> 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</quote> protocol 169 to issue a <command>FETCH</command> 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</command> 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</literal> or <literal>FOR SHARE</literal>. 208 </para> 209 210 <para> 211 The <literal>SCROLL</literal> 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</literal>, 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</literal> 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</literal> or <literal>FOR SHARE</literal>; therefore 226 <literal>SCROLL</literal> may not be specified in this case. 227 </para> 228 229 <caution> 230 <para> 231 Scrollable 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. It's best to 236 specify <literal>NO SCROLL</literal> for a query involving volatile 237 functions. If that is not practical, one workaround 238 is to declare the cursor <literal>SCROLL WITH HOLD</literal> and commit the 239 transaction before reading any rows from it. This will force the 240 entire output of the cursor to be materialized in temporary storage, 241 so that volatile functions are executed exactly once for each row. 242 </para> 243 </caution> 244 245 <para> 246 If the cursor's query includes <literal>FOR UPDATE</literal> or <literal>FOR 247 SHARE</literal>, then returned rows are locked at the time they are first 248 fetched, in the same way as for a regular 249 <xref linkend="sql-select"/> command with 250 these options. 251 In addition, the returned rows will be the most up-to-date versions; 252 therefore these options provide the equivalent of what the SQL standard 253 calls a <quote>sensitive cursor</quote>. (Specifying <literal>INSENSITIVE</literal> 254 together with <literal>FOR UPDATE</literal> or <literal>FOR SHARE</literal> is an error.) 255 </para> 256 257 <caution> 258 <para> 259 It is generally recommended to use <literal>FOR UPDATE</literal> if the cursor 260 is intended to be used with <command>UPDATE ... WHERE CURRENT OF</command> or 261 <command>DELETE ... WHERE CURRENT OF</command>. Using <literal>FOR UPDATE</literal> 262 prevents other sessions from changing the rows between the time they are 263 fetched and the time they are updated. Without <literal>FOR UPDATE</literal>, 264 a subsequent <literal>WHERE CURRENT OF</literal> command will have no effect if 265 the row was changed since the cursor was created. 266 </para> 267 268 <para> 269 Another reason to use <literal>FOR UPDATE</literal> is that without it, a 270 subsequent <literal>WHERE CURRENT OF</literal> might fail if the cursor query 271 does not meet the SQL standard's rules for being <quote>simply 272 updatable</quote> (in particular, the cursor must reference just one table 273 and not use grouping or <literal>ORDER BY</literal>). Cursors 274 that are not simply updatable might work, or might not, depending on plan 275 choice details; so in the worst case, an application might work in testing 276 and then fail in production. If <literal>FOR UPDATE</literal> is 277 specified, the cursor is guaranteed to be updatable. 278 </para> 279 280 <para> 281 The main reason not to use <literal>FOR UPDATE</literal> with <literal>WHERE 282 CURRENT OF</literal> is if you need the cursor to be scrollable, or to be 283 insensitive to the subsequent updates (that is, continue to show the old 284 data). If this is a requirement, pay close heed to the caveats shown 285 above. 286 </para> 287 </caution> 288 289 <para> 290 The SQL standard only makes provisions for cursors in embedded 291 <acronym>SQL</acronym>. The <productname>PostgreSQL</productname> 292 server does not implement an <command>OPEN</command> statement for 293 cursors; a cursor is considered to be open when it is declared. 294 However, <application>ECPG</application>, the embedded SQL 295 preprocessor for <productname>PostgreSQL</productname>, supports 296 the standard SQL cursor conventions, including those involving 297 <command>DECLARE</command> and <command>OPEN</command> statements. 298 </para> 299 300 <para> 301 You can see all available cursors by querying the <link 302 linkend="view-pg-cursors"><structname>pg_cursors</structname></link> 303 system view. 304 </para> 305 </refsect1> 306 307 <refsect1> 308 <title>Examples</title> 309 310 <para> 311 To declare a cursor: 312<programlisting> 313DECLARE liahona CURSOR FOR SELECT * FROM films; 314</programlisting> 315 See <xref linkend="sql-fetch"/> for more 316 examples of cursor usage. 317 </para> 318 </refsect1> 319 320 <refsect1> 321 <title>Compatibility</title> 322 323 <para> 324 The SQL standard says that it is implementation-dependent whether cursors 325 are sensitive to concurrent updates of the underlying data by default. In 326 <productname>PostgreSQL</productname>, cursors are insensitive by default, 327 and can be made sensitive by specifying <literal>FOR UPDATE</literal>. Other 328 products may work differently. 329 </para> 330 331 <para> 332 The SQL standard allows cursors only in embedded 333 <acronym>SQL</acronym> and in modules. <productname>PostgreSQL</productname> 334 permits cursors to be used interactively. 335 </para> 336 337 <para> 338 Binary cursors are a <productname>PostgreSQL</productname> 339 extension. 340 </para> 341 </refsect1> 342 343 <refsect1> 344 <title>See Also</title> 345 346 <simplelist type="inline"> 347 <member><xref linkend="sql-close"/></member> 348 <member><xref linkend="sql-fetch"/></member> 349 <member><xref linkend="sql-move"/></member> 350 </simplelist> 351 </refsect1> 352</refentry> 353