Product Documentation

Appearance

FETCH ​

Fetch rows from a cursor.

FETCH [ direction [ FROM | IN ] ] name

where direction is any one of the following (and name is an open cursor):

  • NEXT | FIRST | LAST
  • ABSOLUTE count
  • RELATIVE count
  • count
  • ALL
  • FORWARD [ count | ALL ]

See also MOVE cursor, which changes the position of a cursor without returning any rows.

If no direction is specified, the FETCH command fetches the next row.

FETCH commands rely on the current position associated with the cursor. When you declare a cursor, it is positioned before the first row. After some rows are fetched, the cursor is positioned on the last retrieved row, and the next fetch proceeds from that position.

If FETCH reaches the end of the available rows, the cursor is positioned after the last row. FETCH ALL always leaves the cursor positioned after or before the first row.

NEXT, FIRST, LAST, ABSOLUTE, and RELATIVE fetch a single row after moving the cursor appropriately. If there is no such row, FETCH returns an empty result, and the cursor is positioned before the first row or after the last row (as appropriate).

FORWARD returns the requested number of rows, moving forward, leaving the cursor positioned on the last-returned row (or after all rows if count exceeds the number of rows available).

RELATIVE 0 and FORWARD 0 fetch the current row without moving the cursor. In other words, they fetch the most recently fetched row again. No row is returned if the cursor is positioned before the first row or after the last row.

For example:

premdb=# begin;
BEGIN
premdb=# declare team cursor for select * from team order by teamid;
DECLARE CURSOR
premdb=# fetch forward 5 from team;
 teamid | htid | atid |       name       | nickname |    city    |     stadium      | capacity 
--------+------+------+------------------+----------+------------+------------------+----------
     1 |    2 |   51 | Arsenal          | Gunners  | London     | Emirates Stadium |    60260
     2 |    3 |   52 | Aston Villa      | Villains | Birmingham | Villa Park       |    42785
     3 |    4 |   53 | Barnsley         | Tykes    | Barnsley   | Oakwell Stadium  |    23009
     4 |    5 |   54 | Birmingham City  | Blues    | Birmingham | St. Andrew's     |    30016
     5 |    6 |   55 | Blackburn Rovers | Rovers   | Blackburn  | Ewood Park       |    31367
(5 rows)

premdb=# fetch forward 5 from team;
 teamid | htid | atid |       name       | nickname  |    city     |     stadium      | capacity 
--------+------+------+------------------+-----------+-------------+------------------+----------
     6 |    7 |   56 | Blackpool        | Seasiders | Blackpool   | Bloomfield Road  |    17338
     7 |    8 |   57 | Bolton Wanderers | Trotters  | Bolton      | Macron Stadium   |    28723
     8 |    9 |   58 | Bournemouth      | Cherries  | Bournemouth | Vitality Stadium |    11464
     9 |   10 |   59 | Bradford City    | Bantams   | Bradford    | Valley Parade    |    25136
    10 |   11 |   60 | Burnley          | Clarets   | Burnley     | Turf Moor        |    22546
(5 rows)

Parent topic:SQL Commands