FETCH
YSQL currently supports only fetching rows from a cursor consecutively in the forward direction.
See the subsection Beware Issue #6514 in the generic section Cursors.Synopsis
Use the FETCH
statement to fetch one or several rows from a cursor. See the generic section Cursors. The FETCH
statement is used jointly with the DECLARE
, MOVE
, and CLOSE
statements.
Syntax
fetch ::= FETCH [ fetch_one_row | fetch_many_rows ] [ FROM | IN ] name
fetch_one_row ::= FIRST
| LAST
| ABSOLUTE int_literal
| NEXT
| FORWARD
| PRIOR
| BACKWARD
| RELATIVE int_literal
fetch_many_rows ::= ALL | FORWARD ALL
| FORWARD int_literal
| int_literal
| BACKWARD ALL
| BACKWARD int_literal
fetch
fetch_one_row
fetch_many_rows
Semantics
FETCH
fetches one or several rows from a cursor.
A cursor represents the current position in its result set. After declaring a cursor but before the first FETCH
or MOVE
execution, the current position is immediately before the first row.
-
The
FETCH FORWARD 0
variant fetches the row at the current position and leaves the current position unchanged. -
The
FETCH NEXT
variant, the bareFETCH
variant, the bareFETCH FORWARD
variant, and theFETCH FORWARD 1
variant all fetch the row immediately after the current position and update the current position to the just-fetched row. However, if before executing one of theseFETCH
variants, the current position is the last row in the result set, then theFETCH
runs off the end of the available rows, an empty result is returned, and the cursor position is left after the last row. There are no flavors of after the last row. It's a uniquely defined state so that following any number of invocations ofFETCH NEXT
in this state,FETCH PRIOR
will then fetch the last row in the result set (and update the current position to that last row.) -
The
FETCH PRIOR
variant, the bareFETCH BACKWARD
variant, and theFETCH BACKWARD 1
variant all fetch the row immediately before the current position and update the current position to the just-fetched row. However, if before executing one of theseFETCH
variants, the current position is the first row in the result set, then theFETCH
runs off the start of the available rows, an empty result is returned, and the cursor position is left before the first row. There are no flavors of before the first row. It's a uniquely defined state so that after following any number of invocations ofFETCH PRIOR
in this state,FETCH NEXT
will then fetch the first row in the result set (and update the current position to that first row).* -
FETCH ALL
andFETCH FORWARD ALL
fetch all the rows from the row immediately after the current position through the last row, and the cursor position is left after the last row. Of course, if whenFETCH ALL
(orFETCH FORWARD ALL
) is invoked, the current position is the last row, or after the last row, then an empty result is returned and the current position is left after the last row. -
FETCH BACKWARD ALL
fetches all the rows from the row immediately before the current position through the first row, and the cursor position is left before the first row. Of course, if whenFETCH BACKWARD ALL
is invoked, the current position is the first row, or before the first row, then an empty result is returned and the current position is left before the first row.* -
The
FETCH :n
andFETCH FORWARD :n
variants fetch exactly :n rows forwards from and including the row after the current position when this many rows are available and otherwise just as many as there are to fetch analogously to howFETCH FORWARD ALL
behaves. -
The
FETCH BACKWARD :n
variant fetches exactly :n rows backwards from and including the row before the current position when this many rows are available and otherwise just as many as there are to fetch analogously to howFETCH BACKWARD ALL
behaves.* -
The
FETCH ABSOLUTE :n
variant fetches the single row at exactly the indicated absolute position. TheFETCH RELATIVE :n
variant fetches the single row at exactly the indicated relative position (:n can be negative) to the current row. For bothFETCH ABSOLUTE :n
andFETCH RELATIVE :n
, the requested row might lie before the first row or after the last row. The outcome here is the same as it is when executing otherFETCH
variants cause the current position to fall outside the range from the first through the last row in the cursor's result set. Notice that :n can be negative for both theABSOLUTE
and theRELATIVE
variants.* -
Each of the
FETCH FIRST
andFETCH LAST
variants fetches, respectively, the first row or the last row. The meanings are therefore insensitive to the current cursor position, and each can be repeated time and again and will always produce the same result.*
Notice that the three variants ,FETCH FORWARD 0
, FETCH BACKWARD 0
, and FETCH RELATIVE 0
, all mean the same as each other.*
[*] See the subsection Beware Issue #6514 in the generic section Cursors.
name
A cursor is identified only by an unqualified name and is visible only in the session that declares it. This determines the uniqueness scope for its name. (The name of a cursor is like that of a prepared statement in this respect.)
Simple example
drop table if exists t cascade;
create table t(k, v) as
select g.val, g.val*100
from generate_series(1, 22) as g(val);
start transaction;
declare cur scroll cursor without hold for
select k, v
from t
where (k <> all (array[1, 3, 5, 7, 11, 13, 17, 19]))
order by k;
fetch forward from cur;
fetch forward from cur;
fetch forward from cur;
fetch forward 0 from cur;
fetch forward 0 from cur;
fetch forward all from cur;
rollback;
This is the result. (Blanks lines were added manually to improve the readability.)
k | v
----+------
2 | 200
4 | 400
6 | 600
6 | 600
6 | 600
8 | 800
9 | 900
10 | 1000
12 | 1200
14 | 1400
15 | 1500
16 | 1600
18 | 1800
20 | 2000
21 | 2100
22 | 2200