PostgreSQL Function Executed No Return Statement (SQLSTATE 2F005)

When a SQL-language function in PostgreSQL reaches the end of its body without executing a RETURN statement, PostgreSQL raises ERROR: control reached end of function without RETURN with SQLSTATE 2F005 and condition name function_executed_no_return_statement. This error belongs to error class 2F — SQL Routine Exception.

What This Error Means

SQLSTATE class 2F covers errors that arise during the execution of SQL routines (functions and procedures defined in SQL). The specific code 2F005 signals that a function's body completed all of its statements but never reached a RETURN. For non-void functions, a RETURN is mandatory — it is the only way the function can deliver a value back to the caller.

PostgreSQL enforces this at runtime rather than at function-creation time. This means a function body with conditional logic can be CREATEd successfully even when some code paths lack a RETURN, and the error only surfaces when execution reaches one of those incomplete paths.

When this error is raised, the transaction is aborted. Any work done within the function call (and any enclosing transaction that has not been saved via a savepoint) must be rolled back before new work can proceed.

Common Causes

1. Missing RETURN in a non-void SQL function. A function declared with a return type other than void must always execute a RETURN <expression> before it exits. Forgetting to add one — especially in a short utility function — is the most common trigger.

2. Conditional logic that leaves a code path without a RETURN. A function may have IF ... THEN RETURN ...; END IF; but omit an ELSE branch (or a final unconditional RETURN). When the IF condition evaluates to FALSE, execution falls off the end of the function without returning.

3. RETURN inside a loop that never executes. A RETURN placed only inside a FOR or WHILE loop body will be skipped entirely if the loop iterates zero times, leaving the function without a return value.

4. Confusing RETURN with RETURN NEXT / RETURN QUERY in set-returning functions. In set-returning functions (those declared RETURNS SETOF or RETURNS TABLE), rows are emitted via RETURN NEXT or RETURN QUERY, but the function still needs a final bare RETURN; to signal end-of-set. Omitting that final RETURN; will trigger 2F005.

How to Fix function_executed_no_return_statement

1. Add an unconditional RETURN at the end of the function body. The simplest fix is to ensure every code path ends with a RETURN statement.

   CREATE OR REPLACE FUNCTION get_discount(price numeric)
   RETURNS numeric
   LANGUAGE plpgsql AS $$
   BEGIN
     IF price > 100 THEN
       RETURN price * 0.10;
     END IF;
     -- Without the line below, calls where price <= 100 raise 2F005
     RETURN 0;
   END;
   $$;
   

2. Audit all conditional branches. Review every IF/ELSIF/ELSE chain and every CASE expression to confirm each branch terminates with a RETURN. A common pattern is to place a final default RETURN after all conditional blocks as a safety net.

3. Add the final bare RETURN; to set-returning functions.

   CREATE OR REPLACE FUNCTION active_users()
   RETURNS SETOF users
   LANGUAGE plpgsql AS $$
   BEGIN
     RETURN QUERY SELECT * FROM users WHERE active = TRUE;
     RETURN;  -- required to signal end of the result set
   END;
   $$;
   

4. Declare void functions explicitly. If a function is not meant to return a value, declare RETURNS void. PostgreSQL then does not require a RETURN statement (though you may still use RETURN; to exit early).

   CREATE OR REPLACE FUNCTION log_event(msg text)
   RETURNS void
   LANGUAGE plpgsql AS $$
   BEGIN
     INSERT INTO event_log(message, logged_at) VALUES (msg, now());
   END;
   $$;
   

5. Use \df+ or pg_proc to inspect existing functions. If the error appears in a production function whose source you don't immediately have access to, retrieve it via:

   SELECT prosrc
   FROM pg_proc
   WHERE proname = 'your_function_name';
   

Additional Information

• SQLSTATE class 2F (SQL Routine Exception) contains four codes: 2F000 (generic SQL routine exception), 2F002 (modifying_sql_data_not_permitted), 2F003 (prohibited_sql_statement_attempted), and 2F005 (function_executed_no_return_statement). Of these, 2F005 is by far the most frequently encountered in day-to-day development.
• This error is specific to SQL-language and PL/pgSQL functions. C-language functions and internal functions cannot produce it because their return handling is managed at the C level.
• Most PostgreSQL client libraries (libpq, psycopg2, psycopg3, asyncpg, node-postgres) surface this as a regular server-side error with the 2F005 SQLSTATE. No special client-side handling is needed beyond standard error catching.
• PostgreSQL has enforced this behaviour consistently across all currently supported major versions (12 through 17). There is no version where this check was absent.
• Static analysis tools such as plpgsql_check (an open-source PostgreSQL extension) can detect missing RETURN paths at function-definition time, before any runtime error occurs.

Frequently Asked Questions

Why does PostgreSQL allow me to create the function without error, but then fail at call time? PostgreSQL only parses and syntax-checks PL/pgSQL function bodies at creation time; it does not perform full control-flow analysis. Missing-return paths are only detectable at execution, because the outcome of conditional branches depends on runtime data. The plpgsql_check extension adds the static analysis that the core system omits.

Does this error occur for void functions too? No. Functions declared RETURNS void are exempt — PostgreSQL does not require them to execute a RETURN statement. The error is only possible when the function has a concrete return type.

My function always returns in practice, but I still get this error in production. Why? The most common explanation is that an edge-case input triggers a code path that was never hit during testing. For example, a NULL argument may cause an IF condition to evaluate to neither TRUE nor FALSE (three-valued logic), bypassing the branch that contains the RETURN. Add an explicit ELSE or a final unconditional RETURN to cover all cases.

Can I catch this error inside the same function using an exception handler? No. Once the function body exits without a RETURN, control returns to the PostgreSQL executor, which then raises 2F005. There is no opportunity for an exception handler inside the function itself to intercept it, because the error originates after the function body has finished executing.