2 $PostgreSQL: pgsql/doc/src/sgml/ref/fetch.sgml,v 1.37 2004/06/17 12:41:02 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-FETCH">
8 <refentrytitle id="SQL-FETCH-TITLE">FETCH</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>FETCH</refname>
14 <refpurpose>retrieve rows from a query using a cursor</refpurpose>
17 <indexterm zone="sql-fetch">
18 <primary>FETCH</primary>
21 <indexterm zone="sql-fetch">
22 <primary>cursor</primary>
23 <secondary>FETCH</secondary>
28 FETCH [ <replaceable class="PARAMETER">direction</replaceable> { FROM | IN } ] <replaceable class="PARAMETER">cursorname</replaceable>
30 where <replaceable class="PARAMETER">direction</replaceable> can be empty or one of:
36 ABSOLUTE <replaceable class="PARAMETER">count</replaceable>
37 RELATIVE <replaceable class="PARAMETER">count</replaceable>
38 <replaceable class="PARAMETER">count</replaceable>
41 FORWARD <replaceable class="PARAMETER">count</replaceable>
44 BACKWARD <replaceable class="PARAMETER">count</replaceable>
50 <title>Description</title>
53 <command>FETCH</command> retrieves rows using a previously-created cursor.
57 A cursor has an associated position, which is used by
58 <command>FETCH</>. The cursor position can be before the first row of the
59 query result, on any particular row of the result, or after the last row
60 of the result. When created, a cursor is positioned before the first row.
61 After fetching some rows, the cursor is positioned on the row most recently
62 retrieved. If <command>FETCH</> runs off the end of the available rows
63 then the cursor is left positioned after the last row, or before the first
64 row if fetching backward. <command>FETCH ALL</> or <command>FETCH BACKWARD
65 ALL</> will always leave the cursor positioned after the last row or before
70 The forms <literal>NEXT</>, <literal>PRIOR</>, <literal>FIRST</>,
71 <literal>LAST</>, <literal>ABSOLUTE</>, <literal>RELATIVE</> fetch
72 a single row after moving the cursor appropriately. If there is no
73 such row, an empty result is returned, and the cursor is left
74 positioned before the first row or after the last row as
79 The forms using <literal>FORWARD</> and <literal>BACKWARD</>
80 retrieve the indicated number of rows moving in the forward or
81 backward direction, leaving the cursor positioned on the
82 last-returned row (or after/before all rows, if the <replaceable
83 class="PARAMETER">count</replaceable> exceeds the number of rows
88 <literal>RELATIVE 0</>, <literal>FORWARD 0</>, and
89 <literal>BACKWARD 0</> all request fetching the current row without
90 moving the cursor, that is, re-fetching the most recently fetched
91 row. This will succeed unless the cursor is positioned before the
92 first row or after the last row; in which case, no row is returned.
97 <title>Parameters</title>
101 <term><replaceable class="PARAMETER">direction</replaceable></term>
104 <replaceable class="PARAMETER">direction</replaceable> defines
105 the fetch direction and number of rows to fetch. It can be one
110 <term><literal>NEXT</literal></term>
113 Fetch the next row. This is the default if <replaceable
114 class="PARAMETER">direction</replaceable> is omitted.
120 <term><literal>PRIOR</literal></term>
129 <term><literal>FIRST</literal></term>
132 Fetch the first row of the query (same as <literal>ABSOLUTE 1</literal>).
138 <term><literal>LAST</literal></term>
141 Fetch the last row of the query (same as <literal>ABSOLUTE -1</literal>).
147 <term><literal>ABSOLUTE <replaceable class="PARAMETER">count</replaceable></literal></term>
150 Fetch the <replaceable
151 class="PARAMETER">count</replaceable>'th row of the query,
152 or the <literal>abs(<replaceable
153 class="PARAMETER">count</replaceable>)</literal>'th row from
154 the end if <replaceable
155 class="PARAMETER">count</replaceable> is negative. Position
156 before first row or after last row if <replaceable
157 class="PARAMETER">count</replaceable> is out of range; in
158 particular, <literal>ABSOLUTE 0</literal> positions before
165 <term><literal>RELATIVE <replaceable class="PARAMETER">count</replaceable></literal></term>
168 Fetch the <replaceable
169 class="PARAMETER">count</replaceable>'th succeeding row, or
170 the <literal>abs(<replaceable
171 class="PARAMETER">count</replaceable>)</literal>'th prior
172 row if <replaceable class="PARAMETER">count</replaceable> is
173 negative. <literal>RELATIVE 0</literal> re-fetches the
180 <term><replaceable class="PARAMETER">count</replaceable></term>
183 Fetch the next <replaceable
184 class="PARAMETER">count</replaceable> rows (same as
185 <literal>FORWARD <replaceable
186 class="PARAMETER">count</replaceable></literal>).
192 <term><literal>ALL</literal></term>
195 Fetch all remaining rows (same as <literal>FORWARD ALL</literal>).
201 <term><literal>FORWARD</literal></term>
204 Fetch the next row (same as <literal>NEXT</literal>).
210 <term><literal>FORWARD <replaceable class="PARAMETER">count</replaceable></literal></term>
213 Fetch the next <replaceable
214 class="PARAMETER">count</replaceable> rows.
215 <literal>FORWARD 0</literal> re-fetches the current row.
221 <term><literal>FORWARD ALL</literal></term>
224 Fetch all remaining rows.
230 <term><literal>BACKWARD</literal></term>
233 Fetch the prior row (same as <literal>PRIOR</literal>).
239 <term><literal>BACKWARD <replaceable class="PARAMETER">count</replaceable></literal></term>
242 Fetch the prior <replaceable
243 class="PARAMETER">count</replaceable> rows (scanning
244 backwards). <literal>BACKWARD 0</literal> re-fetches the
251 <term><literal>BACKWARD ALL</literal></term>
254 Fetch all prior rows (scanning backwards).
264 <term><replaceable class="PARAMETER">count</replaceable></term>
267 <replaceable class="PARAMETER">count</replaceable> is a
268 possibly-signed integer constant, determining the location or
269 number of rows to fetch. For <literal>FORWARD</> and
270 <literal>BACKWARD</> cases, specifying a negative <replaceable
271 class="PARAMETER">count</replaceable> is equivalent to changing
272 the sense of <literal>FORWARD</> and <literal>BACKWARD</>.
278 <term><replaceable class="PARAMETER">cursorname</replaceable></term>
281 An open cursor's name.
289 <title>Outputs</title>
292 On successful completion, a <command>FETCH</> command returns a command
295 FETCH <replaceable class="parameter">count</replaceable>
297 The <replaceable class="parameter">count</replaceable> is the number
298 of rows fetched (possibly zero). Note that in
299 <application>psql</application>, the command tag will not actually be
300 displayed, since <application>psql</application> displays the fetched
309 The cursor should be declared with the <literal>SCROLL</literal>
310 option if one intends to use any variants of <command>FETCH</>
311 other than <command>FETCH NEXT</> or <command>FETCH FORWARD</> with
312 a positive count. For simple queries
313 <productname>PostgreSQL</productname> will allow backwards fetch
314 from cursors not declared with <literal>SCROLL</literal>, but this
315 behavior is best not relied on. If the cursor is declared with
316 <literal>NO SCROLL</literal>, no backward fetches are allowed.
320 <literal>ABSOLUTE</literal> fetches are not any faster than
321 navigating to the desired row with a relative move: the underlying
322 implementation must traverse all the intermediate rows anyway.
323 Negative absolute fetches are even worse: the query must be read to
324 the end to find the last row, and then traversed backward from
325 there. However, rewinding to the start of the query (as with
326 <literal>FETCH ABSOLUTE 0</literal>) is fast.
330 Updating data via a cursor is currently not supported by
331 <productname>PostgreSQL</productname>.
335 <xref linkend="sql-declare" endterm="sql-declare-title">
336 is used to define a cursor. Use
337 <xref linkend="sql-move" endterm="sql-move-title">
338 to change cursor position without retrieving data.
343 <title>Examples</title>
346 The following example traverses a table using a cursor.
352 DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
354 -- Fetch the first 5 rows in the cursor liahona:
355 FETCH FORWARD 5 FROM liahona;
357 code | title | did | date_prod | kind | len
358 -------+-------------------------+-----+------------+----------+-------
359 BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44
360 BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43
361 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
362 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
363 P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28
365 -- Fetch the previous row:
366 FETCH PRIOR FROM liahona;
368 code | title | did | date_prod | kind | len
369 -------+---------+-----+------------+--------+-------
370 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
372 -- Close the cursor and end the transaction:
380 <title>Compatibility</title>
383 The SQL standard defines <command>FETCH</command> for use in
384 embedded SQL only. The variant of <command>FETCH</command>
385 described here returns the data as if it were a
386 <command>SELECT</command> result rather than placing it in host
387 variables. Other than this point, <command>FETCH</command> is
388 fully upward-compatible with the SQL standard.
392 The <command>FETCH</command> forms involving
393 <literal>FORWARD</literal> and <literal>BACKWARD</literal>, as well
394 as the forms <literal>FETCH <replaceable
395 class="PARAMETER">count</replaceable></literal> and <literal>FETCH
396 ALL</literal>, in which <literal>FORWARD</literal> is implicit, are
397 <productname>PostgreSQL</productname> extensions.
401 The SQL standard allows only <literal>FROM</> preceding the cursor
402 name; the option to use <literal>IN</> is an extension.
407 <!-- Keep this comment at the end of the file
412 sgml-minimize-attributes:nil
413 sgml-always-quote-attributes:t
416 sgml-parent-document:nil
417 sgml-default-dtd-file:"../reference.ced"
418 sgml-exposed-tags:nil
419 sgml-local-catalogs:"/usr/lib/sgml/catalog"
420 sgml-local-ecat-files:nil