How to create and use a cursor in PostgreSQL

Introduction

In database management, cursors are essential for navigating through rows returned by a query while having the ability to process each row individually. This guide will walk you through creating and using a cursor in PostgreSQL with step-by-step code examples.

Mastering Cursors in PostgreSQL

Before diving into the code, it’s important to understand what a cursor is. A cursor in PostgreSQL is a database query object that allows you to iterate over the results of an SQL query one row at a time. They are particularly useful for operations that require row-by-row processing, such as complex calculations or batch updates.

To create a cursor in PostgreSQL, you use the DECLARE statement. You then use the FETCH command to retrieve individual rows, and finally, use CLOSE to release the cursor when you are done.

Basic Cursor Operations

-- Create a cursor
BEGIN;
DECLARE my_cursor CURSOR FOR SELECT * FROM my_table;

-- Fetch the first row
FETCH FIRST FROM my_cursor;

-- Fetch the next row
FETCH NEXT FROM my_cursor;

-- Close the cursor
CLOSE my_cursor;
COMMIT;

Using Cursors Within a Transaction

It’s essential to understand that cursors exist within the context of a transaction. This means once a transaction is closed (either committed or rolled back), the cursor ceases to exist. Below is an example of how you might use a cursor within a transaction:

BEGIN;
DECLARE transaction_cursor CURSOR FOR SELECT * FROM sales_data;

FETCH NEXT FROM transaction_cursor;
-- Process row

FETCH NEXT FROM transaction_cursor;
-- Process next row

CLOSE transaction_cursor;
COMMIT;

Scrollable Cursors

By default, cursors in PostgreSQL are ‘no-scroll’, meaning you cannot move backwards through the result set. However, if you declare a scrollable cursor, you can move in both directions.

BEGIN;
DECLARE scrollable_cursor SCROLL CURSOR FOR SELECT * FROM account_history;

-- Fetch the last row
FETCH LAST FROM scrollable_cursor;

-- Fetch the prior row
FETCH PRIOR FROM scrollable_cursor;

-- Fetch a specific row
FETCH ABSOLUTE 5 FROM scrollable_cursor;

-- Fetch rows in a range
FETCH FORWARD 10 FROM scrollable_cursor;

-- Close the cursor
CLOSE scrollable_cursor;
COMMIT;

Holding Cursors Beyond Transactions

In PostgreSQL, you also have the option to declare a cursor WITH HOLD, which allows it to continue existing even after the transaction that created it has been committed. This can be useful in situations where you need to maintain the state of processing across transactions.

BEGIN;
DECLARE hold_cursor CURSOR WITH HOLD FOR SELECT * FROM user_logins;
COMMIT;

FETCH NEXT FROM hold_cursor;
-- Process row

-- Close the cursor
CLOSE hold_cursor;

Parameterized Cursors

You may find yourself needing to create a cursor that takes parameters to filter the query results dynamically. This can be accomplished by using the OPEN-FOR-USING statement in a PL/pgSQL block or stored procedure.

DO $
DECLARE
    param_cursor REFCURSOR;
    account_id integer := 10;
BEGIN
    OPEN param_cursor FOR SELECT * FROM transactions WHERE account_id = account_id;

    -- Fetch and process rows
    -- ...

    CLOSE param_cursor;
END;
$;

Advanced Cursor Usage in PostgreSQL

Cursor Functions

PostgreSQL allows the use of cursor functions that encapsulate cursor logic within a callable function. This approach can lead to more maintainable code by separating the cursor usage logic from the application code.

Here is how to encapsulate a cursor in a function:

CREATE FUNCTION fetch_accounts_cursor() RETURNS void AS $
DECLARE
    account_cursor CURSOR FOR SELECT * FROM accounts;
BEGIN

    -- Open the cursor
    OPEN account_cursor;

    -- Loop to process each row
    LOOP
        FETCH account_cursor INTO account_record;
        EXIT WHEN NOT FOUND;
        -- Process each account_record
    END LOOP;

    -- Close the cursor
    CLOSE account_cursor;
END
$ LANGUAGE plpgsql;

Controlling Cursors with SAVEPOINT

Using SAVEPOINT with cursors allows you to set a point within a transaction that you can rollback to without affecting the entire transaction. This can be useful for complex sequences of operations where you may need to undo a part of the transaction due to an error but do not wish to rollback entirely.

BEGIN;
SAVEPOINT my_savepoint;
DECLARE error_cursor CURSOR FOR SELECT * FROM error_logs;

-- Attempt operation
-- If failure occurs:
ROLLBACK TO SAVEPOINT my_savepoint;

-- Proceed with next operations

CLOSE error_cursor;
COMMIT;

Using Cursors with Dynamic SQL

PostgreSQL allows creating cursors that can execute dynamic SQL with the EXECUTE statement. This advanced use of cursors is particularly valuable when the query itself is constructed programmatically during runtime.

DO $
DECLARE
    dynamic_cursor REFCURSOR;
    table_name text := 'dynamic_table';
    dynamic_query text;
BEGIN
    dynamic_query := 'SELECT * FROM ' || quote_ident(table_name);
    OPEN dynamic_cursor FOR EXECUTE dynamic_query;

    -- Fetch and process rows
    -- ...

    CLOSE dynamic_cursor;
END;
$;

Conclusion

This guide aimed to provide a clear understanding of how to create and use cursors in PostgreSQL, with examples from basic to advanced usage. Whether you are navigating through large data sets, needing row-by-row processing, or dealing with dynamic queries, mastering cursors can significantly enhance the power and efficiency of your PostgreSQL scripts.