2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.27 2003/03/11 19:40:22 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-FETCH">
8 <refentrytitle id="SQL-FETCH-TITLE">FETCH</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
16 retrieve rows from a query using a cursor
21 <date>2003-03-11</date>
24 FETCH [ <replaceable class="PARAMETER">direction</replaceable> { FROM | IN } ] <replaceable class="PARAMETER">cursor</replaceable>
26 where <replaceable class="PARAMETER">direction</replaceable> can be empty or one of:
32 ABSOLUTE <replaceable class="PARAMETER">count</replaceable>
33 RELATIVE <replaceable class="PARAMETER">count</replaceable>
34 <replaceable class="PARAMETER">count</replaceable>
37 FORWARD <replaceable class="PARAMETER">count</replaceable>
40 BACKWARD <replaceable class="PARAMETER">count</replaceable>
44 <refsect2 id="R2-SQL-FETCH-1">
46 <date>2003-03-11</date>
55 <term><replaceable class="PARAMETER">direction</replaceable></term>
58 <replaceable class="PARAMETER">direction</replaceable>
59 defines the fetch direction and number of rows to fetch.
60 It can be one of the following:
67 fetch next row. This is the default
68 if <replaceable class="PARAMETER">direction</replaceable> is omitted.
86 fetch first row of query (same as ABSOLUTE 1).
95 fetch last row of query (same as ABSOLUTE -1).
101 <term>ABSOLUTE <replaceable class="PARAMETER">count</replaceable></term>
104 fetch the <replaceable class="PARAMETER">count</replaceable>'th
106 abs(<replaceable class="PARAMETER">count</replaceable>)'th row
108 <replaceable class="PARAMETER">count</replaceable> < 0.
109 Position before first row or after last row
110 if <replaceable class="PARAMETER">count</replaceable> is out of
111 range; in particular, ABSOLUTE 0 positions before first row.
117 <term>RELATIVE <replaceable class="PARAMETER">count</replaceable></term>
120 fetch the <replaceable class="PARAMETER">count</replaceable>'th
121 succeeding row, or the
122 abs(<replaceable class="PARAMETER">count</replaceable>)'th prior
123 row if <replaceable class="PARAMETER">count</replaceable> < 0.
124 RELATIVE 0 re-fetches current row, if any.
130 <term><replaceable class="PARAMETER">count</replaceable></term>
133 fetch the next <replaceable class="PARAMETER">count</replaceable>
134 rows (same as FORWARD <replaceable class="PARAMETER">count</replaceable>).
143 fetch all remaining rows (same as FORWARD ALL).
152 fetch next row (same as NEXT).
158 <term>FORWARD <replaceable class="PARAMETER">count</replaceable></term>
161 fetch next <replaceable class="PARAMETER">count</replaceable>
162 rows. FORWARD 0 re-fetches current row.
168 <term>FORWARD ALL</term>
171 fetch all remaining rows.
177 <term>BACKWARD</term>
180 fetch prior row (same as PRIOR).
186 <term>BACKWARD <replaceable class="PARAMETER">count</replaceable></term>
189 fetch prior <replaceable class="PARAMETER">count</replaceable>
190 rows (scanning backwards). BACKWARD 0 re-fetches current row.
196 <term>BACKWARD ALL</term>
199 fetch all prior rows (scanning backwards).
210 <term><replaceable class="PARAMETER">count</replaceable></term>
213 <replaceable class="PARAMETER">count</replaceable>
214 is a possibly-signed integer constant, determining the location
215 or number of rows to fetch. For FORWARD and BACKWARD cases,
216 specifying a negative <replaceable
217 class="PARAMETER">count</replaceable>
218 is equivalent to changing the sense of FORWARD and BACKWARD.
224 <term><replaceable class="PARAMETER">cursor</replaceable></term>
227 An open cursor's name.
235 <refsect2 id="R2-SQL-FETCH-2">
237 <date>2003-03-11</date>
243 <command>FETCH</command> returns rows from the result of the query defined
244 by the specified cursor.
245 The following messages will be returned if the query fails:
249 <term><computeroutput>
250 WARNING: PerformPortalFetch: portal "<replaceable class="PARAMETER">cursor</replaceable>" not found
251 </computeroutput></term>
254 If <replaceable class="PARAMETER">cursor</replaceable> is not known.
255 The cursor must have been declared within the current transaction block.
264 <refsect1 id="R1-SQL-FETCH-1">
266 <date>2003-03-11</date>
273 <command>FETCH</command> retrieves rows using a cursor.
277 A cursor has an associated <firstterm>position</> that is used by
278 <command>FETCH</>. The cursor position can be before the first row of the
279 query result, or on any particular row of the result, or after the last row
280 of the result. When created, a cursor is positioned before the first row.
281 After fetching some rows, the cursor is positioned on the row most recently
282 retrieved. If <command>FETCH</> runs off the end of the available rows
283 then the cursor is left positioned after the last row, or before the first
284 row if fetching backward. <command>FETCH ALL</> or <command>FETCH BACKWARD
285 ALL</> will always leave the cursor positioned after the last row or before
290 The SQL-compatible forms (NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE)
291 fetch a single row after moving the cursor appropriately. If there is
292 no such row, an empty result is returned, and the cursor is left positioned
293 before the first row or after the last row as appropriate.
297 The forms using FORWARD and BACKWARD are not in the SQL standard, but
298 are <productname>PostgreSQL</productname> extensions. These forms
299 retrieve the indicated number of rows moving in the forward or backward
300 direction, leaving the cursor positioned on the last-returned row
301 (or after/before all rows, if the <replaceable
302 class="PARAMETER">count</replaceable> exceeds the number of rows
308 RELATIVE 0, FORWARD 0, and BACKWARD 0 all request
309 fetching the current row without moving the
310 cursor --- that is, re-fetching the most recently fetched row.
311 This will succeed unless the cursor is positioned before the
312 first row or after the last row; in which case, no row is returned.
316 <refsect2 id="R2-SQL-FETCH-3">
318 <date>2003-03-11</date>
325 The cursor should be declared with the SCROLL option if one intends to
326 use any variants of <command>FETCH</> other than <command>FETCH NEXT</>
327 or <command>FETCH FORWARD</> with a positive count. For simple queries
328 <productname>PostgreSQL</productname> will allow backwards fetch from
329 cursors not declared with SCROLL, but this behavior is best not relied on.
333 ABSOLUTE fetches are not any faster than navigating to the desired row
334 with a relative move: the underlying implementation must traverse all
335 the intermediate rows anyway. Negative absolute fetches are even worse:
336 the query must be read to the end to find the last row, and then
337 traversed backward from there. However, rewinding to the start of the
338 query (as with FETCH ABSOLUTE 0) is fast.
342 Updating data via a cursor is not supported by
343 <productname>PostgreSQL</productname>,
344 because mapping cursor updates back to base tables is
345 not generally possible, as is also the case with VIEW updates.
347 users must issue explicit UPDATE commands to replace data.
351 Cursors may only be used inside transaction blocks.
355 <xref linkend="sql-declare" endterm="sql-declare-title">
356 is used to define a cursor.
358 <xref linkend="sql-move" endterm="sql-move-title">
359 to change cursor position without retrieving data.
361 <xref linkend="sql-begin" endterm="sql-begin-title">,
362 <xref linkend="sql-commit" endterm="sql-commit-title">,
364 <xref linkend="sql-rollback" endterm="sql-rollback-title">
365 for further information about transactions.
370 <refsect1 id="R1-SQL-FETCH-2">
376 The following example traverses a table using a cursor.
379 -- Set up and use a cursor:
382 DECLARE liahona CURSOR FOR SELECT * FROM films;
384 -- Fetch first 5 rows in the cursor liahona:
385 FETCH FORWARD 5 IN liahona;
388 code | title | did | date_prod | kind | len
389 -------+-------------------------+-----+------------+----------+-------
390 BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44
391 BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43
392 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
393 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
394 P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28
397 -- Fetch previous row:
398 FETCH PRIOR FROM liahona;
401 code | title | did | date_prod | kind | len
402 -------+---------+-----+------------+--------+-------
403 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
406 -- close the cursor and commit work:
414 <refsect1 id="R1-SQL-FETCH-3">
419 <refsect2 id="R2-SQL-FETCH-4">
421 <date>2003-03-11</date>
428 <acronym>SQL92</acronym> defines FETCH for use in embedded contexts only.
429 Therefore, it describes placing the results into explicit variables using
430 an <literal>INTO</> clause, for example:
433 FETCH ABSOLUTE <replaceable class="PARAMETER">n</replaceable>
434 FROM <replaceable class="PARAMETER">cursor</replaceable>
435 INTO :<replaceable class="PARAMETER">variable</replaceable> [, ...]
438 <productname>PostgreSQL</productname>'s use of non-embedded cursors
439 is non-standard, and so is its practice of returning the result data
440 as if it were a SELECT result. Other than this point, FETCH is fully
441 upward-compatible with <acronym>SQL92</acronym>.
445 The FETCH forms involving FORWARD and BACKWARD (including the forms
446 FETCH <replaceable class="PARAMETER">count</replaceable> and FETCH ALL,
447 in which FORWARD is implicit) are <productname>PostgreSQL</productname>
452 <acronym>SQL92</acronym> allows only <literal>FROM</> preceding the
453 cursor name; the option to use <literal>IN</> is an extension.
459 <!-- Keep this comment at the end of the file
464 sgml-minimize-attributes:nil
465 sgml-always-quote-attributes:t
468 sgml-parent-document:nil
469 sgml-default-dtd-file:"../reference.ced"
470 sgml-exposed-tags:nil
471 sgml-local-catalogs:"/usr/lib/sgml/catalog"
472 sgml-local-ecat-files:nil