1FETCH(7)                 PostgreSQL 14.3 Documentation                FETCH(7)
2
3
4

NAME

6       FETCH - retrieve rows from a query using a cursor
7

SYNOPSIS

9       FETCH [ direction [ FROM | IN ] ] cursor_name
10
11       where direction can be empty or one of:
12
13           NEXT
14           PRIOR
15           FIRST
16           LAST
17           ABSOLUTE count
18           RELATIVE count
19           count
20           ALL
21           FORWARD
22           FORWARD count
23           FORWARD ALL
24           BACKWARD
25           BACKWARD count
26           BACKWARD ALL
27

DESCRIPTION

29       FETCH retrieves rows using a previously-created cursor.
30
31       A cursor has an associated position, which is used by FETCH. The cursor
32       position can be before the first row of the query result, on any
33       particular row of the result, or after the last row of the result. When
34       created, a cursor is positioned before the first row. After fetching
35       some rows, the cursor is positioned on the row most recently retrieved.
36       If FETCH runs off the end of the available rows then the cursor is left
37       positioned after the last row, or before the first row if fetching
38       backward.  FETCH ALL or FETCH BACKWARD ALL will always leave the cursor
39       positioned after the last row or before the first row.
40
41       The forms NEXT, PRIOR, FIRST, LAST, ABSOLUTE, RELATIVE fetch a single
42       row after moving the cursor appropriately. If there is no such row, an
43       empty result is returned, and the cursor is left positioned before the
44       first row or after the last row as appropriate.
45
46       The forms using FORWARD and BACKWARD retrieve the indicated number of
47       rows moving in the forward or backward direction, leaving the cursor
48       positioned on the last-returned row (or after/before all rows, if the
49       count exceeds the number of rows available).
50
51       RELATIVE 0, FORWARD 0, and BACKWARD 0 all request fetching the current
52       row without moving the cursor, that is, re-fetching the most recently
53       fetched row. This will succeed unless the cursor is positioned before
54       the first row or after the last row; in which case, no row is returned.
55
56           Note
57           This page describes usage of cursors at the SQL command level. If
58           you are trying to use cursors inside a PL/pgSQL function, the rules
59           are different — see Section 43.7.3.
60

PARAMETERS

62       direction
63           direction defines the fetch direction and number of rows to fetch.
64           It can be one of the following:
65
66           NEXT
67               Fetch the next row. This is the default if direction is
68               omitted.
69
70           PRIOR
71               Fetch the prior row.
72
73           FIRST
74               Fetch the first row of the query (same as ABSOLUTE 1).
75
76           LAST
77               Fetch the last row of the query (same as ABSOLUTE -1).
78
79           ABSOLUTE count
80               Fetch the count'th row of the query, or the abs(count)'th row
81               from the end if count is negative. Position before first row or
82               after last row if count is out of range; in particular,
83               ABSOLUTE 0 positions before the first row.
84
85           RELATIVE count
86               Fetch the count'th succeeding row, or the abs(count)'th prior
87               row if count is negative.  RELATIVE 0 re-fetches the current
88               row, if any.
89
90           count
91               Fetch the next count rows (same as FORWARD count).
92
93           ALL
94               Fetch all remaining rows (same as FORWARD ALL).
95
96           FORWARD
97               Fetch the next row (same as NEXT).
98
99           FORWARD count
100               Fetch the next count rows.  FORWARD 0 re-fetches the current
101               row.
102
103           FORWARD ALL
104               Fetch all remaining rows.
105
106           BACKWARD
107               Fetch the prior row (same as PRIOR).
108
109           BACKWARD count
110               Fetch the prior count rows (scanning backwards).  BACKWARD 0
111               re-fetches the current row.
112
113           BACKWARD ALL
114               Fetch all prior rows (scanning backwards).
115
116       count
117           count is a possibly-signed integer constant, determining the
118           location or number of rows to fetch. For FORWARD and BACKWARD
119           cases, specifying a negative count is equivalent to changing the
120           sense of FORWARD and BACKWARD.
121
122       cursor_name
123           An open cursor's name.
124

OUTPUTS

126       On successful completion, a FETCH command returns a command tag of the
127       form
128
129           FETCH count
130
131       The count is the number of rows fetched (possibly zero). Note that in
132       psql, the command tag will not actually be displayed, since psql
133       displays the fetched rows instead.
134

NOTES

136       The cursor should be declared with the SCROLL option if one intends to
137       use any variants of FETCH other than FETCH NEXT or FETCH FORWARD with a
138       positive count. For simple queries PostgreSQL will allow backwards
139       fetch from cursors not declared with SCROLL, but this behavior is best
140       not relied on. If the cursor is declared with NO SCROLL, no backward
141       fetches are allowed.
142
143       ABSOLUTE fetches are not any faster than navigating to the desired row
144       with a relative move: the underlying implementation must traverse all
145       the intermediate rows anyway. Negative absolute fetches are even worse:
146       the query must be read to the end to find the last row, and then
147       traversed backward from there. However, rewinding to the start of the
148       query (as with FETCH ABSOLUTE 0) is fast.
149
150       DECLARE is used to define a cursor. Use MOVE to change cursor position
151       without retrieving data.
152

EXAMPLES

154       The following example traverses a table using a cursor:
155
156           BEGIN WORK;
157
158           -- Set up a cursor:
159           DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
160
161           -- Fetch the first 5 rows in the cursor liahona:
162           FETCH FORWARD 5 FROM liahona;
163
164            code  |          title          | did | date_prod  |   kind   |  len
165           -------+-------------------------+-----+------------+----------+-------
166            BL101 | The Third Man           | 101 | 1949-12-23 | Drama    | 01:44
167            BL102 | The African Queen       | 101 | 1951-08-11 | Romantic | 01:43
168            JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
169            P_301 | Vertigo                 | 103 | 1958-11-14 | Action   | 02:08
170            P_302 | Becket                  | 103 | 1964-02-03 | Drama    | 02:28
171
172           -- Fetch the previous row:
173           FETCH PRIOR FROM liahona;
174
175            code  |  title  | did | date_prod  |  kind  |  len
176           -------+---------+-----+------------+--------+-------
177            P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
178
179           -- Close the cursor and end the transaction:
180           CLOSE liahona;
181           COMMIT WORK;
182

COMPATIBILITY

184       The SQL standard defines FETCH for use in embedded SQL only. The
185       variant of FETCH described here returns the data as if it were a SELECT
186       result rather than placing it in host variables. Other than this point,
187       FETCH is fully upward-compatible with the SQL standard.
188
189       The FETCH forms involving FORWARD and BACKWARD, as well as the forms
190       FETCH count and FETCH ALL, in which FORWARD is implicit, are PostgreSQL
191       extensions.
192
193       The SQL standard allows only FROM preceding the cursor name; the option
194       to use IN, or to leave them out altogether, is an extension.
195

SEE ALSO

197       CLOSE(7), DECLARE(7), MOVE(7)
198
199
200
201PostgreSQL 14.3                      2022                             FETCH(7)
Impressum