FETCH
FETCH — Retrieve rows from a query using a cursor
Synopsis
FETCH [ direction [ FROM | IN ] ] cursor_name
where direction can be empty or one of the following:
NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL
Description
FETCH retrieves rows from a previously created cursor.
A cursor has an associated position, which is used by FETCH. The cursor position can be before the first row of the query result, on any particular row of the result, or after the last row of the result. When created, a cursor is positioned before the first row. After fetching some rows, the cursor is positioned on the row most recently fetched. If FETCH runs past the end of available rows, the cursor is positioned after the last row (or before the first row, if fetching backward). FETCH ALL or FETCH BACKWARD ALL will always leave the cursor positioned after the last row or before the first row.
The NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE forms move the cursor appropriately and then fetch one row. If no such row exists, an empty result is returned, and the cursor is positioned before the first row or after the last row as appropriate.
The forms using FORWARD and BACKWARD retrieve the specified number of rows in the forward or backward direction, then position the cursor on the last returned row (or after/before all rows if count exceeds available rows).
RELATIVE 0, FORWARD 0, and BACKWARD 0 all request retrieving the current row without moving the cursor, i.e., re-fetching the most recently fetched row. This operation succeeds as long as the cursor is not positioned before the first row or after the last row; otherwise, no rows are returned.
Parameters
direction
direction defines the fetch direction and the number of rows to fetch. It can be one of the following:
NEXT
Fetch the next row. This is the default if direction is omitted.
PRIOR
Fetch the row preceding the current position.
FIRST
Fetch the first row of the query (same as ABSOLUTE 1).
LAST
Fetch the last row of the query (same as ABSOLUTE -1).
ABSOLUTE count
Fetch the count'th row of the query, or if count is negative, fetch the abs(count)'th row from the end. If count is out of range, the cursor is positioned before the first row or after the last row. In particular, ABSOLUTE 0 positions before the first row.
RELATIVE count
Fetch the count'th successive row, or if count is negative, fetch the abs(count)'th preceding row. RELATIVE 0 re-fetches the current row, if any.
count
Fetch the next count rows (same as FORWARD count).
ALL
Fetch all remaining rows (same as FORWARD ALL).
FORWARD
Fetch the next row (same as NEXT).
FORWARD count
Fetch the next count rows. FORWARD 0 re-fetches the current row.
FORWARD ALL
Fetch all remaining rows.
BACKWARD
Fetch the row preceding the current one (same as PRIOR).
BACKWARD count
Fetch the preceding count rows (scanning backwards). BACKWARD 0 re-fetches the current row.
BACKWARD ALL
Fetch all rows preceding the current position (scanning backwards).
count
count is a possibly-signed integer constant that determines the position or number of rows to fetch. For FORWARD and BACKWARD cases, specifying a negative count is equivalent to swapping the meaning of FORWARD and BACKWARD.
cursor_name
The name of an open cursor.
Output
If successful, the FETCH command returns a command tag of the form:
FETCH count
count is the number of rows fetched (possibly zero). Note that in psql, the command tag will not actually be displayed, since psql displays the fetched rows instead.
Notes
If you want to use any variant of FETCH other than FETCH NEXT or FETCH FORWARD with a positive count, the cursor should be declared with SCROLL. For simple queries, Halo will allow backward fetching from a cursor declared without SCROLL, but it is best not to rely on this behavior. If the cursor is declared with SCROLL, backward fetching is not allowed.
Fetching rows with ABSOLUTE is not significantly faster than fetching with relative movement: the underlying implementation must traverse all intermediate rows in either case. Fetching with a negative absolute value is even worse: it must read to the end of the query to find the last row, and then traverse backward from there. However, rewinding to the start of the query (as with FETCH ABSOLUTE 0) is fast.
DECLARE is used to define a cursor. Use MOVE to change the cursor position without retrieving data.
Examples
-- The following example traverses a table using a cursor:
BEGIN WORK;
-- Set up a cursor:
DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films;
-- Fetch the first 5 rows from cursor liahona:
FETCH FORWARD 5 FROM liahona;
code | title | did | date_prod | kind | len
-------+-------------------------+-----+------------+----------+-------
BL101 | The Third Man | 101 | 1949-12-23 | Drama | 01:44
BL102 | The African Queen | 101 | 1951-08-11 | Romantic | 01:43
JL201 | Une Femme est une Femme | 102 | 1961-03-12 | Romantic | 01:25
P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
P_302 | Becket | 103 | 1964-02-03 | Drama | 02:28
-- Fetch the previous row:
FETCH PRIOR FROM liahona;
code | title | did | date_prod | kind | len
-------+---------+-----+------------+--------+-------
P_301 | Vertigo | 103 | 1958-11-14 | Action | 02:08
1601
FETCH
-- Close the cursor and end the transaction:
CLOSE liahona;
COMMIT WORK;