websql

25 Using Cursors

Learning Objectives

:

Describe what a cursor is and why it's used.

Describe the declare-open-fetch-close lifecycle of a cursor.

Declare and open a cursor in PostgreSQL, using both plain SQL and PL/pgSQL.

Use FETCH within a loop to step through a cursor's result set, checking FOUND to detect the end of the data.

Close a cursor when you are finished with it.

SQL retrieval operations work with sets of rows known as result sets. The rows returned are all the rows that match a SQL statement — zero or more of them. When you use simple SELECT statements, there is no way to get the first row, the next row, or the previous 10 rows. This is an integral part of how a relational DBMS works.

Result Set — The results retrieved by a SQL query.

Sometimes you need to step through rows forward or backward and one or more at a time. This is what cursors are used for. A cursor is a database query stored on the DBMS server — not a SELECT statement, but the result set retrieved by that statement. Once the cursor is stored, applications can scroll or browse up and down through the data as needed.

Different DBMSs support different cursor options and features. Some of the more common ones are:

The capability to flag a cursor as read-only so that data can be read but not updated or deleted
The capability to control the directional operations that can be performed (forward, backward, first, last, absolute position, relative position, and so on)
The capability to flag some columns as editable and others as not editable
Scope specification so as to be able to make the cursor accessible to the specific request that created it (a function or procedure, for example) or to all requests
Instructing the DBMS to make a copy of the retrieved data (as opposed to pointing to the live data in the table) so that data does not change between the time the cursor is opened and the time it is accessed

PostgreSQL supports most of these: cursors can be marked SCROLL (to allow backward movement and jumping to arbitrary positions) or NO SCROLL (forward-only), and can be declared WITH HOLD so that they remain open beyond the transaction that created them.

Cursors are used primarily by interactive applications in which users need to scroll up and down through screens of data, browsing or making changes.

25.1 Working with Cursors

Using cursors involves several distinct steps:

Before a cursor can be used, it must be declared (defined). This process does not actually retrieve any data; it merely defines the SELECT statement to be used and any cursor options.
Once it is declared, the cursor must be opened for use. This process actually retrieves the data using the previously defined SELECT statement.
With the cursor populated with data, individual rows can be fetched (retrieved) as needed.
When it is done, the cursor must be closed.

Once a cursor is declared, it may be opened and closed as often as needed. Once it is open, fetch operations can be performed as often as needed.

Cursors Live Inside Transactions. In PostgreSQL, a cursor only exists within a transaction (unless it was declared WITH HOLD). This means the entire declare-open-fetch-close sequence for a plain SQL cursor must happen inside a BEGIN...COMMIT block, as described in the previous lesson on transaction processing.

25.2 Creating Cursors

To demonstrate cursors, we'll create one that retrieves all museums without a website on file, as part of an application enabling an operator to fill in the missing websites one at a time.

PostgreSQL actually supports two related but distinct forms of cursor. The first is a plain SQL cursor, declared and used directly in a transaction:

BEGIN;

DECLARE museum_cursor CURSOR FOR
    SELECT * FROM museum WHERE url IS NULL;

Notice that, at this top level, DECLARE both defines and opens the cursor in a single step — there is no separate OPEN statement here. The cursor is now ready to be fetched from.

The second form is used inside PL/pgSQL — the procedural language you used in the lesson on stored procedures — such as within a function, a procedure, or a one-off anonymous code block. There, DECLARE only defines the cursor; it must still be opened explicitly with OPEN, matching the full declare-open-fetch-close lifecycle described above. The rest of this lesson focuses on this PL/pgSQL form, since it demonstrates every step of that lifecycle.

25.3 Using Cursors

The following anonymous PL/pgSQL block declares the same cursor, opens it, fetches a single row from it, and closes it again:

DO $$
DECLARE
    museum_cursor CURSOR FOR
        SELECT * FROM museum WHERE url IS NULL;
    museum_record museum%ROWTYPE;
BEGIN
    OPEN museum_cursor;
    FETCH museum_cursor INTO museum_record;
    CLOSE museum_cursor;
END $$;

In this example, museum_cursor is declared in the DECLARE section along with a variable, museum_record, typed as museum%ROWTYPE (a row with the same shape as a row of the museum table). The BEGIN...END block then opens the cursor, uses FETCH to retrieve the current row (it starts at the first row automatically) into museum_record, and closes the cursor. Nothing is done with the retrieved data.

In the next example, the retrieved data is looped through from the first row to the last:

DO $$
DECLARE
    museum_cursor CURSOR FOR
        SELECT * FROM museum WHERE url IS NULL;
    museum_record museum%ROWTYPE;
BEGIN
    OPEN museum_cursor;
    LOOP
        FETCH museum_cursor INTO museum_record;
        EXIT WHEN NOT FOUND;
        -- process museum_record here
    END LOOP;
    CLOSE museum_cursor;
END $$;

Like the previous example, this example uses FETCH to retrieve the current row into museum_record. Unlike the previous example, the FETCH here is within a LOOP so that it is repeated over and over. PostgreSQL automatically sets a special boolean, FOUND, after every FETCH — true if a row was retrieved, false if there were no more rows left. The line EXIT WHEN NOT FOUND; uses this to terminate the loop (exiting it) once the cursor is exhausted. This example also does no actual processing; in real-world code you'd replace the -- process museum_record here comment with your own logic — for example, code that prompts an operator for a website and issues an UPDATE for that row.

FETCH Directions. The examples above always fetch the next row. PostgreSQL's FETCH also supports FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD count, and BACKWARD count, letting you move through a cursor's result set in more than one direction — provided the cursor was declared SCROLL rather than the default NO SCROLL.

25.4 Closing Cursors

As already mentioned and seen in the previous examples, cursors need to be closed after they have been used:

CLOSE museum_cursor;

The CLOSE statement is used to close cursors; once a cursor is closed, it cannot be reused without being opened again (for a plain SQL cursor, that means running DECLARE again; for a PL/pgSQL cursor, an OPEN statement is sufficient). Unlike some other DBMSs, PostgreSQL does not require a separate step to deallocate the resources used by a cursor — closing it (or simply letting the enclosing transaction end) is enough.

25.5 Summary

In this lesson, you were introduced to cursors, what they are, and why they are used. You saw that PostgreSQL supports both a simple, auto-opening form of cursor for use directly inside a transaction, and a more explicit declare-open-fetch-close form for use inside PL/pgSQL functions, procedures, and anonymous code blocks. Refer to the PostgreSQL documentation for more details.