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.
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.
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:
-- 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.
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:
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.
When consuming dynamic result sets from your application, ensure your database driver supports multiple cursors. Example in Python with psycopg2:
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.
If you prefer functions over procedures, use SETOF refcursor with RETURN NEXT:
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.
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.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
vacuum failsafe triggered
How to fix "vacuum failsafe triggered" in PostgreSQL
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL