PostgreSQL 0100C Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 0100C: dynamic_result_sets_returned

PostgreSQL warning code 0100C (dynamic_result_sets_returned) is raised when a stored procedure returns more dynamic result sets than it declared in its RESULT SETS clause. This is a warning, not a fatal error, but it signals a mismatch between your procedure's signature and its actual runtime behavior. Ignoring it can cause subtle bugs in applications that rely on a fixed number of result sets from a procedure call.

Top 3 Causes

1. Mismatch Between RESULT SETS Declaration and Actual Returns

The most common cause: you declared RESULT SETS 1 but the procedure actually returns 2 or more result sets at runtime.

-- Problematic: declared RESULT SETS 1, but returns 2
CREATE OR REPLACE PROCEDURE get_data(dept_id INT)
LANGUAGE SQL
RESULT SETS 1  -- Wrong!
AS $$
  SELECT employee_id, name FROM employees WHERE department_id = dept_id;
  SELECT dept_id, dept_name FROM departments WHERE department_id = dept_id;
$$;

-- Fixed: update declaration to match actual result sets
CREATE OR REPLACE PROCEDURE get_data(dept_id INT)
LANGUAGE SQL
RESULT SETS 2  -- Correct
AS $$
  SELECT employee_id, name FROM employees WHERE department_id = dept_id;
  SELECT dept_id, dept_name FROM departments WHERE department_id = dept_id;
$$;

2. Conditional Logic Producing a Variable Number of Result Sets

When IF/CASE branches inside a procedure return different numbers of result sets, the declared value will inevitably be wrong for at least one branch.

-- Problematic: branch-dependent result set count
CREATE OR REPLACE PROCEDURE dynamic_report(mode VARCHAR)
LANGUAGE plpgsql
RESULT SETS 1
AS $$
BEGIN
  IF mode = 'basic' THEN
    RETURN QUERY SELECT id, name FROM employees;         -- 1 result set
  ELSE
    RETURN QUERY SELECT id, name FROM employees;         -- triggers 0100C
    RETURN QUERY SELECT dept_id, name FROM departments;  -- 2 result sets
  END IF;
END;
$$;

-- Fix: split into separate, single-responsibility procedures
CREATE OR REPLACE PROCEDURE basic_report()
LANGUAGE plpgsql RESULT SETS 1
AS $$
BEGIN
  RETURN QUERY SELECT id, name FROM employees;
END;
$$;

CREATE OR REPLACE PROCEDURE full_report()
LANGUAGE plpgsql RESULT SETS 2
AS $$
BEGIN
  RETURN QUERY SELECT id, name FROM employees;
  RETURN QUERY SELECT dept_id, name FROM departments;
END;
$$;

3. Incorrect Auto-Conversion During Database Migration

Migration tools (from Oracle, DB2, SQL Server) often default RESULT SETS to 1, regardless of the procedure's actual logic. This is a silent, widespread issue in migrated codebases.

-- Audit all procedures after migration
SELECT
    n.nspname   AS schema_name,
    p.proname   AS proc_name,
    pg_get_functiondef(p.oid) AS definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.prokind = 'p'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;

-- Check a specific procedure's full definition
SELECT pg_get_functiondef(oid)
FROM pg_proc
WHERE proname = 'get_data'
  AND prokind = 'p';

Quick Fix Solutions

Option A – Update the RESULT SETS clause to match the actual number of result sets returned by the procedure.

Option B – Refactor to a table-returning function, which eliminates the RESULT SETS declaration entirely and is more idiomatic in PostgreSQL:

-- Preferred PostgreSQL pattern: use RETURNS TABLE instead
CREATE OR REPLACE FUNCTION get_employee_info(dept_id INT)
RETURNS TABLE(
  employee_id  INT,
  name         VARCHAR,
  record_type  VARCHAR
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT e.employee_id, e.name, 'EMPLOYEE'::VARCHAR
    FROM employees e
    WHERE e.department_id = dept_id;

  RETURN QUERY
    SELECT d.dept_id, d.dept_name, 'DEPARTMENT'::VARCHAR
    FROM departments d
    WHERE d.department_id = dept_id;
END;
$$;

-- Call it like a regular query
SELECT * FROM get_employee_info(10);

Option C – Capture and monitor warnings in your application layer or logs:

-- Set client_min_messages to capture warnings
SET client_min_messages = 'WARNING';
CALL get_data(10);

Prevention Tips

1. Enforce signature reviews in your PR/code review process.
Any change to a stored procedure's body must include a review of its RESULT SETS declaration. Add an automated test step in your CI/CD pipeline that calls each procedure and checks for 0100C warnings in the PostgreSQL log output.

2. Prefer table-returning functions over procedures with RESULT SETS.
In PostgreSQL, RETURNS TABLE(...) functions are more flexible, easier to test, and avoid the RESULT SETS declaration pitfall entirely. Reserve RESULT SETS only when strict SQL standard compliance is a hard requirement.

-- Run a quick sanity check on all procedures post-deployment
DO $$
DECLARE
  proc RECORD;
BEGIN
  FOR proc IN
    SELECT proname FROM pg_proc WHERE prokind = 'p'
      AND pronamespace NOT IN (
        SELECT oid FROM pg_namespace
        WHERE nspname IN ('pg_catalog','information_schema')
      )
  LOOP
    RAISE NOTICE 'Reviewing procedure: %', proc.proname;
  END LOOP;
END;
$$;

Related Error Codes

Code	Name	Relationship
`01000`	`warning`	Parent class of `0100C`
`01P01`	`deprecated_feature`	Often appears alongside `0100C` during migrations
`42P13`	`invalid_function_definition`	Raised when `RESULT SETS` syntax itself is invalid
`0A000`	`feature_not_supported`	Can occur when using `RESULT SETS` on unsupported PostgreSQL versions

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.