PostgreSQL - Cursor

Cursors in PostgreSQL are used to process query results row by row, which is useful when you can’t—or don’t want to—handle the entire result set at once.

---

💡 Why Use a Cursor?

1. Row-by-row processing: Ideal when each row needs special handling in a loop.

2. Memory efficiency: Prevents loading a huge result set into memory all at once.

3. Complex logic: Enables conditional processing or multi-step logic across rows.

4. Procedural operations: Often used in stored procedures or PL/pgSQL blocks.

---

🧩 When NOT to Use a Cursor

• When you can do everything in pure SQL (faster and more efficient).

• When the result set is small and can be handled easily in memory.

---

🛠️ How to Use a Cursor in PostgreSQL (PL/pgSQL)

Here's a full example:

1. Create a table for the example:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price NUMERIC
);

INSERT INTO products (name, price) VALUES
('Apple', 1.00),
('Banana', 0.50),
('Cherry', 2.00);

 

2. Use a cursor inside a PL/pgSQL DO block:

DO $$
DECLARE
    prod_rec RECORD;
    prod_cursor CURSOR FOR SELECT * FROM products;
BEGIN
    OPEN prod_cursor;

    LOOP
        FETCH prod_cursor INTO prod_rec;
        EXIT WHEN NOT FOUND;

        -- Example processing: Print the name and price
        RAISE NOTICE 'Product: %, Price: %', prod_rec.name, prod_rec.price;
    END LOOP;

    CLOSE prod_cursor;
END $$;

 

🧭 Cursor Control Commands

Command	Description
OPEN cursor	Opens the cursor
FETCH INTO	Retrieves the next row into a variable
MOVE	Moves the cursor without retrieving data
CLOSE cursor	Closes the cursor
DECLARE	Defines the cursor query