Performing operations on cursors in PostgreSQL

Introduction

Cursors let you handle query results one row at a time. This helps when you want to work with 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 row-by-row operations like calculations or updates.

When you want to fetch a few rows at a time for better control.

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

When you want to pause and resume reading data from a query.

Syntax

PostgreSQL

DECLARE cursor_name CURSOR FOR select_statement;
OPEN cursor_name;
FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM cursor_name;
CLOSE cursor_name;

DECLARE creates the cursor with a query.

OPEN starts the cursor so you can fetch rows.

Examples

This example declares a cursor for employee data, opens it, fetches the first row, then closes it.

PostgreSQL

DECLARE mycursor CURSOR FOR SELECT id, name FROM employees;
OPEN mycursor;
FETCH NEXT FROM mycursor;
CLOSE mycursor;

This fetches the third row from the products ordered by price.

PostgreSQL

DECLARE mycursor CURSOR FOR SELECT * FROM products ORDER BY price;
OPEN mycursor;
FETCH ABSOLUTE 3 FROM mycursor;
CLOSE mycursor;

Sample Program

This block opens a cursor on the employees table, fetches each row one by one, and prints the employee's ID and name.

PostgreSQL

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

Important Notes

Always close your cursor after use to free resources.

FETCH moves the cursor and retrieves the current row.

You can fetch rows in different directions using options like NEXT, PRIOR, ABSOLUTE, and RELATIVE.

Summary

Cursors let you handle query results row by row.

Use DECLARE, OPEN, FETCH, and CLOSE to work with cursors.

Cursors are useful for processing large data or complex row-wise logic.