PostgreSQL 42P13 Error: Causes and Solutions Complete Guide

PostgreSQL Error 42P13: Invalid Function Definition

PostgreSQL error code 42P13 (invalid_function_definition) is raised when the server detects a logical or structural problem in a CREATE FUNCTION or CREATE PROCEDURE statement that goes beyond a simple syntax error. Unlike 42601 (syntax error), this error specifically targets violations of PostgreSQL's function definition rules — such as mismatched return types, invalid language declarations, or improper use of RETURN statements. Understanding the root cause quickly is essential, as this error can surface in both development and production deployments.

---

Top 3 Causes and Fixes

1. Mismatch Between RETURNS Clause and Actual Return Value

The most common cause is declaring one return type but returning a value of a completely different type inside the function body.

-- ❌ Wrong: Declared RETURNS INTEGER but returning TEXT
CREATE OR REPLACE FUNCTION get_label(p_id INT)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN 'active';  -- ERROR: cannot return text as integer
END;
$$;

-- ✅ Fixed: Match the return type to the actual value
CREATE OR REPLACE FUNCTION get_label(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN 'active';
END;
$$;

-- ✅ Fixed for set-returning functions: Use RETURN QUERY
CREATE OR REPLACE FUNCTION get_active_users()
RETURNS TABLE(user_id INT, username TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
        SELECT u.user_id, u.username
        FROM users u
        WHERE u.is_active = TRUE;
END;
$$;

---

2. Invalid or Unavailable LANGUAGE Declaration

Specifying a procedural language that either has a typo or is not installed on the server will immediately trigger 42P13.

-- Check installed languages first
SELECT lanname FROM pg_language;

-- Install missing language if needed (requires superuser)
CREATE EXTENSION IF NOT EXISTS plpgsql;
CREATE EXTENSION IF NOT EXISTS plpython3u;

-- ❌ Wrong: Typo in language name
CREATE OR REPLACE FUNCTION compute_tax(p_amount NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsq  -- typo: missing 'l'
AS $$
BEGIN
    RETURN p_amount * 0.15;
END;
$$;

-- ✅ Fixed: Correct language name
CREATE OR REPLACE FUNCTION compute_tax(p_amount NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_amount * 0.15;
END;
$$;

---

3. Incorrect Use of RETURN in VOID or OUT Parameter Functions

Using a value-returning RETURN statement inside a RETURNS VOID function, or mixing OUT parameters with an explicit RETURNS clause, are both common triggers for 42P13.

-- ❌ Wrong: RETURNS VOID cannot return a value
CREATE OR REPLACE FUNCTION archive_record(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE records SET archived = TRUE WHERE id = p_id;
    RETURN TRUE;  -- ERROR: cannot return value from void function
END;
$$;

-- ✅ Fixed: Use bare RETURN or omit it entirely
CREATE OR REPLACE FUNCTION archive_record(p_id INT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE records SET archived = TRUE WHERE id = p_id;
    -- RETURN; is optional here
END;
$$;

-- ❌ Wrong: Mixing OUT parameters with RETURNS clause
CREATE OR REPLACE FUNCTION get_summary(
    IN p_id INT,
    OUT o_total INT,
    OUT o_avg NUMERIC
)
RETURNS INTEGER  -- ERROR: redundant with OUT parameters
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), AVG(amount) INTO o_total, o_avg
    FROM orders WHERE customer_id = p_id;
END;
$$;

-- ✅ Fixed: Remove the explicit RETURNS clause
CREATE OR REPLACE FUNCTION get_summary(
    IN p_id INT,
    OUT o_total INT,
    OUT o_avg NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*), AVG(amount) INTO o_total, o_avg
    FROM orders WHERE customer_id = p_id;
END;
$$;

---

Quick Fix Checklist

• Verify the RETURNS type matches what the function body actually returns
• Run SELECT lanname FROM pg_language; before using any procedural language
• Never mix OUT parameters with an explicit RETURNS type clause
• Use RETURN QUERY for set-returning functions, not a plain RETURN
• Test function logic using an anonymous DO block before formalizing it

---

Prevention Tips

Document function signatures before writing the body. Define the input types, return type, and language upfront. Use a DO block to prototype and validate your logic before creating the actual function.

-- Prototype logic safely before creating the function
DO $$
DECLARE v_result NUMERIC;
BEGIN
    SELECT AVG(salary) INTO v_result FROM employees WHERE dept_id = 10;
    RAISE NOTICE 'Avg salary: %', v_result;
END;
$$;

Use pg_proc to audit existing functions and catch definition inconsistencies early in your CI/CD pipeline.

SELECT proname, prorettype::regtype AS return_type, lanname AS language
FROM pg_proc p
JOIN pg_language l ON p.prolang = l.oid
WHERE proname = 'your_function_name';

Related errors: 42601 (syntax error inside function body), 42883 (function not found after failed creation), 0A000 (unsupported feature in the current PostgreSQL version).

---

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