PL/pgSQL Refcursor

Master PL/pgSQL refcursors — cursor variables that point to cursors. Covers OPEN ... FOR query, OPEN ... FOR EXECUTE for dynamic SQL, returning cursors from functions to callers, and when modern RETURNS TABLE / RETURN QUERY is the simpler choice. Completely rewritten from the source which had unrelated content.

A refcursor (reference cursor) is a cursor variable — a variable that points to a cursor rather than being a cursor itself. The crucial difference from a plain explicit cursor:

Three big use cases:

• Returning a cursor from a function — caller iterates the result set themselves
• Passing a cursor to another function — the receiving function fetches from it
• Choosing the query at runtime — open different SELECTs based on input parameters

The PostgreSQL type for cursor variables is REFCURSOR — declared like any variable in the DECLARE section.

Declaring a refcursor variable:

Opening it for a specific query (note FOR takes a SELECT here):

Or for a dynamically-built SQL string with EXECUTE:

Fetching and closing work the same as with explicit cursors:

DROP TABLE IF EXISTS products;

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

INSERT INTO products (product_name, category, price) VALUES
    ('Laptop',         'Electronics', 1200.00),
    ('Smartphone',     'Electronics',  800.00),
    ('Tablet',         'Electronics',  500.00),
    ('Headphones',     'Audio',        100.00),
    ('Keyboard',       'Accessories',   50.00),
    ('Mouse',          'Accessories',   20.00),
    ('Monitor',        'Electronics',  250.00),
    ('Printer',        'Electronics',  150.00);

The simplest case: declare a refcursor, open it for a query, fetch rows, close it. Functionally similar to an explicit cursor, but the query is bound at OPEN time, not DECLARE time:

DO $$
DECLARE
    my_cursor REFCURSOR;
    rec       record;
BEGIN
    -- The query is supplied here, not on the DECLARE line
    OPEN my_cursor FOR
        SELECT product_name, price FROM products ORDER BY price DESC;

    LOOP
        FETCH my_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% - %', rec.product_name, rec.price;
    END LOOP;

    CLOSE my_cursor;
END
$$;

The same refcursor variable can be opened for different queries depending on input. This is what plain explicit cursors can't do — they're bound to one query at declaration:

DO $$
DECLARE
    my_cursor REFCURSOR;
    rec       record;
    sort_by   TEXT := 'price';   -- could come from a function parameter
BEGIN
    IF sort_by = 'price' THEN
        OPEN my_cursor FOR
            SELECT product_name, price FROM products ORDER BY price DESC LIMIT 3;
    ELSE
        OPEN my_cursor FOR
            SELECT product_name, price FROM products ORDER BY product_name LIMIT 3;
    END IF;

    LOOP
        FETCH my_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% - %', rec.product_name, rec.price;
    END LOOP;

    CLOSE my_cursor;
END
$$;

The classic pattern: a function builds and opens a cursor, then returns the refcursor handle. The caller fetches from it. This is how PostgreSQL functions traditionally returned multi-row result sets to client applications before RETURNS TABLE existed.

The function:

DROP FUNCTION IF EXISTS get_expensive_products(NUMERIC);

CREATE OR REPLACE FUNCTION get_expensive_products(min_price NUMERIC)
RETURNS REFCURSOR AS $$
DECLARE
    result_cursor REFCURSOR := 'expensive_products_cur';   -- name is optional
BEGIN
    OPEN result_cursor FOR
        SELECT product_name, price FROM products
        WHERE  price >= min_price
        ORDER  BY price DESC;

    RETURN result_cursor;
END;
$$ LANGUAGE plpgsql;

The caller fetches from the returned cursor. Refcursors must be used inside a transaction — wrap the calls in BEGIN ... COMMIT or run them in a transaction block:

BEGIN;
SELECT get_expensive_products(500);
FETCH ALL FROM expensive_products_cur;
COMMIT;

Refcursors paired with OPEN ... FOR EXECUTE let you build the query string dynamically — useful for reporting tools where the table or columns aren't known until runtime. Use USING for parameter values to avoid SQL injection:

DO $$
DECLARE
    my_cursor      REFCURSOR;
    rec            record;
    target_category TEXT := 'Electronics';
    sql_text       TEXT;
BEGIN
    sql_text := 'SELECT product_name, price FROM products
                 WHERE category = $1 ORDER BY price DESC';

    OPEN my_cursor FOR EXECUTE sql_text USING target_category;

    LOOP
        FETCH my_cursor INTO rec;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE '% - %', rec.product_name, rec.price;
    END LOOP;

    CLOSE my_cursor;
END
$$;

1. Use RETURNS TABLE / RETURN QUERY as the default for functions that produce row results — simpler than refcursors and doesn't require the caller to manage transaction boundaries.
2. Reach for refcursors when you need dynamic queries, when callers explicitly want a cursor handle to fetch from at their own pace, or when integrating with code that expects them.
3. Wrap caller-side fetching in a transaction. BEGIN ... CALL/SELECT function ... FETCH ... COMMIT. Outside a transaction, the cursor disappears when the function call's auto-commit ends.
4. Use USING for parameters in OPEN ... FOR EXECUTE — never concatenate user input into the SQL text.
5. Optionally name your refcursor by assigning a string to it before OPEN: cur := 'my_cursor_name'; OPEN cur FOR ...; — gives the caller a known handle.
6. CLOSE explicitly when done. The cursor will close at transaction end anyway, but clean code closes its own resources.

• A refcursor is a cursor variable — a value that points to a cursor, not a cursor bound to one query.
• Declare with name REFCURSOR;, open with OPEN name FOR query; (or FOR EXECUTE sql_text USING values), fetch and close like any cursor.
• Refcursors enable two things plain explicit cursors can't: returning a cursor from a function, and opening a different query depending on runtime conditions.
• Pair refcursor-returning functions with BEGIN ... COMMIT on the caller side — cursors live inside a transaction.
• Use OPEN ... FOR EXECUTE ... USING for safe dynamic SQL.
• For the common "function returning rows" case, modern RETURNS TABLE / RETURN QUERY is simpler. Refcursors shine for dynamic SQL and explicit cursor handling.