1FETCH() SQL Commands FETCH()
2
3
4
6 FETCH - retrieve rows from a query using a cursor
7
8
10 FETCH [ direction { FROM | IN } ] cursorname
11
12 where direction can be empty or one of:
13
14 NEXT
15 PRIOR
16 FIRST
17 LAST
18 ABSOLUTE count
19 RELATIVE count
20 count
21 ALL
22 FORWARD
23 FORWARD count
24 FORWARD ALL
25 BACKWARD
26 BACKWARD count
27 BACKWARD ALL
28
29
31 FETCH retrieves rows using a previously-created cursor.
32
33 A cursor has an associated position, which is used by FETCH. The cursor
34 position can be before the first row of the query result, on any par‐
35 ticular row of the result, or after the last row of the result. When
36 created, a cursor is positioned before the first row. After fetching
37 some rows, the cursor is positioned on the row most recently retrieved.
38 If FETCH runs off the end of the available rows then the cursor is left
39 positioned after the last row, or before the first row if fetching
40 backward. FETCH ALL or FETCH BACKWARD ALL will always leave the cursor
41 positioned after the last row or before the first row.
42
43 The forms NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE fetch a single
44 row after moving the cursor appropriately. If there is no such row, an
45 empty result is returned, and the cursor is left positioned before the
46 first row or after the last row as appropriate.
47
48 The forms using FORWARD and BACKWARD retrieve the indicated number of
49 rows moving in the forward or backward direction, leaving the cursor
50 positioned on the last-returned row (or after/before all rows, if the
51 count exceeds the number of rows available).
52
53 RELATIVE 0, FORWARD 0, and BACKWARD 0 all request fetching the current
54 row without moving the cursor, that is, re-fetching the most recently
55 fetched row. This will succeed unless the cursor is positioned before
56 the first row or after the last row; in which case, no row is returned.
57
59 direction
60 direction defines the fetch direction and number of rows to
61 fetch. It can be one of the following:
62
63 NEXT Fetch the next row. This is the default if direction is
64 omitted.
65
66 PRIOR Fetch the prior row.
67
68 FIRST Fetch the first row of the query (same as ABSOLUTE 1).
69
70 LAST Fetch the last row of the query (same as ABSOLUTE -1).
71
72 ABSOLUTE count
73 Fetch the count'th row of the query, or the abs(count)'th
74 row from the end if count is negative. Position before
75 first row or after last row if count is out of range; in
76 particular, ABSOLUTE 0 positions before the first row.
77
78 RELATIVE count
79 Fetch the count'th succeeding row, or the abs(count)'th
80 prior row if count is negative. RELATIVE 0 re-fetches the
81 current row, if any.
82
83 count Fetch the next count rows (same as FORWARD count).
84
85 ALL Fetch all remaining rows (same as FORWARD ALL).
86
87 FORWARD
88 Fetch the next row (same as NEXT).
89
90 FORWARD count
91 Fetch the next count rows. FORWARD 0 re-fetches the cur‐
92 rent row.
93
94 FORWARD ALL
95 Fetch all remaining rows.
96
97 BACKWARD
98 Fetch the prior row (same as PRIOR).
99
100 BACKWARD count
101 Fetch the prior count rows (scanning backwards). BACKWARD
102 0 re-fetches the current row.
103
104 BACKWARD ALL
105 Fetch all prior rows (scanning backwards).
106
107 count count is a possibly-signed integer constant, determining the
108 location or number of rows to fetch. For FORWARD and BACKWARD
109 cases, specifying a negative count is equivalent to changing the
110 sense of FORWARD and BACKWARD.
111
112 cursorname
113 An open cursor's name.
114
116 On successful completion, a FETCH command returns a command tag of the
117 form
118
119 FETCH count
120
121 The count is the number of rows fetched (possibly zero). Note that in
122 psql, the command tag will not actually be displayed, since psql dis‐
123 plays the fetched rows instead.
124
126 The cursor should be declared with the SCROLL option if one intends to
127 use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a
128 positive count. For simple queries PostgreSQL will allow backwards
129 fetch from cursors not declared with SCROLL, but this behavior is best
130 not relied on. If the cursor is declared with NO SCROLL, no backward
131 fetches are allowed.
132
133 ABSOLUTE fetches are not any faster than navigating to the desired row
134 with a relative move: the underlying implementation must traverse all
135 the intermediate rows anyway. Negative absolute fetches are even
136 worse: the query must be read to the end to find the last row, and then
137 traversed backward from there. However, rewinding to the start of the
138 query (as with FETCH ABSOLUTE 0) is fast.
139
140 Updating data via a cursor is currently not supported by PostgreSQL.
141
142 DECLARE [declare(7)] is used to define a cursor. Use MOVE [move(7)] to
143 change cursor position without retrieving data.
144
146 The following example traverses a table using a cursor.
147
148 BEGIN WORK;
149
150 -- Set up a cursor:
151 DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
152
153 -- Fetch the first 5 rows in the cursor liahona:
154 FETCH FORWARD 5 FROM liahona;
155
156 code | title | did | date_prod | kind | len
157 -------+-------------------------+-----+------------+----------+-------
158 BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44
159 BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43
160 JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
161 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
162 P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28
163
164 -- Fetch the previous row:
165 FETCH PRIOR FROM liahona;
166
167 code | title | did | date_prod | kind | len
168 -------+---------+-----+------------+--------+-------
169 P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
170
171 -- Close the cursor and end the transaction:
172 CLOSE liahona;
173 COMMIT WORK;
174
175
177 The SQL standard defines FETCH for use in embedded SQL only. The vari‐
178 ant of FETCH described here returns the data as if it were a SELECT
179 result rather than placing it in host variables. Other than this point,
180 FETCH is fully upward-compatible with the SQL standard.
181
182 The FETCH forms involving FORWARD and BACKWARD, as well as the forms
183 FETCH count and FETCH ALL, in which FORWARD is implicit, are PostgreSQL
184 extensions.
185
186 The SQL standard allows only FROM preceding the cursor name; the option
187 to use IN is an extension.
188
190 CLOSE [close(7)], DECLARE [declare(l)], MOVE [move(l)]
191
192
193
194SQL - Language Statements 2008-06-08 FETCH()