PostgreSQL 2F005 Error: Causes and Solutions Complete Guide

PostgreSQL Error 2F005: function executed no return statement

PostgreSQL error 2F005 occurs when a PL/pgSQL function (or a function written in another procedural language) reaches the end of its execution without hitting a RETURN statement, despite having a non-void return type. This is a runtime error, meaning PostgreSQL cannot always catch it at function creation time — it only surfaces when the specific code path without a RETURN is actually executed in production.

---

Top 3 Causes

1. Missing RETURN in a Conditional Branch

The most common cause: an IF/ELSIF/ELSE chain where one or more branches lack a RETURN statement.

-- BROKEN: No RETURN when score < 70
CREATE OR REPLACE FUNCTION get_grade(p_score INT)
RETURNS TEXT AS $$
BEGIN
    IF p_score >= 90 THEN
        RETURN 'A';
    ELSIF p_score >= 80 THEN
        RETURN 'B';
    ELSIF p_score >= 70 THEN
        RETURN 'C';
    -- If score is 69 or below, 2F005 is raised!
    END IF;
END;
$$ LANGUAGE plpgsql;

-- FIXED: Always cover all branches
CREATE OR REPLACE FUNCTION get_grade(p_score INT)
RETURNS TEXT AS $$
BEGIN
    IF p_score >= 90 THEN
        RETURN 'A';
    ELSIF p_score >= 80 THEN
        RETURN 'B';
    ELSIF p_score >= 70 THEN
        RETURN 'C';
    ELSE
        RETURN 'F'; -- Covers all remaining cases
    END IF;
END;
$$ LANGUAGE plpgsql;

2. Missing RETURN in an EXCEPTION Block

When a function has an EXCEPTION handler, every branch of that handler must also return a value. Forgetting to add RETURN inside the exception block is a very common oversight.

-- BROKEN: EXCEPTION block has no RETURN
CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE WARNING 'Cannot divide by zero';
        -- Missing RETURN here → 2F005!
END;
$$ LANGUAGE plpgsql;

-- FIXED: Every exception path returns a value
CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN a / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE WARNING 'Cannot divide by zero, returning NULL';
        RETURN NULL; -- Explicit return in exception handler
    WHEN OTHERS THEN
        RAISE WARNING 'Unexpected error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

3. Loop That Never Executes

When a function relies on a FOR loop to return a value, but the query returns no rows, the loop body never runs — leaving the function with no RETURN to execute.

-- BROKEN: If query returns no rows, 2F005 is raised
CREATE OR REPLACE FUNCTION get_top_user(p_dept INT)
RETURNS TEXT AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT username FROM users WHERE dept_id = p_dept AND active = TRUE
    LOOP
        RETURN rec.username; -- Never reached if no rows exist
    END LOOP;
    -- No fallback RETURN!
END;
$$ LANGUAGE plpgsql;

-- FIXED option 1: Add a fallback RETURN after the loop
CREATE OR REPLACE FUNCTION get_top_user(p_dept INT)
RETURNS TEXT AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT username FROM users WHERE dept_id = p_dept AND active = TRUE
    LOOP
        RETURN rec.username;
    END LOOP;
    RETURN NULL; -- Fallback when loop does not execute
END;
$$ LANGUAGE plpgsql;

-- FIXED option 2 (preferred): Use SELECT INTO for cleaner logic
CREATE OR REPLACE FUNCTION get_top_user(p_dept INT)
RETURNS TEXT AS $$
DECLARE
    v_username TEXT;
BEGIN
    SELECT username INTO v_username
    FROM users
    WHERE dept_id = p_dept AND active = TRUE
    LIMIT 1;
    RETURN v_username; -- Returns NULL automatically if no row found
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Checklist

• Always use a fallback RETURN at the very end of your function body as a safety net.
• Prefer the Single Exit Point pattern: assign results to a variable and use one RETURN at the end instead of multiple RETURN statements scattered through the code.
• Review every EXCEPTION block to ensure it includes its own RETURN or RAISE statement.
• Test with empty datasets and boundary values before deploying to production.

---

Prevention Tips

Use the Single Exit Point pattern to structurally eliminate this error:

CREATE OR REPLACE FUNCTION calculate_discount(p_amount NUMERIC, p_tier TEXT)
RETURNS NUMERIC AS $$
DECLARE
    v_discount NUMERIC := 0; -- Default value ensures RETURN always has data
BEGIN
    IF p_tier = 'GOLD' THEN
        v_discount := p_amount * 0.20;
    ELSIF p_tier = 'SILVER' THEN
        v_discount := p_amount * 0.10;
    ELSIF p_tier = 'BRONZE' THEN
        v_discount := p_amount * 0.05;
    END IF;
    RETURN v_discount; -- Single, guaranteed exit point
END;
$$ LANGUAGE plpgsql;

Write unit tests with pgTAP covering all branches, especially empty-result and exception scenarios, and integrate them into your CI/CD pipeline to catch missing RETURN paths before they reach production.

---

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