PostgreSQL 0100C dynamic_result_sets_returned warning explained and fixed

What is the dynamic_result_sets_returned warning 0100C?

The SQLSTATE 0100C warning tells you that a statement created one or more dynamic result sets that remain open.

In PostgreSQL this usually means a PL/pgSQL function or CALL statement opened refcursors.

Because the server cannot predict whether the client intends to fetch those cursors, it emits the warning so client libraries can choose to read or ignore the extra result sets.

When does PostgreSQL raise dynamic_result_sets_returned?

The server raises 0100C after executing a function, procedure, or anonymous code block that returns refcursor values or explicitly OPENs cursors WITH HOLD. Each cursor becomes a detached result set.

If at least one cursor is left open, the warning fires.

Interactive tools like psql usually ignore the code, but some ORMs convert any SQLSTATE into an exception, which can halt your application unexpectedly.

Why should you fix or silence this warning?

Open cursors consume memory and transaction resources. Repeated warnings clutter logs and confuse monitoring systems that treat nonzero SQLSTATE values as failures. Clearing the warning ensures predictable behaviour across drivers.

Production code that unintentionally returns cursors often hides deeper logic bugs.

Fixing the routine safeguards performance and clarity.

What Causes This Error?

Returning SETOF refcursor from a PL/pgSQL function without subsequently closing the cursors triggers 0100C.

Calling a stored procedure that OPENs cursors WITH HOLD but never FETCHes them also raises the warning.

Client drivers that end a session before consuming extra result sets leave the cursors dangling, prompting 0100C in the server log.

Mismatched BEGIN/COMMIT blocks where cursors are opened in one block and never closed in the same session can surface the warning.

How to Fix dynamic_result_sets_returned

Always fetch or close every refcursor that a function opens.

Use FETCH ALL IN cursor_name followed by CLOSE cursor_name before the transaction ends.

Refactor routines to return SETOF record or TABLE(...) instead of refcursor when you only need one result set. This eliminates dynamic cursors entirely.

If you genuinely need multiple result sets, ensure your client library iterates through all of them. JDBC getMoreResults() and psycopg3.nextset() handle this correctly.

Common Scenarios and Solutions

Scenario: A PL/pgSQL function RETURNs two refcursors.

Solution: After SELECT * FROM function(), immediately run FETCH ALL IN each returned cursor and then CLOSE it.

Scenario: A procedure called from an application opens a cursor for debugging but never closes it. Solution: Wrap the debug cursor in IF debug THEN OPEN ... END IF; or drop the code in production.

Scenario: A reporting tool uses CALL my_proc() but ignores additional result sets.

Solution: Modify the tool’s driver to read all result sets or change the procedure to return a composite set.

Best Practices to Avoid This Error

Prefer SETOF record and TABLE-valued functions over refcursors when only one result set is required.

Close cursors inside the routine that opened them whenever possible.

Use PERFORM CLOSE cursor_name at the end of PL/pgSQL blocks.

Add unit tests that call every function from an automated script and assert no SQLSTATE other than 00000 is returned.

Leverage Galaxy’s AI copilot to scan your PL/pgSQL for OPEN statements lacking CLOSE, and fix them before code review.

Related Errors and Solutions

SQLSTATE 55000 attempted to open an already open cursor occurs when you OPEN a cursor twice.

Close the cursor first.

SQLSTATE 34000 invalid cursor name fires when you FETCH from a cursor that does not exist. Verify naming and scope.

SQLSTATE 01003 null_value_eliminated_in_set_function is another benign warning often logged with 0100C. Use COALESCE or FILTER to avoid it.

.