FETCH

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

• Grammar
• Diagram

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

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 bare FETCH variant, the bare FETCH FORWARD variant, and the FETCH 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 these FETCH variants, the current position is the last row in the result set, then the FETCH 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 of FETCH 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 bare FETCH BACKWARD variant, and the FETCH 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 these FETCH variants, the current position is the first row in the result set, then the FETCH 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 of FETCH 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 and FETCH 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 when FETCH ALL (or FETCH 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 when FETCH 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 and FETCH 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 how FETCH 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 how FETCH BACKWARD ALL behaves.*

• The FETCH ABSOLUTE :n variant fetches the single row at exactly the indicated absolute position. The FETCH RELATIVE :n variant fetches the single row at exactly the indicated relative position (:n can be negative) to the current row. For both FETCH ABSOLUTE :n and FETCH 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 other FETCH 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 the ABSOLUTE and the RELATIVE variants.*

• Each of the FETCH FIRST and FETCH 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

See also

• DECLARE
• MOVE
• CLOSE