PostgreSQL Containing SQL Not Permitted (SQLSTATE 38001)

PostgreSQL raises ERROR: containing SQL not permitted with SQLSTATE 38001 and condition name containing_sql_not_permitted when an external routine (typically written in a language such as PL/Java, PL/Perl, or another external procedural language) that was declared with a NO SQL access level attribute attempts to execute an SQL statement. This error belongs to SQLSTATE class 38 — "External Routine Exception."

What This Error Means

SQLSTATE class 38 covers errors originating in external routines — functions implemented in languages outside PostgreSQL's built-in PL/pgSQL. When you create such a function, you can declare its SQL access level using one of four keywords: NO SQL, CONTAINS SQL, READS SQL DATA, or MODIFIES SQL DATA. These declarations serve as a contract between the function and the database engine.

When a function is declared NO SQL, PostgreSQL (and the underlying language handler) expects it to make no SQL calls whatsoever. If the function body violates that contract by issuing a query or statement, PostgreSQL raises SQLSTATE 38001. The error is thrown at runtime, not at function creation time, because the declaration is advisory/contractual rather than enforced at parse time.

This error is most commonly encountered in environments using external language extensions like PL/Java, where the SQL access level attribute is explicitly checked by the language handler. In contrast, PL/pgSQL functions do not use this attribute system in the same way, so 38001 is virtually never seen in pure PL/pgSQL code.

Common Causes

1. Function declared NO SQL but executes a query: A PL/Java or other external language function was created with NO SQL in its CREATE FUNCTION statement, but the function body calls Statement.executeQuery(), SPI_exec(), or equivalent, attempting to run SQL at runtime.

2. Copy-paste or template mismatch: A function definition was copied from a template that included NO SQL, but the function body was later updated to include SQL logic without updating the SQL access level declaration.

3. Third-party extension or ORM-generated DDL: An ORM, migration tool, or extension auto-generates CREATE FUNCTION statements with NO SQL as a conservative default, but the underlying implementation actually issues SQL statements.

4. Language handler enforcement: Some external language handlers (particularly PL/Java) enforce the SQL access attribute strictly. A function that worked under a more permissive handler may fail when the handler is upgraded or replaced with one that enforces NO SQL strictly.

How to Fix containing_sql_not_permitted

1. Update the SQL access level to match what the function actually does. If the function reads data but does not modify it, use READS SQL DATA. If it can modify data, use MODIFIES SQL DATA. If it only calls SQL but does not depend on any specific data, CONTAINS SQL is also an option:

-- Before (incorrect declaration for a function that reads data)
CREATE FUNCTION my_function(p_id integer)
RETURNS text
LANGUAGE java
NO SQL
AS 'com.example.MyClass.myMethod';

-- After (corrected declaration)
CREATE FUNCTION my_function(p_id integer)
RETURNS text
LANGUAGE java
READS SQL DATA
AS 'com.example.MyClass.myMethod';

2. Alter an existing function's access level without recreating it using ALTER FUNCTION:

ALTER FUNCTION my_function(integer) READS SQL DATA;

Note that not all PostgreSQL versions and language handlers support altering the SQL access level directly — you may need to DROP and recreate the function.

3. Remove the SQL calls from the function body if the function truly should not execute SQL. If the logic can be refactored to avoid issuing SQL (e.g., by computing the result purely in the external language), the NO SQL declaration becomes valid.

4. Check your ORM or migration framework DDL output. If the function definition is auto-generated, inspect the generated SQL and override the default SQL access level annotation in the framework's configuration or function metadata.

5. Review the function definition in the catalog to confirm the current declared access level:

SELECT proname, prosrc, provolatile
FROM pg_proc
WHERE proname = 'my_function';

For external language functions, the relevant attribute is stored in prokind and related columns. You can inspect the full definition with:

\sf my_function

Additional Information

• SQLSTATE class 38 (External Routine Exception) contains four conditions: 38000 (external_routine_exception, the generic catch-all), 38001 (containing_sql_not_permitted), 38002 (modifying_sql_data_not_permitted), and 38003 (prohibited_sql_statement_attempted). These are all related to mismatches between a function's declared SQL access level and its actual behavior.
• The SQL access level attributes (NO SQL, CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA) originate from the SQL/PSM standard and are most meaningful for external language routines. PL/pgSQL does not use this declaration mechanism.
• PL/Java is the most common environment where 38001 surfaces, because its JDBC-based execution model explicitly checks the declared SQL access level before allowing Connection or Statement objects to be used.
• The error leaves the current transaction in an aborted state. You must issue a ROLLBACK (or ROLLBACK TO SAVEPOINT) before issuing further statements in the same session.
• PostgreSQL has supported this SQLSTATE since external language function support was introduced. The behavior has been consistent across PostgreSQL 9.x through 17.x.

Frequently Asked Questions

Why does PostgreSQL not catch this error when the function is created? The NO SQL declaration is a contract, not a compile-time constraint. PostgreSQL does not parse or analyze the body of external language functions — that is the responsibility of the language handler at runtime. The mismatch is only detected when the function actually executes and the handler attempts to issue a SQL call.

Does this error occur with PL/pgSQL functions? No. PL/pgSQL does not use the NO SQL / CONTAINS SQL / READS SQL DATA / MODIFIES SQL DATA attribute system in a way that produces SQLSTATE 38001. This error is specific to external language functions (e.g., PL/Java, PL/Perl with certain configurations) where the language handler enforces the declared access level.

What is the difference between SQLSTATE 38001 and 38002? 38001 (containing_sql_not_permitted) is raised when a NO SQL function attempts to execute any SQL at all. 38002 (modifying_sql_data_not_permitted) is raised when a function declared READS SQL DATA attempts to execute data-modifying SQL (INSERT, UPDATE, DELETE, etc.). Both indicate a mismatch between a function's declared and actual SQL behavior.

Can I declare a function MODIFIES SQL DATA as a safe default to avoid this error? Using MODIFIES SQL DATA as a blanket default will prevent 38001 and 38002 from being raised, but it may have correctness implications — some optimizations and call-site restrictions rely on the declared access level. Use the most accurate declaration that reflects what the function actually does.