PostgreSQL 42P13 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P13: Invalid Function Definition

PostgreSQL error code 42P13 occurs when you attempt to create or replace a function (or procedure) with a structurally invalid definition. Unlike a simple syntax error, this error points to a logical inconsistency in the function's declaration — such as a mismatched return type, missing language clause, or conflicting output parameter definitions. Understanding the root cause is essential for writing robust, production-ready database functions.

---

Top 3 Causes and Fixes

1. Wrong Return Type for Trigger Functions

Trigger functions must return TRIGGER. Declaring any other return type causes a 42P13 error when PostgreSQL validates the function definition.

Incorrect:

-- Wrong: trigger function cannot return void
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS void          -- ❌ This will cause 42P13
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

Correct:

-- Correct: trigger function must return TRIGGER
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER       -- ✅ Correct return type
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

---

2. Missing or Invalid LANGUAGE Clause

Every PostgreSQL function requires an explicit LANGUAGE clause. Omitting it or specifying an uninstalled language raises 42P13 immediately.

Incorrect:

-- Missing LANGUAGE clause
CREATE OR REPLACE FUNCTION get_order_total(p_order_id INTEGER)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN (SELECT total FROM orders WHERE id = p_order_id);
END;
$$;
-- ERROR:  42P13: no language specified

Correct:

-- With explicit LANGUAGE clause
CREATE OR REPLACE FUNCTION get_order_total(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql    -- ✅ Always specify the language
AS $$
BEGIN
    RETURN (SELECT total FROM orders WHERE id = p_order_id);
END;
$$;

-- Check available languages in your database
SELECT lanname FROM pg_language ORDER BY lanname;

---

3. Mixing RETURNS TABLE with OUT Parameters

PostgreSQL does not allow combining RETURNS TABLE(...) and OUT parameters in the same function definition. Both mechanisms define the function's output structure, and using them together creates a conflict that triggers 42P13.

Incorrect:

-- Mixing RETURNS TABLE and OUT parameters
CREATE OR REPLACE FUNCTION get_users(
    OUT user_id INTEGER,
    OUT username TEXT
)
RETURNS TABLE(user_id INTEGER, username TEXT)  -- ❌ Conflict!
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT id, name FROM users WHERE active = TRUE;
END;
$$;

Correct — Use RETURNS TABLE only:

CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(user_id INTEGER, username TEXT)  -- ✅ Clean definition
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT id, name
        FROM users
        WHERE active = TRUE;
END;
$$;

-- Call it like this
SELECT * FROM get_active_users();

Correct — Use OUT parameters only:

CREATE OR REPLACE FUNCTION get_user_by_id(
    IN  p_id    INTEGER,
    OUT username TEXT,
    OUT email    TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT name, email
    INTO   username, email
    FROM   users
    WHERE  id = p_id;
END;
$$;

---

Quick Fix Checklist

When you encounter 42P13, run through this checklist:

-- 1. Verify function definition details after creation
SELECT
    proname                          AS function_name,
    pg_get_function_result(oid)      AS return_type,
    pg_get_function_arguments(oid)   AS arguments,
    lanname                          AS language
FROM pg_proc
JOIN pg_language ON prolang = pg_language.oid
WHERE proname = 'your_function_name';

-- 2. Confirm the language is installed
SELECT lanname, lanpltrusted FROM pg_language;

---

Prevention Tips

Standardize function templates. Keep team-wide boilerplate templates for each function type (trigger, scalar, set-returning). A well-structured template prevents omitting critical clauses like LANGUAGE or using the wrong RETURNS type.

Validate in staging first. Always deploy and test new or modified functions in a development or staging environment before promoting to production. Integrate a schema-validation step into your CI/CD pipeline using tools like pg_dump --schema-only or pgTAP unit tests to catch definition errors automatically before they reach production.

---

Related Errors

Code	Name	Notes
42601	syntax_error	Parser-level syntax mistake, often confused with 42P13
42883	undefined_function	Calling a function that failed to be created due to 42P13
42P16	invalid_table_definition	Table-definition equivalent of 42P13
0A000	feature_not_supported	Using unsupported features in a function definition

---

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