2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/fetch.sgml,v 1.22 2002/12/30 15:31:47 momjian 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 table using a cursor
21 <date>1999-07-20</date>
24 FETCH [ <replaceable class="PARAMETER">direction</replaceable> ] [ <replaceable class="PARAMETER">count</replaceable> ] { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
25 FETCH [ FORWARD | BACKWARD | RELATIVE ] [ <replaceable class="PARAMETER">#</replaceable> | ALL | NEXT | PRIOR ]
26 { IN | FROM } <replaceable class="PARAMETER">cursor</replaceable>
29 <refsect2 id="R2-SQL-FETCH-1">
31 <date>1998-09-01</date>
40 <term><replaceable class="PARAMETER">direction</replaceable></term>
43 <replaceable class="PARAMETER">selector</replaceable>
44 defines the fetch direction. It can be one of
52 fetch next row(s). This is the default
53 if <replaceable class="PARAMETER">selector</replaceable> is omitted.
61 fetch previous row(s).
69 Noise word for SQL92 compatibility.
79 <term><replaceable class="PARAMETER">count</replaceable></term>
82 <replaceable class="PARAMETER">count</replaceable>
83 determines how many rows to fetch. It can be one of the following:
87 <term><replaceable class="PARAMETER">#</replaceable></term>
90 A signed integer that specifies how many rows to fetch.
91 Note that a negative integer is equivalent to changing the sense of
92 FORWARD and BACKWARD. Zero re-fetches the current row.
103 Retrieve all remaining rows.
114 Equivalent to specifying a count of <command>1</command>.
125 Equivalent to specifying a count of <command>-1</command>.
135 <term><replaceable class="PARAMETER">cursor</replaceable></term>
138 An open cursor's name.
146 <refsect2 id="R2-SQL-FETCH-2">
148 <date>1998-04-15</date>
154 <command>FETCH</command> returns the results of the query defined by the specified cursor.
155 The following messages will be returned if the query fails:
159 <term><computeroutput>
160 WARNING: PerformPortalFetch: portal "<replaceable class="PARAMETER">cursor</replaceable>" not found
161 </computeroutput></term>
164 If <replaceable class="PARAMETER">cursor</replaceable>
165 is not previously declared.
166 The cursor must be declared within a transaction block.
172 <term><computeroutput>
173 WARNING: FETCH/ABSOLUTE not supported, using RELATIVE
174 </computeroutput></term>
177 <productname>PostgreSQL</productname> does not support absolute
178 positioning of cursors.
188 <refsect1 id="R1-SQL-FETCH-1">
190 <date>1998-04-15</date>
197 <command>FETCH</command> allows a user to retrieve rows using a cursor.
198 The number of rows retrieved is specified by
199 <replaceable class="PARAMETER">#</replaceable>.
200 If the number of rows remaining in the cursor is less
201 than <replaceable class="PARAMETER">#</replaceable>,
202 then only those available are fetched.
203 Substituting the keyword ALL in place of a number will
204 cause all remaining rows in the cursor to be retrieved.
205 Instances may be fetched in both FORWARD and BACKWARD
206 directions. The default direction is FORWARD.
210 Negative numbers are allowed to be specified for the
211 row count. A negative number is equivalent to reversing
212 the sense of the FORWARD and BACKWARD keywords. For example,
213 <command>FORWARD -1</command> is the same as <command>BACKWARD 1</command>.
218 <refsect2 id="R2-SQL-FETCH-3">
220 <date>1998-04-15</date>
227 Note that the FORWARD and BACKWARD keywords are
228 <productname>PostgreSQL</productname> extensions.
229 The <acronym>SQL92</acronym> syntax is also supported, specified
230 in the second form of the command. See below for details
231 on compatibility issues.
235 Updating data in a cursor is not supported by
236 <productname>PostgreSQL</productname>,
237 because mapping cursor updates back to base tables is
238 not generally possible, as is also the case with VIEW updates.
240 users must issue explicit UPDATE commands to replace data.
244 Cursors may only be used inside of transactions because
245 the data that they store spans multiple user queries.
250 <xref linkend="sql-move" endterm="sql-move-title">
251 to change cursor position.
252 <xref linkend="sql-declare" endterm="sql-declare-title">
253 will define a cursor.
255 <xref linkend="sql-begin" endterm="sql-begin-title">,
256 <xref linkend="sql-commit" endterm="sql-commit-title">,
258 <xref linkend="sql-rollback" endterm="sql-rollback-title">
259 for further information about transactions.
264 <refsect1 id="R1-SQL-FETCH-2">
270 The following examples traverses a table using a cursor.
273 -- Set up and use a cursor:
276 DECLARE liahona CURSOR FOR SELECT * FROM films;
278 -- Fetch first 5 rows in the cursor liahona:
279 FETCH FORWARD 5 IN liahona;
282 code | title | did | date_prod | kind | len
283 -------+-------------------------+-----+------------+----------+-------
284 BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44
285 BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43
286 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
287 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
288 P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28
291 -- Fetch previous row:
292 FETCH BACKWARD 1 IN liahona;
295 code | title | did | date_prod | kind | len
296 -------+---------+-----+------------+--------+-------
297 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
300 -- close the cursor and commit work:
308 <refsect1 id="R1-SQL-FETCH-3">
313 <refsect2 id="R2-SQL-FETCH-4">
315 <date>1998-09-01</date>
324 The non-embedded use of cursors is a <productname>PostgreSQL</productname>
325 extension. The syntax and usage of cursors is being compared
326 against the embedded form of cursors defined in <acronym>SQL92</acronym>.
332 <acronym>SQL92</acronym> allows absolute positioning of the cursor for
333 FETCH, and allows placing the results into explicit variables:
336 FETCH ABSOLUTE <replaceable class="PARAMETER">#</replaceable>
337 FROM <replaceable class="PARAMETER">cursor</replaceable>
338 INTO :<replaceable class="PARAMETER">variable</replaceable> [, ...]
343 <term>ABSOLUTE</term>
346 The cursor should be positioned to the specified absolute
347 row number. All row numbers in <productname>PostgreSQL</productname>
348 are relative numbers so this capability is not supported.
353 <term>:<replaceable class="PARAMETER">variable</replaceable></term>
356 Target host variable(s).
366 <!-- Keep this comment at the end of the file
371 sgml-minimize-attributes:nil
372 sgml-always-quote-attributes:t
375 sgml-parent-document:nil
376 sgml-default-dtd-file:"../reference.ced"
377 sgml-exposed-tags:nil
378 sgml-local-catalogs:"/usr/lib/sgml/catalog"
379 sgml-local-ecat-files:nil