PostgreSQL No Additional Dynamic Result Sets Returned (SQLSTATE 02001)

PostgreSQL raises SQLSTATE 02001 with the condition name no_additional_dynamic_result_sets_returned when a client or stored procedure attempts to retrieve another result set from a dynamic SQL call, but all result sets have already been consumed. This is a warning-class condition in PostgreSQL's SQLSTATE hierarchy, not a fatal error.

What This Error Means

SQLSTATE codes beginning with 02 belong to the "No Data" warning class in the SQL standard. Within this class, 02001 specifically covers the case where a dynamic SQL execution was expected to produce multiple result sets but no further result sets remain to be fetched.

In the SQL standard and in PostgreSQL's PL/pgSQL environment, certain dynamic SQL constructs — particularly those involving EXECUTE statements or calls to functions/procedures that return multiple result sets — can produce more than one result set in sequence. The client or calling code must iterate through each result set in order. Once all result sets have been consumed, any additional attempt to advance to the next result set triggers this condition.

This condition is distinct from 02000 (no_data_found), which signals that a query returned no rows at all. With 02001, the query executed successfully and may well have returned rows — the issue is that the caller asked for a next result set when none remains.

Common Causes

1. Iterating past the last result set in a dynamic SQL call. Code that loops over result sets from a stored procedure or dynamic query advances the cursor past the final set without checking whether another set actually exists.

2. Mismatch between expected and actual result set count. A stored procedure is expected to return multiple result sets (e.g., based on its previous behavior), but a code change reduced the number of result sets it produces. The calling code still attempts to fetch one more than is available.

3. Driver or middleware auto-iteration. Some JDBC drivers, ODBC drivers, or middleware layers automatically attempt to advance to the next result set after consuming each one. In rare configurations this can trigger 02001 at the driver level when the procedure returns fewer sets than the driver iterates.

4. PL/pgSQL handler logic. Custom exception or result-set handler blocks in PL/pgSQL that unconditionally call FETCH NEXT FROM a cursor derived from a dynamic query, without checking FOUND or row count first.

How to Fix no_additional_dynamic_result_sets_returned

1. Check for remaining result sets before advancing. In PL/pgSQL, test the FOUND variable or row count after each fetch before attempting to move to the next result set:

   DO $$
   DECLARE
     rec record;
   BEGIN
     FOR rec IN EXECUTE 'SELECT id, name FROM users' LOOP
       -- process rec
     END LOOP;
     -- Do not attempt another FETCH after the loop ends
   END;
   $$;
   

2. Use GET DIAGNOSTICS to verify row counts before iterating. After a dynamic SQL execution, check how many rows or result sets were produced:

   DO $$
   DECLARE
     affected integer;
   BEGIN
     EXECUTE 'UPDATE orders SET status = $1 WHERE created_at < $2'
       USING 'archived', now() - interval '1 year';
     GET DIAGNOSTICS affected = ROW_COUNT;
     RAISE NOTICE 'Rows updated: %', affected;
   END;
   $$;
   

3. Handle the condition explicitly in exception blocks. If your code must attempt an advance regardless, catch no_additional_dynamic_result_sets_returned and treat it as a normal end-of-data condition rather than a failure:

   DO $$
   BEGIN
     -- ... dynamic SQL logic ...
   EXCEPTION
     WHEN no_additional_dynamic_result_sets_returned THEN
       -- All result sets consumed; exit cleanly
       NULL;
   END;
   $$;
   

4. Audit procedure return paths. If a stored procedure conditionally returns result sets (e.g., only returns a second result set when certain data exists), ensure calling code accounts for the case where fewer sets are returned. Do not assume a fixed number of result sets from a procedure that has branching logic.

5. Update driver iteration logic. If the condition surfaces through a JDBC or ODBC driver, review the loop that calls getMoreResults() (JDBC) or SQLMoreResults() (ODBC) and add a check on the return value before each call to avoid requesting a result set that does not exist.

Additional Information

• SQLSTATE 02001 belongs to class 02 ("No Data"), which the SQL standard designates as a warning rather than an error. PostgreSQL propagates it as a warning condition, meaning execution continues unless the application explicitly handles or promotes it.
• The closely related condition 02000 (no_data_found) is far more commonly encountered in practice. 02001 is specific to multi-result-set dynamic SQL scenarios, which are less common in typical PostgreSQL usage.
• PostgreSQL's support for returning multiple result sets from a single call is more limited than some other databases (such as SQL Server or MySQL). The pattern is primarily relevant when using PERFORM or EXECUTE inside PL/pgSQL, or when using SQL/CLI-based interfaces.
• This condition is defined in the SQL standard (ISO/IEC 9075) and has been part of PostgreSQL's SQLSTATE table since early versions. Its practical relevance increased with improved stored procedure support in PostgreSQL 11 (which added CREATE PROCEDURE with transaction control).
• Related SQLSTATE codes: 02000 (no_data_found), P0002 (no_data_found in PL/pgSQL raise context).

Frequently Asked Questions

Is SQLSTATE 02001 a fatal error that aborts my transaction? No. Class 02 codes are warning-level conditions in PostgreSQL. The transaction remains open and the connection is not terminated. Your application can catch the condition and continue, or ignore it if the logic treats all result sets as optional.

Why does this SQLSTATE appear so rarely in PostgreSQL compared to other databases? PostgreSQL's procedural language and client protocol are primarily oriented around single-result-set interactions. Multi-result-set patterns are much more common in SQL Server (via SELECT statements without cursors in procedures) and MySQL. In PostgreSQL, stored procedures typically use OUT parameters, SETOF return types, or explicit cursors rather than implicit multiple result sets, so the scenario triggering 02001 arises infrequently.

How do I reproduce this condition to test my error handler? You can raise it explicitly in PL/pgSQL using RAISE with the SQLSTATE:

DO $$
BEGIN
  RAISE SQLSTATE '02001';
END;
$$;

This lets you verify that your exception handler catches and processes the condition correctly without needing to construct a real multi-result-set scenario.

Does no_additional_dynamic_result_sets_returned indicate a bug in my procedure or a normal end-of-data condition? It depends on context. If your code explicitly loops over result sets and checks for this condition to stop iterating, it is functioning as a normal sentinel. If it appears unexpectedly — particularly in production logs — it typically indicates that calling code assumes more result sets than the procedure actually returns, which is a logic mismatch worth investigating.