1<!-- 2doc/src/sgml/ref/fetch.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="SQL-FETCH"> 7 8 <indexterm zone="sql-fetch"> 9 <primary>FETCH</primary> 10 </indexterm> 11 12 <indexterm zone="sql-fetch"> 13 <primary>cursor</primary> 14 <secondary>FETCH</secondary> 15 </indexterm> 16 <refmeta> 17 <refentrytitle>FETCH</refentrytitle> 18 <manvolnum>7</manvolnum> 19 <refmiscinfo>SQL - Language Statements</refmiscinfo> 20 </refmeta> 21 22 <refnamediv> 23 <refname>FETCH</refname> 24 <refpurpose>retrieve rows from a query using a cursor</refpurpose> 25 </refnamediv> 26 27 <refsynopsisdiv> 28<!-- Note the "direction" bit is also in ref/move.sgml --> 29<synopsis> 30FETCH [ <replaceable class="PARAMETER">direction</replaceable> [ FROM | IN ] ] <replaceable class="PARAMETER">cursor_name</replaceable> 31 32<phrase>where <replaceable class="PARAMETER">direction</replaceable> can be empty or one of:</phrase> 33 34 NEXT 35 PRIOR 36 FIRST 37 LAST 38 ABSOLUTE <replaceable class="PARAMETER">count</replaceable> 39 RELATIVE <replaceable class="PARAMETER">count</replaceable> 40 <replaceable class="PARAMETER">count</replaceable> 41 ALL 42 FORWARD 43 FORWARD <replaceable class="PARAMETER">count</replaceable> 44 FORWARD ALL 45 BACKWARD 46 BACKWARD <replaceable class="PARAMETER">count</replaceable> 47 BACKWARD ALL 48</synopsis> 49 </refsynopsisdiv> 50 51 <refsect1> 52 <title>Description</title> 53 54 <para> 55 <command>FETCH</command> retrieves rows using a previously-created cursor. 56 </para> 57 58 <para> 59 A cursor has an associated position, which is used by 60 <command>FETCH</>. The cursor position can be before the first row of the 61 query result, on any particular row of the result, or after the last row 62 of the result. When created, a cursor is positioned before the first row. 63 After fetching some rows, the cursor is positioned on the row most recently 64 retrieved. If <command>FETCH</> runs off the end of the available rows 65 then the cursor is left positioned after the last row, or before the first 66 row if fetching backward. <command>FETCH ALL</> or <command>FETCH BACKWARD 67 ALL</> will always leave the cursor positioned after the last row or before 68 the first row. 69 </para> 70 71 <para> 72 The forms <literal>NEXT</>, <literal>PRIOR</>, <literal>FIRST</>, 73 <literal>LAST</>, <literal>ABSOLUTE</>, <literal>RELATIVE</> fetch 74 a single row after moving the cursor appropriately. If there is no 75 such row, an empty result is returned, and the cursor is left 76 positioned before the first row or after the last row as 77 appropriate. 78 </para> 79 80 <para> 81 The forms using <literal>FORWARD</> and <literal>BACKWARD</> 82 retrieve the indicated number of rows moving in the forward or 83 backward direction, leaving the cursor positioned on the 84 last-returned row (or after/before all rows, if the <replaceable 85 class="PARAMETER">count</replaceable> exceeds the number of rows 86 available). 87 </para> 88 89 <para> 90 <literal>RELATIVE 0</>, <literal>FORWARD 0</>, and 91 <literal>BACKWARD 0</> all request fetching the current row without 92 moving the cursor, that is, re-fetching the most recently fetched 93 row. This will succeed unless the cursor is positioned before the 94 first row or after the last row; in which case, no row is returned. 95 </para> 96 97 <note> 98 <para> 99 This page describes usage of cursors at the SQL command level. 100 If you are trying to use cursors inside a <application>PL/pgSQL</> 101 function, the rules are different — 102 see <xref linkend="plpgsql-cursor-using">. 103 </para> 104 </note> 105 </refsect1> 106 107 <refsect1> 108 <title>Parameters</title> 109 110 <variablelist> 111 <varlistentry> 112 <term><replaceable class="PARAMETER">direction</replaceable></term> 113 <listitem> 114 <para><replaceable class="PARAMETER">direction</replaceable> defines 115 the fetch direction and number of rows to fetch. It can be one 116 of the following: 117 118 <variablelist> 119 <varlistentry> 120 <term><literal>NEXT</literal></term> 121 <listitem> 122 <para> 123 Fetch the next row. This is the default if <replaceable 124 class="PARAMETER">direction</replaceable> is omitted. 125 </para> 126 </listitem> 127 </varlistentry> 128 129 <varlistentry> 130 <term><literal>PRIOR</literal></term> 131 <listitem> 132 <para> 133 Fetch the prior row. 134 </para> 135 </listitem> 136 </varlistentry> 137 138 <varlistentry> 139 <term><literal>FIRST</literal></term> 140 <listitem> 141 <para> 142 Fetch the first row of the query (same as <literal>ABSOLUTE 1</literal>). 143 </para> 144 </listitem> 145 </varlistentry> 146 147 <varlistentry> 148 <term><literal>LAST</literal></term> 149 <listitem> 150 <para> 151 Fetch the last row of the query (same as <literal>ABSOLUTE -1</literal>). 152 </para> 153 </listitem> 154 </varlistentry> 155 156 <varlistentry> 157 <term><literal>ABSOLUTE <replaceable class="PARAMETER">count</replaceable></literal></term> 158 <listitem> 159 <para> 160 Fetch the <replaceable 161 class="PARAMETER">count</replaceable>'th row of the query, 162 or the <literal>abs(<replaceable 163 class="PARAMETER">count</replaceable>)</literal>'th row from 164 the end if <replaceable 165 class="PARAMETER">count</replaceable> is negative. Position 166 before first row or after last row if <replaceable 167 class="PARAMETER">count</replaceable> is out of range; in 168 particular, <literal>ABSOLUTE 0</literal> positions before 169 the first row. 170 </para> 171 </listitem> 172 </varlistentry> 173 174 <varlistentry> 175 <term><literal>RELATIVE <replaceable class="PARAMETER">count</replaceable></literal></term> 176 <listitem> 177 <para> 178 Fetch the <replaceable 179 class="PARAMETER">count</replaceable>'th succeeding row, or 180 the <literal>abs(<replaceable 181 class="PARAMETER">count</replaceable>)</literal>'th prior 182 row if <replaceable class="PARAMETER">count</replaceable> is 183 negative. <literal>RELATIVE 0</literal> re-fetches the 184 current row, if any. 185 </para> 186 </listitem> 187 </varlistentry> 188 189 <varlistentry> 190 <term><replaceable class="PARAMETER">count</replaceable></term> 191 <listitem> 192 <para> 193 Fetch the next <replaceable 194 class="PARAMETER">count</replaceable> rows (same as 195 <literal>FORWARD <replaceable 196 class="PARAMETER">count</replaceable></literal>). 197 </para> 198 </listitem> 199 </varlistentry> 200 201 <varlistentry> 202 <term><literal>ALL</literal></term> 203 <listitem> 204 <para> 205 Fetch all remaining rows (same as <literal>FORWARD ALL</literal>). 206 </para> 207 </listitem> 208 </varlistentry> 209 210 <varlistentry> 211 <term><literal>FORWARD</literal></term> 212 <listitem> 213 <para> 214 Fetch the next row (same as <literal>NEXT</literal>). 215 </para> 216 </listitem> 217 </varlistentry> 218 219 <varlistentry> 220 <term><literal>FORWARD <replaceable class="PARAMETER">count</replaceable></literal></term> 221 <listitem> 222 <para> 223 Fetch the next <replaceable 224 class="PARAMETER">count</replaceable> rows. 225 <literal>FORWARD 0</literal> re-fetches the current row. 226 </para> 227 </listitem> 228 </varlistentry> 229 230 <varlistentry> 231 <term><literal>FORWARD ALL</literal></term> 232 <listitem> 233 <para> 234 Fetch all remaining rows. 235 </para> 236 </listitem> 237 </varlistentry> 238 239 <varlistentry> 240 <term><literal>BACKWARD</literal></term> 241 <listitem> 242 <para> 243 Fetch the prior row (same as <literal>PRIOR</literal>). 244 </para> 245 </listitem> 246 </varlistentry> 247 248 <varlistentry> 249 <term><literal>BACKWARD <replaceable class="PARAMETER">count</replaceable></literal></term> 250 <listitem> 251 <para> 252 Fetch the prior <replaceable 253 class="PARAMETER">count</replaceable> rows (scanning 254 backwards). <literal>BACKWARD 0</literal> re-fetches the 255 current row. 256 </para> 257 </listitem> 258 </varlistentry> 259 260 <varlistentry> 261 <term><literal>BACKWARD ALL</literal></term> 262 <listitem> 263 <para> 264 Fetch all prior rows (scanning backwards). 265 </para> 266 </listitem> 267 </varlistentry> 268 </variablelist></para> 269 </listitem> 270 </varlistentry> 271 272 <varlistentry> 273 <term><replaceable class="PARAMETER">count</replaceable></term> 274 <listitem> 275 <para><replaceable class="PARAMETER">count</replaceable> is a 276 possibly-signed integer constant, determining the location or 277 number of rows to fetch. For <literal>FORWARD</> and 278 <literal>BACKWARD</> cases, specifying a negative <replaceable 279 class="PARAMETER">count</replaceable> is equivalent to changing 280 the sense of <literal>FORWARD</> and <literal>BACKWARD</>. 281 </para> 282 </listitem> 283 </varlistentry> 284 285 <varlistentry> 286 <term><replaceable class="PARAMETER">cursor_name</replaceable></term> 287 <listitem> 288 <para> 289 An open cursor's name. 290 </para> 291 </listitem> 292 </varlistentry> 293 </variablelist> 294 </refsect1> 295 296 <refsect1> 297 <title>Outputs</title> 298 299 <para> 300 On successful completion, a <command>FETCH</> command returns a command 301 tag of the form 302<screen> 303FETCH <replaceable class="parameter">count</replaceable> 304</screen> 305 The <replaceable class="parameter">count</replaceable> is the number 306 of rows fetched (possibly zero). Note that in 307 <application>psql</application>, the command tag will not actually be 308 displayed, since <application>psql</application> displays the fetched 309 rows instead. 310 </para> 311 </refsect1> 312 313 <refsect1> 314 <title>Notes</title> 315 316 <para> 317 The cursor should be declared with the <literal>SCROLL</literal> 318 option if one intends to use any variants of <command>FETCH</> 319 other than <command>FETCH NEXT</> or <command>FETCH FORWARD</> with 320 a positive count. For simple queries 321 <productname>PostgreSQL</productname> will allow backwards fetch 322 from cursors not declared with <literal>SCROLL</literal>, but this 323 behavior is best not relied on. If the cursor is declared with 324 <literal>NO SCROLL</literal>, no backward fetches are allowed. 325 </para> 326 327 <para> 328 <literal>ABSOLUTE</literal> fetches are not any faster than 329 navigating to the desired row with a relative move: the underlying 330 implementation must traverse all the intermediate rows anyway. 331 Negative absolute fetches are even worse: the query must be read to 332 the end to find the last row, and then traversed backward from 333 there. However, rewinding to the start of the query (as with 334 <literal>FETCH ABSOLUTE 0</literal>) is fast. 335 </para> 336 337 <para> 338 <xref linkend="sql-declare"> 339 is used to define a cursor. Use 340 <xref linkend="sql-move"> 341 to change cursor position without retrieving data. 342 </para> 343 </refsect1> 344 345 <refsect1> 346 <title>Examples</title> 347 348 <para> 349 The following example traverses a table using a cursor: 350 351<programlisting> 352BEGIN WORK; 353 354-- Set up a cursor: 355DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films; 356 357-- Fetch the first 5 rows in the cursor liahona: 358FETCH FORWARD 5 FROM liahona; 359 360 code | title | did | date_prod | kind | len 361-------+-------------------------+-----+------------+----------+------- 362 BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44 363 BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43 364 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25 365 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08 366 P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28 367 368-- Fetch the previous row: 369FETCH PRIOR FROM liahona; 370 371 code | title | did | date_prod | kind | len 372-------+---------+-----+------------+--------+------- 373 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08 374 375-- Close the cursor and end the transaction: 376CLOSE liahona; 377COMMIT WORK; 378</programlisting></para> 379 </refsect1> 380 381 <refsect1> 382 <title>Compatibility</title> 383 384 <para> 385 The SQL standard defines <command>FETCH</command> for use in 386 embedded SQL only. The variant of <command>FETCH</command> 387 described here returns the data as if it were a 388 <command>SELECT</command> result rather than placing it in host 389 variables. Other than this point, <command>FETCH</command> is 390 fully upward-compatible with the SQL standard. 391 </para> 392 393 <para> 394 The <command>FETCH</command> forms involving 395 <literal>FORWARD</literal> and <literal>BACKWARD</literal>, as well 396 as the forms <literal>FETCH <replaceable 397 class="PARAMETER">count</replaceable></literal> and <literal>FETCH 398 ALL</literal>, in which <literal>FORWARD</literal> is implicit, are 399 <productname>PostgreSQL</productname> extensions. 400 </para> 401 402 <para> 403 The SQL standard allows only <literal>FROM</> preceding the cursor 404 name; the option to use <literal>IN</>, or to leave them out altogether, is 405 an extension. 406 </para> 407 </refsect1> 408 409 <refsect1> 410 <title>See Also</title> 411 412 <simplelist type="inline"> 413 <member><xref linkend="sql-close"></member> 414 <member><xref linkend="sql-declare"></member> 415 <member><xref linkend="sql-move"></member> 416 </simplelist> 417 </refsect1> 418</refentry> 419