Debian Codegraph @

doc/src/sgml/ref/fetch.sgml

raw
1 <!--
2 doc/src/sgml/ref/fetch.sgml
3 PostgreSQL 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>
30 FETCH [ <replaceable class="parameter">direction</replaceable> ] [ FROM | IN ] <replaceable class="parameter">cursor_name</replaceable>
31
32 <phrase>where <replaceable class="parameter">direction</replaceable> can be 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</command>. 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</command> 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</command> or <command>FETCH BACKWARD
67 ALL</command> 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>, <literal>PRIOR</literal>, <literal>FIRST</literal>,
73 <literal>LAST</literal>, <literal>ABSOLUTE</literal>, <literal>RELATIVE</literal> 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</literal> and <literal>BACKWARD</literal>
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>, <literal>FORWARD 0</literal>, and
91 <literal>BACKWARD 0</literal> 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</application>
101 function, the rules are different &mdash;
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</literal> and
278 <literal>BACKWARD</literal> cases, specifying a negative <replaceable
279 class="parameter">count</replaceable> is equivalent to changing
280 the sense of <literal>FORWARD</literal> and <literal>BACKWARD</literal>.
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> command returns a command
301 tag of the form
302 <screen>
303 FETCH <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</command>
319 other than <command>FETCH NEXT</command> or <command>FETCH FORWARD</command> 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 <link linkend="sql-declare"><command>DECLARE</command></link>
339 is used to define a cursor. Use
340 <link linkend="sql-move"><command>MOVE</command></link>
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>
352 BEGIN WORK;
353
354 -- Set up a cursor:
355 DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
356
357 -- Fetch the first 5 rows in the cursor liahona:
358 FETCH 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:
369 FETCH 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:
376 CLOSE liahona;
377 COMMIT 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</literal> preceding the cursor
404 name; the option to use <literal>IN</literal>, 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>