PostgreSQL 38001 Error: Causes and Solutions Complete Guide

PostgreSQL Error 38001: containing_sql_not_permitted

PostgreSQL error code 38001 (containing_sql_not_permitted) occurs when a function or procedure attempts to execute SQL statements in a context where SQL execution is explicitly disallowed. This typically happens when a function is declared with NO SQL data access level but contains SQL commands in its body, causing a conflict between the function's declaration and its actual behavior at runtime.

---

Top 3 Causes

1. Function Declared with NO SQL but Contains SQL Statements

The most common cause is a mismatch between the function's declared SQL access level and its actual implementation.

-- WRONG: Declared NO SQL but executes a SELECT
CREATE OR REPLACE FUNCTION bad_get_count()
RETURNS INTEGER
LANGUAGE plpgsql
NO SQL  -- This declaration prohibits any SQL
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    -- This line triggers error 38001 at runtime
    SELECT COUNT(*) INTO v_count FROM users;
    RETURN v_count;
END;
$$;

-- CORRECT: Use READS SQL DATA for read-only SQL access
CREATE OR REPLACE FUNCTION good_get_count()
RETURNS INTEGER
LANGUAGE plpgsql
READS SQL DATA  -- Correctly declares read access
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM users;
    RETURN v_count;
END;
$$;

-- CORRECT: Use MODIFIES SQL DATA for write operations
CREATE OR REPLACE FUNCTION log_event(p_msg TEXT)
RETURNS VOID
LANGUAGE plpgsql
MODIFIES SQL DATA
AS $$
BEGIN
    INSERT INTO event_log(message, logged_at)
    VALUES (p_msg, NOW());
END;
$$;

---

2. External Language Functions (PL/Python, PL/Perl) with Incorrect SQL Access Settings

Functions written in external procedural languages like PL/Python or PL/Perl have strict SQL access controls. Executing SQL via plpy.execute() without the correct function-level declaration will trigger this error.

-- WRONG: Missing proper context for SQL execution in PL/Python
-- (may fail depending on server configuration)
CREATE OR REPLACE FUNCTION py_bad_example()
RETURNS TEXT
LANGUAGE plpython3u
AS $$
# Without proper access level, this may raise 38001
result = plpy.execute("SELECT version()")
return result[0]['version']
$$;

-- CORRECT: Explicitly handle SQL execution with plpy safely
CREATE OR REPLACE FUNCTION py_get_version()
RETURNS TEXT
LANGUAGE plpython3u
AS $$
try:
    result = plpy.execute("SELECT version() AS ver")
    return result[0]['ver']
except plpy.SPIError as e:
    plpy.warning(f"SQL execution failed: {e}")
    return None
$$;

-- Parameterized query using plpy.prepare (recommended)
CREATE OR REPLACE FUNCTION py_find_user(p_id INT)
RETURNS TEXT
LANGUAGE plpython3u
AS $$
plan = plpy.prepare(
    "SELECT username FROM users WHERE user_id = $1",
    ["integer"]
)
result = plpy.execute(plan, [p_id])
return result[0]['username'] if result else None
$$;

---

3. Nested Function Calls with Conflicting SQL Access Levels

When a function with a restrictive SQL access level calls another function that executes SQL, the conflict propagates and causes error 38001.

-- Check which functions might have conflicting SQL access declarations
SELECT
    n.nspname AS schema,
    p.proname AS function_name,
    l.lanname AS language,
    CASE
        WHEN p.prosrc LIKE '%SELECT%' OR p.prosrc LIKE '%INSERT%'
             OR p.prosrc LIKE '%UPDATE%' OR p.prosrc LIKE '%DELETE%'
        THEN 'Contains SQL'
        ELSE 'No SQL detected'
    END AS sql_usage
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE n.nspname = 'public'
ORDER BY p.proname;

-- Correct pattern: ensure calling function allows SQL
CREATE OR REPLACE FUNCTION parent_function(p_user_id INT)
RETURNS TEXT
LANGUAGE plpgsql
READS SQL DATA  -- Must allow SQL since it calls a SQL-using child
AS $$
DECLARE
    v_result TEXT;
BEGIN
    -- Calling a child function that reads data
    SELECT good_get_count()::TEXT INTO v_result;
    RETURN v_result;
END;
$$;

---

Quick Fix Solutions

1. Identify the conflicting function using pg_proc:

SELECT proname, prosrc
FROM pg_proc
WHERE proname = 'your_function_name'
  AND pronamespace = (
      SELECT oid FROM pg_namespace WHERE nspname = 'public'
  );

1. Replace NO SQL with the correct access level (READS SQL DATA or MODIFIES SQL DATA) in your CREATE OR REPLACE FUNCTION statement.

2. Validate all functions in your schema after changes:
   

-- Audit functions for potential access level issues
SELECT
    n.nspname,
    p.proname,
    l.lanname
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l ON p.prolang = l.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;

---

Prevention Tips

• Always explicitly declare the SQL access level (NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA) for every function. Never rely on defaults, especially when using external procedural languages.
• Add automated function validation to your CI/CD pipeline. Before deploying to production, run a script that scans pg_proc for functions whose declared SQL access level conflicts with the actual SQL content in prosrc. This catches 38001-type issues before they ever reach runtime.

---

Related Errors

Code	Name	Description
38000	external_routine_exception	Parent error class for all external routine exceptions
38002	modifying_sql_data_not_permitted	Write SQL attempted in a read-only function context
38003	prohibited_sql_statement_attempted	Forbidden SQL statement (e.g., COMMIT) in a restricted context
38004	reading_sql_data_not_permitted	SELECT attempted where even reads are disallowed

---

📖 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.