PostgreSQL Invalid Function Definition (SQLSTATE 42P13)

When PostgreSQL encounters a CREATE FUNCTION or CREATE PROCEDURE statement with contradictory, missing, or invalid options, it raises:

ERROR:  invalid function definition

with SQLSTATE 42P13 and condition name invalid_function_definition. The error fires at DDL execution time and prevents the function from being created or replaced.

What This Error Means

SQLSTATE 42P13 belongs to error class 42 — Syntax Error or Access Rule Violation. PostgreSQL raises this error during the semantic validation phase of CREATE FUNCTION or CREATE PROCEDURE, after parsing succeeds but before the catalog entry is written. The statement is rolled back entirely; no partial function object is left behind.

The error surfaces when the combination of options supplied to the function definition is logically inconsistent or violates PostgreSQL's rules for function attributes. Unlike a syntax error (class 42601) which indicates malformed SQL, 42P13 means the SQL parsed correctly but the meaning of the option combination is invalid.

Because the error occurs at DDL time and not at runtime, it does not leave an open transaction in a bad state beyond the failure of the statement itself. In an explicit transaction block, the transaction is marked as aborted and must be rolled back before any further commands can proceed.

Common Causes

1. Combining WINDOW with RETURNS TABLE or SET RETURNING functions. A function declared as a window function (WINDOW) must return a scalar value. Declaring it as a set-returning function (RETURNS TABLE(...) or RETURNS SETOF) is contradictory and triggers 42P13.

2. Declaring a procedure with a return type. Procedures (CREATE PROCEDURE) must not specify a RETURNS clause. Adding one (e.g., RETURNS void) raises this error in PostgreSQL 11+, where procedures are a distinct object type from functions.

3. STRICT (or CALLED ON NULL INPUT) combined with WINDOW. Window functions are always called with the full argument list managed by the executor; declaring them STRICT (i.e., RETURNS NULL ON NULL INPUT) is not permitted.

4. SECURITY DEFINER on a procedure in certain PostgreSQL versions. Older versions did not permit SECURITY DEFINER on procedures. Check your PostgreSQL version's documentation if you are upgrading.

5. Missing or conflicting LANGUAGE clause. While a missing LANGUAGE typically produces a different message, certain combinations — such as specifying a language that does not support particular function attributes (e.g., trying to declare an internal function without a valid C-level implementation reference) — can manifest as 42P13.

6. PARALLEL option incompatible with volatility. Declaring a function as PARALLEL SAFE alongside VOLATILE is not automatically an error, but certain combinations with other attributes can conflict internally and trigger this error depending on the PostgreSQL version.

How to Fix invalid_function_definition

1. Remove the conflicting attribute. Identify which pair of options is contradictory and remove or correct one of them. The error message from PostgreSQL often names the conflicting option directly:

   -- Wrong: WINDOW function cannot be set-returning
   CREATE FUNCTION running_total(val numeric)
     RETURNS SETOF numeric
     LANGUAGE internal
     WINDOW
   AS 'window_row_number';
   
   -- Fixed: return a scalar
   CREATE FUNCTION running_total(val numeric)
     RETURNS numeric
     LANGUAGE internal
     WINDOW
   AS 'window_row_number';
   

2. Remove RETURNS from a CREATE PROCEDURE statement.

   -- Wrong: procedures have no return value
   CREATE PROCEDURE update_balances(cutoff date)
     RETURNS void
     LANGUAGE plpgsql
   AS $$
   BEGIN
     UPDATE accounts SET balance = 0 WHERE last_activity < cutoff;
   END;
   $$;
   
   -- Fixed: drop RETURNS entirely
   CREATE PROCEDURE update_balances(cutoff date)
     LANGUAGE plpgsql
   AS $$
   BEGIN
     UPDATE accounts SET balance = 0 WHERE last_activity < cutoff;
   END;
   $$;
   

3. Check the WINDOW + STRICT combination. If you need null-safe behavior in a window function, handle null inputs explicitly inside the function body rather than using STRICT:

   -- Wrong
   CREATE FUNCTION my_window_fn(val numeric)
     RETURNS numeric
     LANGUAGE plpgsql
     WINDOW
     STRICT
   AS $$ ... $$;
   
   -- Fixed: remove STRICT, handle nulls inside
   CREATE FUNCTION my_window_fn(val numeric)
     RETURNS numeric
     LANGUAGE plpgsql
     WINDOW
   AS $$
   BEGIN
     IF val IS NULL THEN RETURN NULL; END IF;
     -- ... rest of logic
   END;
   $$;
   

4. Consult pg_proc for an existing function's definition if you are using CREATE OR REPLACE FUNCTION and the existing version has attributes that conflict with the new definition:

   SELECT proname, prokind, proretset, proiswindow, prosecdef, provolatile, proparallel
   FROM pg_proc
   WHERE proname = 'your_function_name';
   

   Drop and recreate rather than replace if the attribute change is incompatible.

Additional Information

• SQLSTATE class 42 (Syntax Error or Access Rule Violation) includes related codes such as 42601 (syntax error), 42703 (undefined column), and 42883 (undefined function). These all indicate problems detected at parse or planning time rather than runtime.
• CREATE PROCEDURE was introduced in PostgreSQL 11. Before that version, all callable routines were functions, and some attribute rules differed. If you are migrating DDL scripts across major versions, 42P13 errors may appear on upgrade.
• PL/pgSQL, PL/Python, PL/Perl, and other procedural language functions each have their own constraints on which function attributes are valid. An attribute legal for a C-language function may not be valid for a PL/pgSQL function.
• Most PostgreSQL client drivers (libpq, psycopg2, JDBC, node-postgres) surface this as a standard server-side error with the SQLSTATE included. Check the pgcode / getSQLState() property of the exception object to distinguish it programmatically from other DDL failures.
• ORM migration tools (Django, SQLAlchemy-Alembic, Flyway, Liquibase) that auto-generate or apply raw DDL migrations will propagate this error unchanged. The migration will fail and roll back; check your migration script's CREATE FUNCTION statement for conflicting options.

Frequently Asked Questions

Why does PostgreSQL raise 42P13 on a function that worked in an older version? Option validation rules have tightened across major PostgreSQL versions, especially around procedures (added in PostgreSQL 11), parallelism attributes (added in PostgreSQL 9.6), and security-related options. A CREATE FUNCTION script that was valid on PostgreSQL 10 may raise 42P13 on PostgreSQL 14 if it uses attributes that are now more strictly validated.

Can 42P13 be raised at runtime, not just at CREATE FUNCTION time? No. SQLSTATE 42P13 is a DDL-time error raised during the validation of a function or procedure definition. It will never appear during a routine SELECT, INSERT, or a function call. If you see it, the failing statement is always a CREATE FUNCTION, CREATE PROCEDURE, or CREATE OR REPLACE FUNCTION.

The error message just says "invalid function definition" — how do I find out which option is wrong? Enable client_min_messages = debug1 or check the PostgreSQL server log at log_min_messages = debug1 to see more detailed context. Additionally, re-read the PostgreSQL documentation for the specific language and function kind (FUNCTION vs PROCEDURE, WINDOW vs ordinary) and cross-check every attribute in your DDL against the allowed combinations listed there.

Is there a way to test a function definition without committing it? Yes. Wrap the CREATE FUNCTION statement in an explicit transaction and roll it back after checking for errors:

BEGIN;
CREATE FUNCTION test_fn() RETURNS void LANGUAGE plpgsql AS $$ BEGIN END; $$;
-- Inspect pg_proc to verify attributes
SELECT prokind, proretset FROM pg_proc WHERE proname = 'test_fn';
ROLLBACK;

This lets you validate that the definition is accepted without permanently adding the function to the catalog.