Cursor declaration and usage in PostgreSQL

Introduction

Cursors let you look at query results one row at a time. This helps when you want to handle data step-by-step instead of all at once.

When you want to process large query results without loading everything into memory.

When you need to perform operations on each row individually.

When you want to fetch rows in small chunks for better control.

When you want to loop through query results inside a stored procedure or function.

When you want to avoid locking the whole table by processing rows gradually.

Syntax

PostgreSQL

DECLARE cursor_name CURSOR FOR select_statement;

OPEN cursor_name;

FETCH cursor_name INTO target_variable;

CLOSE cursor_name;

You declare a cursor with a name and a SELECT query.

Use OPEN to start the cursor, FETCH to get rows one by one, and CLOSE when done.

Examples

This declares a cursor named emp_cursor to select employee IDs and names.

PostgreSQL

DECLARE emp_cursor CURSOR FOR SELECT id, name FROM employees;

This opens the cursor and fetches the first row into variables emp_id and emp_name.

PostgreSQL

OPEN emp_cursor;
FETCH emp_cursor INTO emp_id, emp_name;

This closes the cursor to release resources.

PostgreSQL

CLOSE emp_cursor;

Sample Program

This anonymous code block declares a cursor to select employee IDs and names ordered by ID. It opens the cursor, fetches each row one by one, prints the employee details, and closes the cursor.

PostgreSQL

DO $$
DECLARE
  emp_cursor CURSOR FOR SELECT id, name FROM employees ORDER BY id;
  emp_id INT;
  emp_name TEXT;
BEGIN
  OPEN emp_cursor;
  LOOP
    FETCH emp_cursor INTO emp_id, emp_name;
    EXIT WHEN NOT FOUND;
    RAISE NOTICE 'Employee ID: %, Name: %', emp_id, emp_name;
  END LOOP;
  CLOSE emp_cursor;
END $$;

Important Notes

Cursors are useful for row-by-row processing but can be slower than set-based queries.

Always close cursors to free resources.

Use EXIT WHEN NOT FOUND; to stop fetching when no more rows are available.

Summary

Cursors let you handle query results one row at a time.

Declare, open, fetch rows, and close the cursor in that order.

Use cursors when you need step-by-step control over data processing.