PL/pgSQL Explicit Cursor

Master PL/pgSQL explicit cursors — DECLARE/OPEN/FETCH/CLOSE lifecycle, the FOUND variable for end-of-rows detection, and the critical clarification that PostgreSQL does NOT have Oracle's %FOUND/%NOTFOUND/%ISOPEN/%ROWCOUNT cursor attributes — use FOUND and GET DIAGNOSTICS instead.

An explicit cursor is one you declare yourself — naming the cursor, defining its query, and managing its lifecycle (OPEN, FETCH, CLOSE) by hand. Compare to implicit cursors, which PostgreSQL creates and manages automatically when you write a SELECT INTO or FOR rec IN SELECT ... LOOP.

You give up convenience and gain control. Explicit cursors are useful when:

• You need to OPEN a cursor in one place, FETCH in another, CLOSE in a third
• You want to fetch a few rows, do something, then maybe fetch more (or stop)
• You want to pass a cursor reference between functions (covered on the next page — Refcursor)
• You need scrollable cursors (FETCH PRIOR / FETCH ABSOLUTE), only available on explicit cursors

For most cases, the implicit-cursor FOR rec IN SELECT ... LOOP is cleaner. Reach for explicit cursors when implicit ones can't express what you need.

Four steps, in order:

Declaration:

Open the cursor:

Fetch a row into variables:

Close when done:

If you've used Oracle PL/SQL, you might expect cursor attributes like cursor_name%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT. PostgreSQL does NOT support those. They're Oracle-specific syntax.

The key one to know: after a FETCH, the global FOUND boolean is true if a row was retrieved, false otherwise. The standard idiom is:

LOOP
    FETCH cursor_name INTO record_variable;
    EXIT WHEN NOT FOUND;
    -- process record_variable
END LOOP;

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price        NUMERIC
);

INSERT INTO products (product_name, price) VALUES
    ('Laptop',              1200.00),
    ('Smartphone',           800.00),
    ('Tablet',               500.00),
    ('Headphones',           100.00),
    ('Keyboard',              50.00),
    ('Mouse',                 20.00),
    ('Monitor',              250.00),
    ('Printer',              150.00),
    ('External Hard Drive',   80.00),
    ('Wireless Router',       70.00);

SELECT * FROM products;

Walk through every product, printing name and price. The classic OPEN / FETCH / EXIT WHEN NOT FOUND / CLOSE pattern:

DO $$
DECLARE
    product_cursor CURSOR FOR
        SELECT product_name, price FROM products ORDER BY product_id;

    v_name  VARCHAR(100);
    v_price NUMERIC;
BEGIN
    OPEN product_cursor;

    LOOP
        FETCH product_cursor INTO v_name, v_price;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Product: %, Price: %', v_name, v_price;
    END LOOP;

    CLOSE product_cursor;
END
$$;

To count rows fetched (the Oracle %ROWCOUNT equivalent), maintain your own counter or use GET DIAGNOSTICS after individual statements:

DO $$
DECLARE
    expensive_cursor CURSOR FOR
        SELECT product_name, price
        FROM   products
        WHERE  price > 100
        ORDER  BY price DESC;

    v_name      VARCHAR(100);
    v_price     NUMERIC;
    fetched_cnt INTEGER := 0;
BEGIN
    OPEN expensive_cursor;

    LOOP
        FETCH expensive_cursor INTO v_name, v_price;
        EXIT WHEN NOT FOUND;
        fetched_cnt := fetched_cnt + 1;
        RAISE NOTICE '%. % - %', fetched_cnt, v_name, v_price;
    END LOOP;

    CLOSE expensive_cursor;

    RAISE NOTICE 'Total fetched: %', fetched_cnt;
END
$$;

One real advantage of explicit cursors: you can stop fetching early. Walk products until you find the first one over a threshold, then stop:

DO $$
DECLARE
    product_cursor CURSOR FOR
        SELECT product_name, price FROM products ORDER BY product_id;

    v_name  VARCHAR(100);
    v_price NUMERIC;
BEGIN
    OPEN product_cursor;

    LOOP
        FETCH product_cursor INTO v_name, v_price;
        EXIT WHEN NOT FOUND;

        IF v_price > 1000 THEN
            RAISE NOTICE 'First expensive product found: % at %', v_name, v_price;
            EXIT;     -- bail out — no need to fetch the rest
        END IF;
    END LOOP;

    CLOSE product_cursor;
END
$$;

The cursor stopped at the first match — only one row was actually FETCHed past the start. Plain SQL with LIMIT 1 would express this more cleanly here, but the pattern generalizes to scenarios where the stopping condition depends on cumulative state across rows.

PostgreSQL's FETCH statement can also take a count, returning multiple rows at once into an array of records. Useful for batch processing:

DO $$
DECLARE
    product_cursor CURSOR FOR
        SELECT product_name FROM products ORDER BY product_id;
    rec record;
BEGIN
    OPEN product_cursor;

    -- Fetch first 3 rows
    FOR i IN 1..3 LOOP
        FETCH product_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Top %: %', i, rec.product_name;
    END LOOP;

    CLOSE product_cursor;
END
$$;

Stopped after three fetches even though more rows existed — useful when you want to peek at the top N without iterating the whole result set.

By default cursors only move forward. Add SCROLL to enable backward fetching and direct positioning:

Then you can FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE n, etc. Most code doesn't need this — but when you do, it's there. Note: scrollable cursors require the underlying query plan to materialize results, so they can be more expensive than forward-only cursors.

1. Default to implicit cursors via FOR rec IN SELECT ... LOOP. Reach for explicit cursors only when implicit ones can't express what you need.
2. Always include ORDER BY in cursor queries when row order matters — without it, PostgreSQL doesn't promise an order.
3. Use the standard EXIT WHEN NOT FOUND idiom after each FETCH. Don't try to count rows up front and loop a fixed number of times.
4. Check the global FOUND variable — that's PostgreSQL's equivalent of Oracle's %FOUND/%NOTFOUND.
5. Use GET DIAGNOSTICS row_count = ROW_COUNT after a single statement to learn how many rows it affected. There's no per-cursor %ROWCOUNT.
6. Close cursors explicitly as soon as you're done — releases resources sooner than waiting for block exit.
7. Forget Oracle attribute syntax. cursor_name%FOUND, %ISOPEN, etc. don't exist in PostgreSQL.

• An explicit cursor is one you declare, open, fetch from, and close yourself.
• Lifecycle: DECLARE → OPEN → FETCH ... INTO in a loop, exit on NOT FOUND → CLOSE.
• Use the global FOUND boolean to detect end-of-rows. EXIT WHEN NOT FOUND; is the standard idiom.
• PostgreSQL does not have Oracle-style %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT cursor attributes. Use FOUND and GET DIAGNOSTICS instead.
• Cursors close automatically when their block exits, but explicit CLOSE releases resources earlier.
• For most use cases, the implicit-cursor FOR rec IN SELECT ... LOOP is cleaner and equally fast.