How to fix 0100C: dynamic_result_sets_returned in PostgreSQL

PostgreSQLINTERMEDIATELOWUpdated Dec 22, 2025· 98 views

This warning appears when a PostgreSQL procedure returns multiple dynamic result sets using refcursors. It's informational, not an error, indicating that multiple cursors were opened and returned.

Quick fix

Fixes 0100C: dynamic_result_sets_returned

-- Example procedure returning multiple result sets
CREATE OR REPLACE PROCEDURE get_multiple_datasets(
  OUT cursor1 refcursor,
  OUT cursor2 refcursor
)
LANGUAGE plpgsql
AS $$
BEGIN
  OPEN cursor1 FOR SELECT * FROM users WHERE active = true;
  OPEN cursor2 FOR SELECT * FROM orders WHERE status = 'pending';
END;
$$;
  1. Verify this is informational, not an error
  2. Ensure transaction context for cursor usage
  3. Handle cursors in application code
  4. Use SETOF refcursor for function-based approach
postgresql — error
-- Example procedure returning multiple result sets
CREATE OR REPLACE PROCEDURE get_multiple_datasets(
  OUT cursor1 refcursor,
  OUT cursor2 refcursor
)
LANGUAGE plpgsql
AS $$
BEGIN
  OPEN cursor1 FOR SELECT * FROM users WHERE active = true;
  OPEN cursor2 FOR SELECT * FROM orders WHERE status = 'pending';
END;
$$;
0100C: dynamic_result_sets_returned

What this error means

The SQLSTATE 0100C "dynamic_result_sets_returned" is a warning-level message (class 01) that PostgreSQL generates when a stored procedure successfully returns one or more dynamic result sets. This commonly occurs when procedures use refcursor types to return multiple query results to the caller. This is not an error condition but rather a notification from the database engine. It indicates that the procedure executed successfully and has made dynamic result sets available for consumption. The warning follows the SQL standard's approach to signaling that additional result sets beyond the primary return value are available. In PostgreSQL, procedures can return multiple result sets by opening cursors and returning them as refcursor types. The 0100C warning confirms that this mechanism worked as intended, and one or more cursors are now open and ready to be fetched from.

Typical symptoms

  • Warning message "0100C: dynamic_result_sets_returned" appears in logs or client output
  • Procedure using SETOF refcursor or OUT refcursor parameters completes successfully
  • Application receives multiple cursor references from a single procedure call
  • Database client shows a warning but continues normal execution

Common causes

  1. Procedure explicitly returns multiple result sets using refcursor return type
  2. Function declared with SETOF refcursor calls RETURN NEXT for each cursor
  3. Procedure uses OUT parameters with refcursor type to return multiple cursors
  4. SQL/PSM procedure opens dynamic cursors and returns them to the caller

How to fix "0100C: dynamic_result_sets_returned"

1Verify this is informational, not an error

The 0100C warning is informational and indicates successful execution. No action is required unless you need to suppress warnings in your application.

Check that your application correctly handles the returned cursors:

sql
-- Example procedure returning multiple result sets
CREATE OR REPLACE PROCEDURE get_multiple_datasets(
  OUT cursor1 refcursor,
  OUT cursor2 refcursor
)
LANGUAGE plpgsql
AS $$
BEGIN
  OPEN cursor1 FOR SELECT * FROM users WHERE active = true;
  OPEN cursor2 FOR SELECT * FROM orders WHERE status = 'pending';
END;
$$;

This procedure will trigger the 0100C warning when called successfully.

2Ensure transaction context for cursor usage

Cursors remain open until the end of the transaction. Since PostgreSQL operates in auto-commit mode by default, cursors close immediately after the procedure call. Start a transaction to use the returned cursors:

sql
BEGIN;

CALL get_multiple_datasets(NULL, NULL);

-- Fetch from the cursors
FETCH ALL FROM cursor1;
FETCH ALL FROM cursor2;

COMMIT;

Without an explicit transaction, the cursors will be closed before you can fetch data from them.

3Handle cursors in application code

When consuming dynamic result sets from your application, ensure your database driver supports multiple cursors. Example in Python with psycopg2:

python
import psycopg2

conn = psycopg2.connect("dbname=mydb user=postgres")
conn.autocommit = False  # Disable auto-commit

cursor = conn.cursor()
cursor.callproc('get_multiple_datasets', [None, None])

# Fetch from first cursor
cursor.execute("FETCH ALL FROM cursor1")
results1 = cursor.fetchall()

# Fetch from second cursor
cursor.execute("FETCH ALL FROM cursor2")
results2 = cursor.fetchall()

conn.commit()
conn.close()

Make sure to handle the cursors within the same transaction context.

4Use SETOF refcursor for function-based approach

If you prefer functions over procedures, use SETOF refcursor with RETURN NEXT:

sql
CREATE OR REPLACE FUNCTION get_multiple_datasets_func()
RETURNS SETOF refcursor
LANGUAGE plpgsql
AS $$
DECLARE
  cursor1 refcursor;
  cursor2 refcursor;
BEGIN
  OPEN cursor1 FOR SELECT * FROM users WHERE active = true;
  RETURN NEXT cursor1;

  OPEN cursor2 FOR SELECT * FROM orders WHERE status = 'pending';
  RETURN NEXT cursor2;
END;
$$;

-- Call the function
BEGIN;
SELECT get_multiple_datasets_func();
-- Then fetch from returned cursors
COMMIT;

This approach also triggers the 0100C warning when multiple cursors are returned.

Advanced notes

The 0100C warning is part of PostgreSQL's SQLSTATE warning class (01). It's defined in the PostgreSQL source code as ERRCODE_WARNING_DYNAMIC_RESULT_SETS_RETURNED and is a standard SQL/PSM condition indicator.

Suppressing the warning: If your application treats warnings as errors or logs them unnecessarily, you can configure your client to ignore class 01 warnings, though this is generally not recommended as it may hide other useful notifications.

Performance considerations: While dynamic result sets provide flexibility, they keep server-side cursors open, consuming memory until the transaction commits. For large datasets, consider whether returning cursors is more efficient than returning table results directly with RETURNS TABLE or RETURNS SETOF.

Alternative patterns: Modern PostgreSQL applications often prefer RETURNS TABLE or OUT parameters with composite types over refcursors, as these are easier to consume and don't require explicit transaction management. Use refcursors primarily when you need streaming large result sets or when integrating with legacy systems that expect cursor-based interfaces.

PostgreSQL version notes: Dynamic result sets through procedures with OUT refcursor parameters were significantly improved in PostgreSQL 11+. Earlier versions had more limited support, particularly around the CALL statement syntax.

Frequently asked questions

What does "0100C: dynamic_result_sets_returned" mean?
The SQLSTATE 0100C "dynamic_result_sets_returned" is a warning-level message (class 01) that PostgreSQL generates when a stored procedure successfully returns one or more dynamic result sets. This commonly occurs when procedures use refcursor types to return multiple query results to the caller. This is not an error condition but rather a notification from the database engine.
How do I fix "0100C: dynamic_result_sets_returned"?
This warning appears when a PostgreSQL procedure returns multiple dynamic result sets using refcursors. It's informational, not an error, indicating that multiple cursors were opened and returned.
What causes "0100C: dynamic_result_sets_returned"?
Procedure explicitly returns multiple result sets using refcursor return type Function declared with SETOF refcursor calls RETURN NEXT for each cursor Procedure uses OUT parameters with refcursor type to return multiple cursors
Is the "0100C: dynamic_result_sets_returned" error serious?
It is a low-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading