2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.34 2003/09/22 00:16:58 petere 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>
23 FETCH [ <replaceable class="PARAMETER">direction</replaceable> { FROM | IN } ] <replaceable class="PARAMETER">cursorname</replaceable>
25 where <replaceable class="PARAMETER">direction</replaceable> can be empty or one of:
31 ABSOLUTE <replaceable class="PARAMETER">count</replaceable>
32 RELATIVE <replaceable class="PARAMETER">count</replaceable>
33 <replaceable class="PARAMETER">count</replaceable>
36 FORWARD <replaceable class="PARAMETER">count</replaceable>
39 BACKWARD <replaceable class="PARAMETER">count</replaceable>
45 <title>Description</title>
48 <command>FETCH</command> retrieves rows using a previously-created cursor.
52 A cursor has an associated position, which is used by
53 <command>FETCH</>. The cursor position can be before the first row of the
54 query result, on any particular row of the result, or after the last row
55 of the result. When created, a cursor is positioned before the first row.
56 After fetching some rows, the cursor is positioned on the row most recently
57 retrieved. If <command>FETCH</> runs off the end of the available rows
58 then the cursor is left positioned after the last row, or before the first
59 row if fetching backward. <command>FETCH ALL</> or <command>FETCH BACKWARD
60 ALL</> will always leave the cursor positioned after the last row or before
65 The forms <literal>NEXT</>, <literal>PRIOR</>, <literal>FIRST</>,
66 <literal>LAST</>, <literal>ABSOLUTE</>, <literal>RELATIVE</> fetch
67 a single row after moving the cursor appropriately. If there is no
68 such row, an empty result is returned, and the cursor is left
69 positioned before the first row or after the last row as
74 The forms using <literal>FORWARD</> and <literal>BACKWARD</>
75 retrieve the indicated number of rows moving in the forward or
76 backward direction, leaving the cursor positioned on the
77 last-returned row (or after/before all rows, if the <replaceable
78 class="PARAMETER">count</replaceable> exceeds the number of rows
83 <literal>RELATIVE 0</>, <literal>FORWARD 0</>, and
84 <literal>BACKWARD 0</> all request fetching the current row without
85 moving the cursor, that is, re-fetching the most recently fetched
86 row. This will succeed unless the cursor is positioned before the
87 first row or after the last row; in which case, no row is returned.
92 <title>Parameters</title>
96 <term><replaceable class="PARAMETER">direction</replaceable></term>
99 <replaceable class="PARAMETER">direction</replaceable> defines
100 the fetch direction and number of rows to fetch. It can be one
105 <term><literal>NEXT</literal></term>
108 Fetch the next row. This is the default if <replaceable
109 class="PARAMETER">direction</replaceable> is omitted.
115 <term><literal>PRIOR</literal></term>
124 <term><literal>FIRST</literal></term>
127 Fetch the first row of the query (same as <literal>ABSOLUTE 1</literal>).
133 <term><literal>LAST</literal></term>
136 Fetch the last row of the query (same as <literal>ABSOLUTE -1</literal>).
142 <term><literal>ABSOLUTE <replaceable class="PARAMETER">count</replaceable></literal></term>
145 Fetch the <replaceable
146 class="PARAMETER">count</replaceable>'th row of the query,
147 or the <literal>abs(<replaceable
148 class="PARAMETER">count</replaceable>)</literal>'th row from
149 the end if <replaceable
150 class="PARAMETER">count</replaceable> is negative. Position
151 before first row or after last row if <replaceable
152 class="PARAMETER">count</replaceable> is out of range; in
153 particular, <literal>ABSOLUTE 0</literal> positions before
160 <term><literal>RELATIVE <replaceable class="PARAMETER">count</replaceable></literal></term>
163 Fetch the <replaceable
164 class="PARAMETER">count</replaceable>'th succeeding row, or
165 the <literal>abs(<replaceable
166 class="PARAMETER">count</replaceable>)</literal>'th prior
167 row if <replaceable class="PARAMETER">count</replaceable> is
168 negative. <literal>RELATIVE 0</literal> re-fetches the
175 <term><replaceable class="PARAMETER">count</replaceable></term>
178 Fetch the next <replaceable
179 class="PARAMETER">count</replaceable> rows (same as
180 <literal>FORWARD <replaceable
181 class="PARAMETER">count</replaceable></literal>).
187 <term><literal>ALL</literal></term>
190 Fetch all remaining rows (same as <literal>FORWARD ALL</literal>).
196 <term><literal>FORWARD</literal></term>
199 Fetch the next row (same as <literal>NEXT</literal>).
205 <term><literal>FORWARD <replaceable class="PARAMETER">count</replaceable></literal></term>
208 Fetch the next <replaceable
209 class="PARAMETER">count</replaceable> rows.
210 <literal>FORWARD 0</literal> re-fetches the current row.
216 <term><literal>FORWARD ALL</literal></term>
219 Fetch all remaining rows.
225 <term><literal>BACKWARD</literal></term>
228 Fetch the prior row (same as <literal>PRIOR</literal>).
234 <term><literal>BACKWARD <replaceable class="PARAMETER">count</replaceable></literal></term>
237 Fetch the prior <replaceable
238 class="PARAMETER">count</replaceable> rows (scanning
239 backwards). <literal>BACKWARD 0</literal> re-fetches the
246 <term><literal>BACKWARD ALL</literal></term>
249 Fetch all prior rows (scanning backwards).
259 <term><replaceable class="PARAMETER">count</replaceable></term>
262 <replaceable class="PARAMETER">count</replaceable> is a
263 possibly-signed integer constant, determining the location or
264 number of rows to fetch. For <literal>FORWARD</> and
265 <literal>BACKWARD</> cases, specifying a negative <replaceable
266 class="PARAMETER">count</replaceable> is equivalent to changing
267 the sense of <literal>FORWARD</> and <literal>BACKWARD</>.
273 <term><replaceable class="PARAMETER">cursorname</replaceable></term>
276 An open cursor's name.
284 <title>Outputs</title>
287 On successful completion, a <command>FETCH</> command returns a command
290 FETCH <replaceable class="parameter">count</replaceable>
292 The <replaceable class="parameter">count</replaceable> is the number
293 of rows fetched (possibly zero). Note that in
294 <application>psql</application>, the command tag will not actually be
295 displayed, since <application>psql</application> displays the fetched
304 The cursor should be declared with the <literal>SCROLL</literal>
305 option if one intends to use any variants of <command>FETCH</>
306 other than <command>FETCH NEXT</> or <command>FETCH FORWARD</> with
307 a positive count. For simple queries
308 <productname>PostgreSQL</productname> will allow backwards fetch
309 from cursors not declared with <literal>SCROLL</literal>, but this
310 behavior is best not relied on. If the cursor is declared with
311 <literal>NO SCROLL</literal>, no backward fetches are allowed.
315 <literal>ABSOLUTE</literal> fetches are not any faster than
316 navigating to the desired row with a relative move: the underlying
317 implementation must traverse all the intermediate rows anyway.
318 Negative absolute fetches are even worse: the query must be read to
319 the end to find the last row, and then traversed backward from
320 there. However, rewinding to the start of the query (as with
321 <literal>FETCH ABSOLUTE 0</literal>) is fast.
325 Updating data via a cursor is currently not supported by
326 <productname>PostgreSQL</productname>.
330 <xref linkend="sql-declare" endterm="sql-declare-title">
331 is used to define a cursor. Use
332 <xref linkend="sql-move" endterm="sql-move-title">
333 to change cursor position without retrieving data.
338 <title>Examples</title>
341 The following example traverses a table using a cursor.
347 DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
349 -- Fetch the first 5 rows in the cursor liahona:
350 FETCH FORWARD 5 FROM liahona;
352 code | title | did | date_prod | kind | len
353 -------+-------------------------+-----+------------+----------+-------
354 BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44
355 BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43
356 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
357 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
358 P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28
360 -- Fetch the previous row:
361 FETCH PRIOR FROM liahona;
363 code | title | did | date_prod | kind | len
364 -------+---------+-----+------------+--------+-------
365 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
367 -- Close the cursor and end the transaction:
375 <title>Compatibility</title>
378 The SQL standard defines <command>FETCH</command> for use in
379 embedded SQL only. This variant of <command>FETCH</command>
380 described here returns the data as if it were a
381 <command>SELECT</command> result rather than placing it in host
382 variables. Other than this point, <command>FETCH</command> is
383 fully upward-compatible with the SQL standard.
387 The <command>FETCH</command> forms involving
388 <literal>FORWARD</literal> and <literal>BACKWARD</literal>, as well
389 as the forms <literal>FETCH <replaceable
390 class="PARAMETER">count</replaceable></literal> and <literal>FETCH
391 ALL</literal>, in which <literal>FORWARD</literal> is implicit, are
392 <productname>PostgreSQL</productname> extensions.
396 The SQL standard allows only <literal>FROM</> preceding the cursor
397 name; the option to use <literal>IN</> is an extension.
402 <!-- Keep this comment at the end of the file
407 sgml-minimize-attributes:nil
408 sgml-always-quote-attributes:t
411 sgml-parent-document:nil
412 sgml-default-dtd-file:"../reference.ced"
413 sgml-exposed-tags:nil
414 sgml-local-catalogs:"/usr/lib/sgml/catalog"
415 sgml-local-ecat-files:nil