PL/pgSQL Cursor With Parameters

Master PL/pgSQL parameterized cursors — declaring CURSOR (param type, ...) FOR query, opening with different argument values, and the cleaner FOR rec IN cursor_name(args) LOOP syntax. Key distinction: PostgreSQL uses FOR not IS (the latter is Oracle).

An explicit cursor with parameters lets you declare the cursor once and open it multiple times with different argument values, getting different result sets each time. The query body stays the same; the placeholders take whatever values you pass at OPEN time.

Compare two approaches:

Declare a parameterized cursor:

Open it, supplying values for the parameters:

Three things to know:

• Each parameter is declared as name type — exactly like function parameters.
• Parameters are referenced inside the cursor query just like ordinary expressions: WHERE col = parameter_name.
• Parameters are scoped to the cursor query — you can't reference them outside.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    product_name VARCHAR(100),
    list_price   NUMERIC
);

INSERT INTO products (product_id, product_name, list_price) VALUES
    (1, 'iPhone 14',             999),
    (2, 'Samsung Galaxy S23',    899),
    (3, 'Sony PlayStation 5',    499),
    (4, 'MacBook Pro',          1499),
    (5, 'Dell XPS 15',          1299),
    (6, 'Bose QuietComfort 45',  329),
    (7, 'Nintendo Switch',       299),
    (8, 'GoPro Hero 12',         499),
    (9, 'Canon EOS R5',         3899);

Declare a cursor that takes low and high price bounds. Open it twice with different argument values to fetch two different price ranges:

DO $$
DECLARE
    -- Parameterized cursor: takes a price range
    products_in_range CURSOR (low NUMERIC, high NUMERIC) FOR
        SELECT product_name, list_price
        FROM   products
        WHERE  list_price BETWEEN low AND high
        ORDER  BY list_price;

    rec record;
BEGIN
    -- First open: mass-market range
    RAISE NOTICE 'Mass products (100 - 500):';
    OPEN products_in_range(100, 500);
    LOOP
        FETCH products_in_range INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '  %: %', rec.product_name, rec.list_price;
    END LOOP;
    CLOSE products_in_range;

    -- Second open: same cursor, different arguments
    RAISE NOTICE 'Luxury products (800 - 1000):';
    OPEN products_in_range(800, 1000);
    LOOP
        FETCH products_in_range INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '  %: %', rec.product_name, rec.list_price;
    END LOOP;
    CLOSE products_in_range;
END
$$;

Same cursor declaration; two opens with different arguments; two completely different result sets. The query body never had to be repeated.

The verbose OPEN / FETCH / EXIT WHEN NOT FOUND / CLOSE dance can also be written as a FOR loop — passing the parameter values right at the loop. PostgreSQL handles the cursor lifecycle for you, just like with implicit cursors:

DO $$
DECLARE
    products_in_range CURSOR (low NUMERIC, high NUMERIC) FOR
        SELECT product_name, list_price
        FROM   products
        WHERE  list_price BETWEEN low AND high
        ORDER  BY list_price;

    rec record;
BEGIN
    -- FOR loop opens, fetches, and closes the cursor automatically
    FOR rec IN products_in_range(100, 500) LOOP
        RAISE NOTICE '  %: %', rec.product_name, rec.list_price;
    END LOOP;
END
$$;

Half the lines, same result. FOR rec IN cursor_name(args) LOOP is the idiomatic shortcut.

Parameterized cursors really shine when the same cursor is opened many times in different contexts. For a one-shot query, plain FOR rec IN SELECT ... LOOP with values inlined is simpler and equally fast — the planner caches plans either way.

1. Use FOR rec IN cursor_name(args) LOOP as the default — much cleaner than the explicit OPEN/FETCH/CLOSE form.
2. Reach for parameterized cursors when you'd otherwise repeat the same query body with different argument values. One declaration, many opens.
3. Don't reach for them for one-shot queries — plain inlined values are simpler.
4. Add an ORDER BY to the cursor query if you care about the row order — without one, PostgreSQL doesn't promise a specific order.
5. Don't use IS after the cursor name — that's Oracle syntax. PostgreSQL uses FOR.
6. Parameters take any expression type — numbers, strings, dates, even arrays. Match types with the WHERE clause they'll appear in.

• A parameterized cursor declares placeholders that get values at OPEN time — the same query body is reusable with different inputs.
• Syntax: cursor_name CURSOR (param1 type, param2 type) FOR query; — note the FOR, not IS (which is Oracle).
• Open with values: OPEN cursor_name(value1, value2);.
• FOR rec IN cursor_name(args) LOOP ... END LOOP is the cleanest way to iterate — PostgreSQL handles the cursor lifecycle.
• Best when the same query body is opened multiple times with different arguments. For single-use queries, inline values are simpler.