Cursors

Ordinarily, all rows generated by a SELECT are returned together to the client.
Cursors allow the individual result rows to be retrieved as needed by the client (an arbitrary number of rows at a time out of a larger query) , e.g. one after the other.

Cursors are only available in transactions (between BEGIN and COMMIT).

DECLARE and CLOSE a CURSOR

First the user has to create a cursor: DECLARE cursorname [ BINARY ] CURSOR FOR query query is an SQL query which will provide the rows to be governed by the cursor.
Cursors can return data either in text or in binary format.

CLOSE cursor deletes the cursor again.

FETCH -- retrieve rows

FETCH [ direction ] [ count ] { IN | FROM } cursor direction = FORWARD | BACKWARD count = # | ALL | NEXT | PRIOR

MOVE -- position a cursor

MOVE allows a user to move cursor position a specified number of rows (relative move). MOVE [ direction ] [ count ] { IN | FROM } cursor direction = FORWARD | BACKWARD count = # | ALL | NEXT | PRIOR

Example:

SELECT * FROM stud ORDER BY id ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 1 | fred | 2 | bio | m 2 | lisa | 2 | bio | f 3 | tom | 1 | bio | m 4 | john | 3 | phy | m BEGIN work ; DECLARE WC CURSOR FOR SELECT * FROM stud ORDER BY ID ; FETCH 1 FROM wc ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 1 | fred | 2 | bio | m FETCH 2 FROM wc ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 2 | lisa | 2 | bio | f 3 | tom | 1 | bio | m FETCH BACKWARD 2 FROM wc ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 2 | lisa | 2 | bio | f 1 | fred | 2 | bio | m FETCH 1 FROM wc ; id | name | semester | diploma | gender ----+------+----------+---------+-------- 2 | lisa | 2 | bio | f MOVE backward all IN wc ; CLOSE wc ; COMMIT work ; Note that cursor activity must take place inside a transaction (BEGIN ... COMMIT).