How to fix 42P13: invalid_function_definition in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 128 views

The PostgreSQL 42P13 error occurs when creating or altering a function with invalid syntax, missing parameters, or conflicting definitions. This prevents the database from parsing and storing the function correctly, requiring syntax fixes and validation.

Quick fix

Fixes 42P13: invalid_function_definition

CREATE FUNCTION calculate_total(price numeric, quantity integer)
RETURNS numeric AS $$
BEGIN
  RETURN price * quantity;
END;
$$ LANGUAGE plpgsql;
  1. Check function syntax and parameters
  2. Verify function language compatibility
  3. Check for function name conflicts
  4. Validate function body syntax
postgresql — error
CREATE FUNCTION calculate_total(price numeric, quantity integer)
RETURNS numeric AS $$
BEGIN
  RETURN price * quantity;
END;
$$ LANGUAGE plpgsql;
42P13: invalid_function_definition

What this error means

The "42P13: invalid_function_definition" error in PostgreSQL indicates that the database cannot accept a function definition due to syntax errors, semantic issues, or conflicts with existing functions. PostgreSQL functions must follow strict syntax rules including proper parameter declarations, return type specifications, and language definitions. This error typically appears when using CREATE FUNCTION or ALTER FUNCTION statements with problems like: - Missing or incorrect parameter types - Invalid return type specifications - Language declaration errors - Syntax errors in the function body - Conflicts with existing function signatures PostgreSQL validates function definitions against the system catalog and SQL standards, rejecting definitions that don't meet requirements. The error prevents the function from being created or modified until all issues are resolved.

Typical symptoms

  • CREATE FUNCTION or ALTER FUNCTION statements fail with "42P13: invalid_function_definition"
  • Function creation is rejected during migration or schema updates
  • Database tools show syntax errors when defining stored procedures
  • SQL scripts fail to execute due to function definition problems

Common causes

  1. Missing or incorrect parameter type declarations in function signature
  2. Invalid or unsupported return type specification
  3. Syntax errors in the function body (missing semicolons, unmatched parentheses)
  4. Language specification mismatch (e.g., specifying "plpgsql" when using SQL syntax)
  5. Function name conflicts with existing functions (same name and parameter types)
  6. Using reserved keywords as parameter names without proper quoting

How to fix "42P13: invalid_function_definition"

1Check function syntax and parameters

Examine your CREATE FUNCTION statement for syntax errors. Ensure all parameters have valid data types and the return type is correctly specified.

Example of correct syntax:

sql
CREATE FUNCTION calculate_total(price numeric, quantity integer)
RETURNS numeric AS $$
BEGIN
  RETURN price * quantity;
END;
$$ LANGUAGE plpgsql;

Common issues to check:
- Missing parameter types: (price, quantity) instead of (price numeric, quantity integer)
- Incorrect return type: RETURNS void when function returns a value
- Missing language declaration: $$ LANGUAGE plpgsql;

2Verify function language compatibility

Ensure the function body matches the declared language. SQL functions and PL/pgSQL functions have different syntax requirements.

For SQL functions:

sql
CREATE FUNCTION add_numbers(a integer, b integer)
RETURNS integer AS $$
  SELECT a + b;
$$ LANGUAGE sql;

For PL/pgSQL functions:

sql
CREATE FUNCTION add_numbers(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Mixing SQL syntax with PL/pgSQL language declaration (or vice versa) causes 42P13 errors.

3Check for function name conflicts

PostgreSQL doesn't allow multiple functions with the same name and parameter types. Use \dF or query the pg_proc catalog to check for existing functions:

sql
-- List functions with similar names
SELECT proname, pronargs, proargtypes::regtype[]
FROM pg_proc
WHERE proname LIKE '%your_function_name%';

-- Or use psql command
\df your_function_name

If a conflict exists, you need to:
1. Drop the existing function: DROP FUNCTION IF EXISTS your_function_name(parameter_types);
2. Use a different function name
3. Change parameter types to create a different signature

4Validate function body syntax

The function body (between the $$ delimiters) must be valid SQL or PL/pgSQL. Common syntax errors include:

- Missing semicolons in PL/pgSQL blocks
- Unmatched parentheses or quotes
- Invalid variable declarations
- Incorrect control structures

Test the function body separately if possible:

sql
-- Test the SQL part separately
SELECT price * quantity;

-- Or test PL/pgSQL logic in a DO block
DO $$
DECLARE
  result numeric;
BEGIN
  result := 10.99 * 3;
  RAISE NOTICE 'Result: %', result;
END;
$$;
5Review error message details

PostgreSQL usually provides additional context in the error message. Look for lines like:

bash
ERROR:  42P13: invalid_function_definition
DETAIL:  SQL function "calculate_total" must return "numeric"
CONTEXT:  SQL statement "CREATE FUNCTION calculate_total..."

The DETAIL and CONTEXT sections often pinpoint the exact issue. Common patterns:

- "must return [type]" - return type mismatch
- "parameter [name] has pseudo-type [type]" - invalid parameter type
- "function already exists with same name and argument types" - name conflict
- "syntax error at or near [token]" - specific syntax error

6Use CREATE OR REPLACE FUNCTION cautiously

While CREATE OR REPLACE FUNCTION can help with updates, it may mask underlying issues. Use it only after verifying your function is correct:

sql
-- First, ensure your function syntax is valid
CREATE OR REPLACE FUNCTION calculate_total(price numeric, quantity integer)
RETURNS numeric AS $$
BEGIN
  RETURN price * quantity;
END;
$$ LANGUAGE plpgsql;

Important notes:
- OR REPLACE only works if the parameter types match exactly
- It won't fix syntax errors in the function body
- Consider using DROP FUNCTION IF EXISTS followed by CREATE FUNCTION for clearer error messages

Advanced notes

PostgreSQL function definitions involve several validation steps:

1. Parser Validation: The SQL parser checks basic syntax before any catalog lookups.

2. Catalog Validation: PostgreSQL checks the pg_proc catalog for conflicts and validates types against pg_type.

3. Language Handler: The specified language (sql, plpgsql, etc.) must be installed and available.

4. Security Context: Functions with SECURITY DEFINER have additional permission checks.

Edge Cases:
- Functions with OUT parameters have different syntax requirements
- VARIADIC parameters must be the last parameter
- Functions returning SETOF require different syntax
- Default parameter values must be constants or immutable expressions

Debugging Tips:
- Use \set VERBOSITY verbose in psql for more detailed error messages
- Check PostgreSQL server logs for additional context
- Test function creation in a fresh database to isolate environment issues

Version Differences:
- PostgreSQL 14+ has stricter type checking for some edge cases
- Older versions may accept syntax that newer versions reject
- Extension functions (like PostGIS) may have additional requirements

Frequently asked questions

What does "42P13: invalid_function_definition" mean?
The "42P13: invalid_function_definition" error in PostgreSQL indicates that the database cannot accept a function definition due to syntax errors, semantic issues, or conflicts with existing functions. PostgreSQL functions must follow strict syntax rules including proper parameter declarations, return type specifications, and language definitions. This error typically appears when using CREATE FUNCTION or ALTER FUNCTION statements with problems like: - Missing or incorrect parameter types - Invalid return type specifications - Language declaration errors - Syntax errors in the function body - Conflicts with existing function signatures PostgreSQL validates function definitions against the system catalog and SQL standards, rejecting definitions that don't meet requirements.
How do I fix "42P13: invalid_function_definition"?
The PostgreSQL 42P13 error occurs when creating or altering a function with invalid syntax, missing parameters, or conflicting definitions. This prevents the database from parsing and storing the function correctly, requiring syntax fixes and validation.
What causes "42P13: invalid_function_definition"?
Missing or incorrect parameter type declarations in function signature Invalid or unsupported return type specification Syntax errors in the function body (missing semicolons, unmatched parentheses)
Is the "42P13: invalid_function_definition" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading