Cursors

A cursor is a reference to the result set of a query and allows you to view the results one row at a time. Cursors remember their positions in result sets, which can be one of the following:

• a result row
• before the first row
• after the last row

You can also iterate over unopened, bound cursors with a FOR loop. See Control Flow for more information.

Declaring Cursors

Bound Cursors

To bind a cursor to a statement on declaration, use the FOR keyword:

cursor_name CURSOR [ ( arg_name arg_type [, ...] ) ] FOR statement;

The arguments to a cursor give you more control over which rows to process. For example, suppose you have the following table:

=> SELECT * FROM coordinates_xy;
 x | y 
---+----
 1 |  2 
 9 |  5 
 7 | 13  
...
(100000 rows)

If you're only interested in the rows where y is 6, you might declare the following cursor and then provide the argument 6 when you OPEN the cursor:

c CURSOR (key int) FOR SELECT * FROM coordinates_xy WHERE y=key;

Unbound Cursors

To declare a cursor without binding it to a particular query, use the refcursor type:

cursor_name refcursor;

You can bind an unbound cursor at any time with OPEN.

For example, to declare the cursor my_unbound_cursor:

my_unbound_cursor refcursor;

Opening and Closing Cursors

OPEN

Opening a cursor executes the query with the given arguments, and puts the cursor before the first row of the result set. The ordering of query results (and therefore, the start of the result set) is non-deterministic, unless you specify an ORDER BY clause.

OPEN a Bound Cursor

To open a cursor that was bound during declaration:

OPEN bound_cursor [ ( [ arg_name := ] arg_value [, ...] ) ];

For example, given the following declaration:

c CURSOR (key int) FOR SELECT * FROM t1 WHERE y=key;

You can open the cursor with one of the following:

OPEN c(5);
OPEN c(key := 5);

CLOSE

Open cursors are automatically closed when the cursor leaves scope, but you can close the cursor preemptively with CLOSE. Closed cursors can be reopened later, which re-executes the query and prepares a new result set.

CLOSE cursor;

OPEN an Unbound Cursor

To bind an unbound cursor and then open it:

OPEN unbound_cursor FOR statement;

You can also use EXECUTE because it's a statement:

OPEN unbound_cursor FOR EXECUTE statement_string [ USING expression [, ... ] ];

For example, to bind the cursor c to a query to a table product_data:

OPEN c for SELECT * FROM product_data;

FETCH Rows

FETCH statements:

1. Retrieve the row that the specified cursor currently points to and stores it in some variable.
2. Advance the cursor to the next position.

variable [, ...] := FETCH opened_cursor;

The retrieved value is stored in variable. Rows typically have more than one value, so you can use one variable for each.

If FETCH successfully retrieves a value, the special variable FOUND is set to true. Otherwise, if you call FETCH when the cursor is past the final row of the result set, it returns NULL and the special variable FOUND is set to false.

The following procedure creates a cursor c, binding it to a SELECT query on the coordinates table. The procedures passes the argument 1 to the cursor, so the cursor only retrieves rows where the y-coordinate is 1, storing the coordinates in the variables x_, y_, and z_.

Only two rows have a y-coordinate of 1, so after using FETCH twice, the third FETCH starts to return NULL values and FOUND is set to false:

=> SELECT * FROM coordinates;
 x  | y | z
----+---+----
 14 | 6 | 19
  1 | 6 |  2
 10 | 6 | 39
 10 | 2 |  1
  7 | 1 | 10
 67 | 1 | 77
(6 rows)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
    x_ int;
    y_ int;
    z_ int;
BEGIN
    OPEN c(1); -- only retrieve rows where y=1
    x_,y_,z_ := FETCH c;
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
    x_,y_,z_ := FETCH c; -- fetches the last set of results and moves to the end of the result set
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
    x_,y_,z_ := FETCH c; -- cursor has advanced past the final row
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
END;
$$;

NOTICE 2005:  cursor returned 7, 1, 10, FOUND=t
NOTICE 2005:  cursor returned 67, 1, 77, FOUND=t
NOTICE 2005:  cursor returned <NULL>, <NULL>, <NULL>, FOUND=f

MOVE Cursors

MOVE advances an open cursor to the next position without retrieving the row. The special FOUND variable is set to true if the cursor's position (before MOVE) was not past the final row—that is, if calling FETCH instead of MOVE would have retrieved the row.

MOVE bound_cursor;

For example, this cursor only retrieves rows where the y-coordinate is 2. The result set is only one row, so using MOVE twice advances past the first (and last) row, setting FOUND to false:

 => SELECT * FROM coordinates WHERE y=2;
 x  | y | z
----+---+---
 10 | 2 | 1
(1 row)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
BEGIN
    OPEN c(2); -- only retrieve rows where y=2, cursor starts before the first row
    MOVE c; -- cursor advances to the first (and last) row
    RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is true because the cursor points to a row in the result set
    MOVE c; -- cursor advances past the final row
    RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is false because the cursor is past the final row
END;
$$;

NOTICE 2005:  FOUND=t
NOTICE 2005:  FOUND=f