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 declared with NO SQL or an incompatible SQL data access level attempts to execute SQL statements internally. This typically surfaces in procedural language functions (PL/pgSQL, PL/Python, PL/Perl) or external routine contexts where the declared SQL access level conflicts with the actual function body behavior.

---

Top 3 Causes

1. Function Declared with NO SQL But Executes SQL Internally

This is the most common cause. When you declare a function with NO SQL, PostgreSQL enforces that the function body contains absolutely no SQL statements. If the function body then tries to run any SQL, error 38001 is raised immediately.

-- BAD: Declared NO SQL but runs a SELECT internally
CREATE FUNCTION bad_count_users()
RETURNS INTEGER
LANGUAGE plpgsql
NO SQL  -- This is the problem
AS $$
DECLARE
    result INTEGER;
BEGIN
    -- This triggers error 38001
    SELECT COUNT(*) INTO result FROM users;
    RETURN result;
END;
$$;

-- GOOD: Use the correct SQL access level
CREATE OR REPLACE FUNCTION good_count_users()
RETURNS INTEGER
LANGUAGE plpgsql
READS SQL DATA  -- Correctly reflects read-only SQL usage
AS $$
DECLARE
    result INTEGER;
BEGIN
    SELECT COUNT(*) INTO result FROM users;
    RETURN result;
END;
$$;

2. External Language Functions (PL/Python / PL/Perl) with Wrong Access Level

PL/Python (plpythonu) and PL/Perl (plperlu) functions that use plpy.execute() or database cursors to run SQL will fail with 38001 if tagged with NO SQL.

-- BAD: PL/Python function with NO SQL tries to query the database
CREATE FUNCTION py_get_version()
RETURNS TEXT
LANGUAGE plpythonu
NO SQL  -- Causes 38001 when plpy.execute() is called
AS $$
result = plpy.execute("SELECT version()")  -- 38001 triggered here
return result[0]['version']
$$;

-- GOOD: Correct SQL access level for PL/Python
CREATE OR REPLACE FUNCTION py_get_version()
RETURNS TEXT
LANGUAGE plpythonu
READS SQL DATA  -- Now consistent with actual behavior
AS $$
result = plpy.execute("SELECT version()")
return result[0]['version']
$$;

3. Trigger Functions with Incompatible SQL Access Level

Trigger functions almost always need to read from or write to database tables. Declaring them with NO SQL breaks this assumption and causes 38001 when the trigger fires.

-- BAD: Trigger function with NO SQL declaration
CREATE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
NO SQL  -- Wrong for a trigger that writes to audit_log
AS $$
BEGIN
    -- 38001 fires here because the function is declared NO SQL
    INSERT INTO audit_log(table_name, action, logged_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$;

-- GOOD: Remove NO SQL or declare MODIFIES SQL DATA
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
MODIFIES SQL DATA  -- Correct: trigger writes to another table
AS $$
BEGIN
    INSERT INTO audit_log(table_name, action, logged_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$;

CREATE TRIGGER users_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_changes();

---

Quick Fix Solutions

Situation	Fix
Function reads data	Use READS SQL DATA
Function writes data	Use MODIFIES SQL DATA
Function has no SQL at all	Use NO SQL
Unsure / mixed usage	Omit the attribute (defaults to CONTAINS SQL)

To inspect existing functions for mismatches:

-- Check all user-defined functions and their definitions
SELECT
    n.nspname AS schema_name,
    p.proname AS function_name,
    l.lanname AS language,
    pg_get_functiondef(p.oid) AS full_definition
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

1. Establish a team coding convention: Reserve NO SQL strictly for pure computation functions (math, string manipulation, etc.) that genuinely contain zero SQL. For everything else, explicitly declare READS SQL DATA or MODIFIES SQL DATA to make intent clear and verifiable during code review.

2. Validate function definitions in CI/CD: Add a pipeline step that inspects newly deployed function definitions and cross-checks their declared SQL access level against keywords like SELECT, INSERT, UPDATE, or DELETE in the function body. Catching this mismatch before production deployment eliminates runtime surprises entirely.

---

Related Errors

• 38000 external_routine_exception — Parent error class for all 3800x errors.
• 38002 modifying_sql_data_not_permitted — Triggered when a READS SQL DATA function attempts a write operation.
• 38003 prohibited_sql_statement_attempted — A SQL statement was attempted that is not allowed in the current context.
• 38004 reading_sql_data_not_permitted — Triggered when a NO SQL or CONTAINS SQL function tries to read data.

---

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