PostgreSQL 02001 Error: Causes and Solutions Complete Guide

PostgreSQL Error 02001: No Additional Dynamic Result Sets Returned

PostgreSQL error code 02001 belongs to SQLSTATE class 02 (No Data) and occurs when a caller attempts to retrieve an additional dynamic result set from a stored procedure, but no more result sets are available to return. This typically surfaces in applications that use JDBC, ODBC, or similar drivers to iterate over multiple result sets returned by a single procedure call. Understanding the root cause is essential because this error often indicates a mismatch between what the procedure promises to return and what it actually delivers at runtime.

---

Top 3 Causes

1. Procedure Returns Fewer Result Sets Than Expected

When a stored procedure is designed to return multiple cursors but conditional logic causes it to skip opening one or more of them, the calling application encounters 02001 when it tries to fetch the missing result set.

-- Problematic: second cursor may never open
CREATE OR REPLACE PROCEDURE get_data(p_active BOOLEAN)
LANGUAGE plpgsql AS $$
DECLARE
    cur1 REFCURSOR := 'users_cursor';
    cur2 REFCURSOR := 'orders_cursor';
BEGIN
    OPEN cur1 FOR SELECT user_id, username FROM users;

    -- Bug: cur2 only opens under a condition
    IF p_active THEN
        OPEN cur2 FOR SELECT order_id, total FROM orders;
    END IF;
END;
$$;

-- Fixed: always open both cursors
CREATE OR REPLACE PROCEDURE get_data_fixed(p_active BOOLEAN)
LANGUAGE plpgsql AS $$
DECLARE
    cur1 REFCURSOR := 'users_cursor';
    cur2 REFCURSOR := 'orders_cursor';
BEGIN
    OPEN cur1 FOR SELECT user_id, username FROM users;

    -- Always open cur2; returns empty set if no data matches
    OPEN cur2 FOR
        SELECT order_id, total FROM orders
        WHERE is_active = p_active;
END;
$$;

---

2. Fetching from a Cursor After It Has Been Closed

If a procedure closes a cursor explicitly and the application still attempts to fetch more data from it, PostgreSQL raises 02001 because the dynamic result set no longer exists in an accessible state.

-- Safe cursor iteration pattern inside PL/pgSQL
DO $$
DECLARE
    cur REFCURSOR;
    rec RECORD;
BEGIN
    OPEN cur FOR SELECT user_id, username FROM users WHERE active = TRUE;

    LOOP
        FETCH cur INTO rec;
        EXIT WHEN NOT FOUND;  -- Prevents over-fetching
        RAISE NOTICE 'User: %', rec.username;
    END LOOP;

    CLOSE cur;
    -- Never attempt FETCH after CLOSE
END;
$$;

---

3. Application Driver Over-iterating Result Sets

JDBC and ODBC drivers that call getMoreResults() in a loop without checking return values can request more result sets than the procedure returned, triggering 02001 server-side.

-- PostgreSQL function returning multiple ref cursors safely
CREATE OR REPLACE FUNCTION get_multi_results()
RETURNS SETOF REFCURSOR
LANGUAGE plpgsql AS $$
DECLARE
    ref1 REFCURSOR := 'set_one';
    ref2 REFCURSOR := 'set_two';
BEGIN
    OPEN ref1 FOR SELECT * FROM users LIMIT 100;
    RETURN NEXT ref1;

    OPEN ref2 FOR SELECT * FROM orders LIMIT 100;
    RETURN NEXT ref2;
END;
$$;

-- Consume in a transaction block
BEGIN;
SELECT get_multi_results();
FETCH ALL FROM set_one;
FETCH ALL FROM set_two;
COMMIT;

---

Quick Fix Solutions

• Always match declared result sets with actual opens: Never let conditional branches skip opening a cursor that callers expect.
• Use EXIT WHEN NOT FOUND: Inside PL/pgSQL loops, always check NOT FOUND after every FETCH to avoid reading past the last row.
• Validate driver logic: In JDBC, check the boolean return of getMoreResults() before calling getResultSet() again. Never assume more result sets exist without verification.

---

Prevention Tips

Consistent result set contracts: Design stored procedures so they always return the same number of result sets regardless of input parameters or internal conditions. Treat an empty result set as a valid return value rather than omitting the cursor entirely.

Regression testing for multi-result procedures: After any schema change or driver upgrade, run automated tests that specifically validate the number and order of result sets returned by each procedure. Catching count mismatches in CI/CD pipelines is far cheaper than debugging production 02001 errors.

---

Related Errors

Code	Name	Brief Description
02000	no_data_found	No rows returned by a query or FETCH
24000	invalid_cursor_state	Operation on a cursor in an invalid state
34000	invalid_cursor_name	Referenced cursor name does not exist
P0002	no_data_found (PL/pgSQL)	SELECT INTO returned no rows in a PL/pgSQL block

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.