PostgreSQL Warning: Dynamic Result Sets Returned (SQLSTATE 0100C)

PostgreSQL raises SQLSTATE 0100C (dynamic_result_sets_returned) as a warning when a stored procedure produces more result sets than the calling context declared it would handle. The full warning typically appears as:

WARNING:  0100C: dynamic result sets returned

This is a class 01 warning — not an error — meaning the statement completes successfully and no transaction rollback occurs.

What This Error Means

SQLSTATE class 01 covers warnings in the SQL standard. The specific condition dynamic_result_sets_returned (SQLSTATE 0100C) originates from the SQL/PSM (Persistent Stored Modules) standard and is raised when a procedure generates result sets dynamically at runtime that exceed the number the invoking context anticipated or declared.

In PostgreSQL, procedures (created with CREATE PROCEDURE, introduced in PostgreSQL 11) can return results by leaving cursors open. When a CALL statement invokes a procedure that opens more cursors than the caller provided bind variables for, the server issues this warning to signal that not all result sets can be consumed through the declared cursor slots.

The warning is informational: the procedure ran to completion. However, the caller may not be able to access all returned data because it did not allocate enough cursor handles to receive every result set the procedure produced.

Common Causes

1. Procedure opens more cursors than the CALL statement declares. A CALL can bind output cursor variables, but if the procedure conditionally opens additional cursors based on runtime logic, the number of open cursors at procedure exit can exceed what the caller prepared for.

2. JDBC or ODBC driver cursor mismatch. Some drivers that support CALL with result-set retrieval allocate a fixed number of result-set slots. If the procedure produces more result sets than the driver's registered output parameters, the driver surfaces this warning from the server.

3. Migrated SQL Server or Oracle stored procedures. Developers porting procedures from SQL Server (which freely returns multiple result sets) or Oracle (ref cursor patterns) may write PostgreSQL procedures that open several cursors. If the calling code was not updated to match, the mismatch triggers this warning.

4. Dynamic SQL inside a procedure. A procedure that uses EXECUTE or OPEN cursor FOR EXECUTE with runtime-determined queries may open a varying number of cursors depending on input, making it hard for callers to predict and declare the exact count.

How to Fix dynamic_result_sets_returned

1. Align cursor output parameters with the CALL statement. Ensure the calling CALL statement binds a refcursor variable for each cursor the procedure opens.

   -- Procedure that opens two cursors
   CREATE OR REPLACE PROCEDURE get_data(
       INOUT cur1 refcursor,
       INOUT cur2 refcursor
   )
   LANGUAGE plpgsql AS $$
   BEGIN
       OPEN cur1 FOR SELECT * FROM orders;
       OPEN cur2 FOR SELECT * FROM customers;
   END;
   $$;
   
   -- Caller that correctly binds both cursors
   BEGIN;
   CALL get_data('cur1', 'cur2');
   FETCH ALL FROM cur1;
   FETCH ALL FROM cur2;
   COMMIT;
   

2. Refactor the procedure to return a fixed, known number of cursors. Avoid patterns where the number of opened cursors depends on runtime conditions. If varying output is required, consider returning a single cursor whose rows encode the result-set identifier as a column.

3. Use a table-valued function instead of a procedure. If a procedure is being used solely to return query results, a RETURNS TABLE or RETURNS SETOF function is usually cleaner and avoids cursor management entirely:

   CREATE OR REPLACE FUNCTION get_orders()
   RETURNS TABLE(order_id int, total numeric)
   LANGUAGE sql AS $$
       SELECT order_id, total FROM orders;
   $$;
   
   SELECT * FROM get_orders();
   

4. Update driver-level cursor registration. If the warning surfaces through a JDBC or ODBC driver, verify that the CallableStatement (JDBC) or SQLBindParameter (ODBC) registrations match the actual number of refcursor output parameters the procedure declares.

Additional Information

• SQLSTATE 0100C is defined in the ISO SQL standard (SQL/PSM) and implemented in PostgreSQL for standard compliance. It is rare in typical PostgreSQL applications because most PostgreSQL code uses functions rather than procedures with multiple refcursors.
• CREATE PROCEDURE and the ability to return result sets via refcursors was introduced in PostgreSQL 11. This warning is therefore only relevant in PostgreSQL 11 and later.
• Related SQLSTATE codes in class 01 (warnings): 01000 (WARNING), 01003 (NULL_VALUE_ELIMINATED_IN_SET_FUNCTION), 01004 (STRING_DATA_RIGHT_TRUNCATION), 01006 (PRIVILEGE_NOT_REVOKED), 01007 (PRIVILEGE_NOT_GRANTED), 0100E (DEPRECATED_FEATURE).
• PostgreSQL surfaces warnings via the server log and returns them to the client as notice/warning messages. Application-level code using libpq, JDBC (org.postgresql), or psycopg2 can inspect these through the connection's notice handler without interrupting the transaction.
• Because this is a warning (not an error), ON_ERROR_STOP in psql will not stop on it, and it will not trigger an exception in PL/pgSQL without an explicit RAISE check.

Frequently Asked Questions

Does SQLSTATE 0100C roll back my transaction? No. Class 01 codes are warnings. The procedure completes successfully, the transaction remains open, and no rollback occurs. You may simply be missing some result sets that were produced but could not be received.

Why do I see this warning only sometimes, not on every CALL? The warning is triggered by a mismatch between the number of cursors opened at runtime and the number the caller declared. If the procedure contains conditional logic that opens extra cursors only under certain inputs, the warning will appear only when that branch executes.

Can I suppress this warning? You can set client_min_messages = warning (the default) or higher (error) in the session to control which messages are sent to the client. Setting it to error would suppress warnings but is too aggressive for most use cases. The proper fix is to resolve the cursor count mismatch rather than suppressing the diagnostic.

My application uses functions, not procedures. Can I still see this warning? No. Functions in PostgreSQL (CREATE FUNCTION) do not use the same result-set return mechanism as procedures. Only CALL against a CREATE PROCEDURE that opens refcursors can trigger 0100C. If you are not using procedures with refcursor output parameters, this warning will not appear.