PostgreSQL 2F005 Error: Causes and Solutions Complete Guide

PostgreSQL Error 2F005: function executed no return statement

PostgreSQL error 2F005 occurs when a PL/pgSQL (or other procedural language) function finishes execution without hitting a RETURN statement. Unlike syntax errors that are caught at function creation time, this error surfaces only at runtime, making it particularly tricky to debug. It typically appears in functions with complex branching logic, exception handlers, or loops where at least one execution path fails to return a value.

---

Top 3 Causes

1. Missing RETURN in a Conditional Branch

The most common cause is an incomplete IF/ELSIF/ELSE structure where at least one branch lacks a RETURN.

Broken code:

CREATE OR REPLACE FUNCTION classify_score(score INT)
RETURNS TEXT AS $$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 80 THEN
        RETURN 'B';
    -- No ELSE! Scores below 80 trigger 2F005
    END IF;
END;
$$ LANGUAGE plpgsql;

Fixed code:

CREATE OR REPLACE FUNCTION classify_score(score INT)
RETURNS TEXT AS $$
BEGIN
    IF score >= 90 THEN
        RETURN 'A';
    ELSIF score >= 80 THEN
        RETURN 'B';
    ELSE
        RETURN 'C'; -- All branches covered
    END IF;
END;
$$ LANGUAGE plpgsql;

---

2. No RETURN Inside an EXCEPTION Block

When an exception is raised and caught, execution jumps to the EXCEPTION block. If that block handles the error (e.g., logs it) but doesn't include a RETURN, the function exits without a return value.

Broken code:

CREATE OR REPLACE FUNCTION parse_integer(input TEXT)
RETURNS INT AS $$
BEGIN
    RETURN input::INT;
EXCEPTION
    WHEN invalid_text_representation THEN
        RAISE NOTICE 'Invalid input: %', input;
        -- No RETURN here → 2F005 when exception fires
END;
$$ LANGUAGE plpgsql;

Fixed code:

CREATE OR REPLACE FUNCTION parse_integer(input TEXT)
RETURNS INT AS $$
BEGIN
    RETURN input::INT;
EXCEPTION
    WHEN invalid_text_representation THEN
        RAISE NOTICE 'Invalid input: %', input;
        RETURN NULL; -- Safe fallback return
END;
$$ LANGUAGE plpgsql;

---

3. Loop That May Never Execute

If a FOR or WHILE loop contains the only RETURN in the function, and the loop body never runs (e.g., empty query result), the function will exit without returning.

Broken code:

CREATE OR REPLACE FUNCTION get_max_price(category_id INT)
RETURNS NUMERIC AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN
        SELECT price FROM products WHERE cat_id = category_id ORDER BY price DESC LIMIT 1
    LOOP
        RETURN rec.price; -- Never reached if no rows found
    END LOOP;
    -- No fallback RETURN → 2F005 on empty result
END;
$$ LANGUAGE plpgsql;

Fixed code:

CREATE OR REPLACE FUNCTION get_max_price(category_id INT)
RETURNS NUMERIC AS $$
DECLARE
    result NUMERIC;
BEGIN
    SELECT MAX(price) INTO result
    FROM products
    WHERE cat_id = category_id;

    RETURN result; -- Returns NULL if no rows, never triggers 2F005
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Checklist

When you encounter 2F005, go through this checklist:

1. Trace every code path — draw a flowchart if needed; every terminal node must have a RETURN.
2. Add a defensive RETURN at the end of the function body as a safety net.
3. Check all EXCEPTION blocks — each one needs its own RETURN or RAISE that re-throws.

-- Defensive pattern: always end with a fallback RETURN
CREATE OR REPLACE FUNCTION safe_lookup(user_id INT)
RETURNS TEXT AS $$
DECLARE
    result TEXT;
BEGIN
    SELECT username INTO result FROM users WHERE id = user_id;
    RETURN COALESCE(result, 'unknown'); -- Handles NULL gracefully
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Unexpected error: %', SQLERRM;
        RETURN 'error'; -- Always returns something
END;
$$ LANGUAGE plpgsql;

---

Prevention Tips

1. Adopt a "defensive RETURN" coding standard.
Always place a final RETURN statement at the very end of your function body, even if logic above should always reach a RETURN first. This acts as a last-resort safety net and clearly communicates intent to future maintainers.

2. Automate boundary testing with pgTAP.
Use pgTAP to write unit tests that cover NULL inputs, empty result sets, and exception-triggering inputs before deploying any function. Integrate these tests into your CI/CD pipeline so that every code change is automatically validated against edge cases — catching 2F005 in development, not production.

-- Simple pgTAP test covering edge cases
SELECT plan(2);
SELECT is(get_max_price(999), NULL, 'Non-existent category returns NULL safely');
SELECT is(classify_score(70), 'C',  'Score below 80 hits the ELSE branch');
SELECT * FROM finish();

---

Related Errors

Error Code	Name	Notes
2F000	sql_routine_exception	Parent class of 2F005
2F002	modifying_sql_data_not_permitted	Writing data in a read-only function context
2F003	prohibited_sql_statement_attempted	Disallowed SQL inside a function
42P13	invalid_function_definition	Caught at CREATE FUNCTION time, unlike 2F005 which is runtime-only

---

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