Cursors

Chapter 30: Cursors

Working with Result Sets Row by Row

---

30.1 Understanding Cursors

Cursors provide a way to process query results one row at a time, which is essential for large result sets or when you need row-level control.

                    Cursor Concept
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      How Cursors Work                                │
    │                                                                      │
    │   Without Cursor:                                                     │
    │   ───────────────                                                    │
    │   SELECT * FROM large_table;                                        │
    │   → Loads entire result set into memory                             │
    │   → May cause OOM for large datasets                                 │
    │                                                                      │
    │   With Cursor:                                                       │
    │   ────────────                                                      │
    │   OPEN cursor → FETCH row → PROCESS → FETCH next → ... → CLOSE     │
    │   → Processes one row at a time                                     │
    │   → Memory efficient                                                │
    │   → Can stop early without processing all                           │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

Basic Cursor Usage

-- Create a cursor in a function
CREATE OR REPLACE FUNCTION process_users_cursor()
RETURNS INTEGER AS $$
DECLARE
    v_user_record RECORD;
    v_count INTEGER := 0;
    v_cursor CURSOR FOR
        SELECT user_id, email, status FROM users;
BEGIN
    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_user_record;
        EXIT WHEN NOT FOUND;

        -- Process each user
        v_count := v_count + 1;
    END LOOP;

    CLOSE v_cursor;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Test it
SELECT process_users_cursor();

---

30.2 Cursor Parameters

-- Cursor with parameters
CREATE OR REPLACE FUNCTION find_users_by_status(
    p_status VARCHAR
) RETURNS INTEGER AS $$
DECLARE
    v_user RECORD;
    v_count INTEGER := 0;
    v_cursor CURSOR (status VARCHAR) FOR
        SELECT user_id, email FROM users WHERE status = status;
BEGIN
    -- Open cursor with parameter
    OPEN v_cursor(p_status);

    LOOP
        FETCH v_cursor INTO v_user;
        EXIT WHEN NOT FOUND;

        v_count := v_count + 1;
    END LOOP;

    CLOSE v_cursor;

    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Call with parameter
SELECT find_users_by_status('active');
SELECT find_users_by_status('inactive');

---

30.3 Cursor Types

PostgreSQL supports different cursor types for different use cases.

-- SCROLL cursor (can move backwards)
CREATE OR REPLACE FUNCTION process_with_scroll()
RETURNS VOID AS $$
DECLARE
    v_record RECORD;
    v_cursor CURSOR WITH SCROLL FOR
        SELECT * FROM users ORDER BY user_id;
BEGIN
    OPEN v_cursor;

    -- Move forward
    FORWARD 5 IN v_cursor;
    FETCH v_cursor INTO v_record;
    RAISE NOTICE 'Forward: %', v_record.username;

    -- Move backward
    BACKWARD 2 IN v_cursor;
    FETCH v_cursor INTO v_record;
    RAISE NOTICE 'Backward: %', v_record.username;

    CLOSE v_cursor;
END;
$$ LANGUAGE plpgsql;

-- NO SCROLL cursor (forward only, more efficient)
CREATE OR REPLACE FUNCTION process_forward_only()
RETURNS VOID AS $$
DECLARE
    v_record RECORD;
    v_cursor CURSOR NO SCROLL FOR
        SELECT * FROM users ORDER BY user_id;
BEGIN
    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_record;
        EXIT WHEN NOT FOUND;

        -- Process record
        RAISE NOTICE 'Processing: %', v_record.username;
    END LOOP;

    CLOSE v_cursor;
END;
$$ LANGUAGE plpgsql;

---

30.4 Using Cursors in Transactions

Cursors must be used within transactions.

-- Cursor within a transaction block
CREATE OR REPLACE FUNCTION process_with_transaction()
RETURNS INTEGER AS $$
DECLARE
    v_order RECORD;
    v_count INTEGER := 0;
    v_cursor CURSOR FOR
        SELECT order_id, total FROM orders WHERE status = 'pending';
BEGIN
    -- Start transaction
    BEGIN

    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_order;
        EXIT WHEN NOT FOUND;

        -- Process each order
        UPDATE orders
        SET status = 'processing'
        WHERE order_id = v_order.order_id;

        v_count := v_count + 1;
    END LOOP;

    CLOSE v_cursor;

    -- Commit transaction
    COMMIT;

    RETURN v_count;

    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;
END;
$$ LANGUAGE plpgsql;

-- Holdable cursor (remains open after transaction)
CREATE OR REPLACE FUNCTION create_holdable_cursor()
RETURNS REFCURSOR AS $$
DECLARE
    v_cursor REFCURSOR;
BEGIN
    BEGIN

    OPEN v_cursor SCROLL FOR
        SELECT * FROM users ORDER BY user_id;

    -- Cursor remains open after COMMIT
    COMMIT;

    RETURN v_cursor;

    END;
END;
$$ LANGUAGE plpgsql;

---

30.5 Practical Cursor Examples

-- Batch processing with cursors
CREATE OR REPLACE PROCEDURE batch_update_orders(
    p_batch_size INTEGER DEFAULT 1000
) LANGUAGE plpgsql AS $$
DECLARE
    v_order RECORD;
    v_count INTEGER := 0;
    v_cursor CURSOR FOR
        SELECT order_id, user_id, total
        FROM orders
        WHERE status = 'pending'
        ORDER BY order_id
        LIMIT p_batch_size;
BEGIN
    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_order;
        EXIT WHEN NOT FOUND;

        -- Business logic for each order
        IF v_order.total > 1000 THEN
            UPDATE orders
            SET priority = 'high'
            WHERE order_id = v_order.order_id;
        END IF;

        v_count := v_count + 1;
    END LOOP;

    CLOSE v_cursor;

    COMMIT;

    RAISE NOTICE 'Processed % orders', v_count;
END;
$$;

-- Cursor for complex processing
CREATE OR REPLACE FUNCTION generate_report()
RETURNS TABLE(
    customer_name TEXT,
    order_count INTEGER,
    total_spent NUMERIC
) AS $$
DECLARE
    v_customer RECORD;
    v_order RECORD;
    v_cursor CURSOR FOR
        SELECT customer_id, customer_name FROM customers;
BEGIN
    FOR v_customer IN v_cursor LOOP
        -- For each customer, get order summary
        SELECT
            COUNT(*),
            SUM(total)
        INTO v_order.order_count, v_order.total_spent
        FROM orders
        WHERE customer_id = v_customer.customer_id;

        RETURN NEXT (
            v_customer.customer_name,
            COALESCE(v_order.order_count, 0),
            COALESCE(v_order.total_spent, 0)
        );
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

---

30.6 Cursor Attributes

PostgreSQL provides cursor attributes to get status information.

-- Using cursor attributes
CREATE OR REPLACE FUNCTION analyze_cursor()
RETURNS VOID AS $$
DECLARE
    v_user RECORD;
    v_cursor CURSOR FOR SELECT * FROM users;
    v_found BOOLEAN;
BEGIN
    OPEN v_cursor;

    -- Check if cursor has rows
    v_found := FOUND;
    RAISE NOTICE 'Cursor initially found: %', v_found;

    -- Fetch first row
    FETCH v_cursor INTO v_user;

    -- Use cursor attributes
    RAISE NOTICE 'Current row: %', v_user.username;
    RAISE NOTICE 'More rows available: %',
        (v_cursor % ISOPEN);

    -- Use FOUND after fetch
    IF FOUND THEN
        RAISE NOTICE 'Fetched successfully';
    END IF;

    CLOSE v_cursor;

    -- Use AFTER close
    IF NOT FOUND THEN
        RAISE NOTICE 'All rows processed';
    END IF;
END;
$$ LANGUAGE plpgsql;

---

30.7 Performance Considerations

    Cursor Best Practices
    ========================================================================

    ✓ When to Use Cursors:
    ─────────────────────
    • Processing large result sets
    • Row-by-row processing with business logic
    • Need to stop early
    • Batch operations with custom logic

    ✗ When NOT to Use Cursors:
    ─────────────────────────
    • Simple SELECT that can return all rows
    • Aggregation queries (use GROUP BY)
    • Small result sets

    Performance Tips:
    ─────────────────
    ✓ Use WITH HOLD for cursors that must persist
    ✓ Close cursors when done
    ✓ Consider using FOR loops (auto-management)
    ✓ Set fetch batch size for optimal performance
*/

---

30.8 Cursor vs FOR Loop

-- Explicit cursor (detailed control)
CREATE OR REPLACE FUNCTION explicit_cursor_method()
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER := 0;
    v_user RECORD;
    v_cursor CURSOR FOR SELECT * FROM users;
BEGIN
    OPEN v_cursor;
    LOOP
        FETCH v_cursor INTO v_user;
        EXIT WHEN NOT FOUND;
        v_count := v_count + 1;
    END LOOP;
    CLOSE v_cursor;
    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

-- Implicit cursor (FOR loop - recommended)
CREATE OR REPLACE FUNCTION for_loop_method()
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER := 0;
BEGIN
    FOR v_user IN SELECT * FROM users LOOP
        v_count := v_count + 1;
    END LOOP;
    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

---

Summary

Feature	Description
Cursor	Database object for row-by-row processing
FETCH	Retrieve single row
OPEN/CLOSE	Manage cursor lifecycle
%ISOPEN	Check if cursor is open
%FOUND	Check if row was fetched

---

Next: Chapter 31: Triggers - Introduction