Name
FETCH -- Retrieves rows from a cursor.
Synopsis
FETCH [ FORWARD | BACKWARD | RELATIVE ] [ # | ALL | NEXT | PRIOR ] { IN | FROM } cursor
Parameters
- direction
Use the direction parameter to specify the direction you want to fetch.
Use FORWARD to fetch the next row(s). This is the default option for this parameter if
none are explicitly specified. Use BACKWARD to fetch the previous row(s). The
RELATIVE keyword is a noise term provided for SQL92 compatibility, and has no functional
effect.
- count
This parameter takes the number of rows you wish to fetch. You can specify a number here to have a specific number of
rows fetched, or specify ALL to retrieve all rows,
NEXT to retrieve the next row, or PRIOR to retrieve
the previous row.
- cursor
Use this parameter to specify the name of an open cursor you wish to use for the fetch.
Results
If successful, the FETCH command returns any query
results generated by the specified cursor. If the query fails, one of the messages
explained below will be displayed:
- NOTICE: PerformPortalFetch: portal "cursor" not found
This message is displayed if the specified cursor has not previously been declared.
You must declare the cursor within a transaction block before it can be used.
- NOTICE: FETCH/ABSOLUTE not supported, using RELATIVE
This message is displayed if you attempt to use absolute positioning with the ABSOLUTE
keyword instead of RELATIVE. PostgreSQL does not currently support the user of
absolute positioning of cursors.
- ERROR: FETCH/RELATIVE at current position is not supported
This error is displayed if you attempt to fetch row 0 with the RELATIVE direction
specified. This happens because attempting to select row 0 relatively (with the
syntax FETCH RELATIVE 0 FROM ...) is defined
within SQL92 as allowing a user to continually retrieve the rows at the cursor's
current position.
PostgreSQL does not support the use of this syntax; instead of returning the current position, the use of
0 indicates to the database that you wish to retrieve ALL rows.
PostgreSQL assumes you are instead trying to use the SQL92 defined functionality and displays this error instead of
fetching all rows.
Description
Use the FETCH command to retrieve a specified number of rows using a cursor. The
number you specify can be either a positive or negative number. A positive number will fetch from whatever direction you
specify with the direction parameter (if you don't specify a direction, FORWARD will be used
by default). A negative number will take you in the opposite direction as specified in with the direction; for example, issuing the command with FORWARD -5
is the same as issuing it with BACKWARD 5. If the number of rows you specify is higher than
the number of rows left in the loop, however many are left will be retrieved. Also, you will always need to be within a
transaction while using cursors, as the data they store is not independent of other users within the system.
 | You cannot update data from a cursor. |
Examples
The following examples assume a transaction and an already-defined cursor
(cur_employee) that uses the employees table for data.
This example fetches the first two rows in the cursor.
booktown=# FETCH FORWARD 2 IN cur_employee;
+-----+-----------+------------+
| id | last_name | first_name |
+-----+-----------+------------+
| 101 | Mills | Jonathan |
| 102 | Holloway | Michael |
+-----+-----------+------------+
(2 rows)
Now, we will fetch backward negative two rows (this effectively steps forward two rows).
booktown=# FETCH BACKWARD -2 IN cur_employee;
+-----+-----------+------------+
| id | last_name | first_name |
+-----+-----------+------------+
| 103 | Joble | David |
| 104 | Noble | Ben |
+-----+-----------+------------+
(2 rows)
The next example demonstrates how to fetch backward.
booktown#= FETCH BACKWARD 1 IN cur_employee;
+-----+-----------+------------+
| id | last_name | first_name |
+-----+-----------+------------+
| 103 | Joble | David |
+-----+-----------+------------+
(1 row)