📋

Description

This error signifies that a PostgreSQL function, procedure, or trigger was executed but did not complete its operation by returning an expected value. It typically arises when a function's logic fails to reach a `RETURN` statement or if the `RETURN` type declared for the function doesn't align with all possible execution paths.

💬

Error Message

function executed no return statement

🔍

Known Causes

3 known causes

⚠️

Missing RETURN Statement

The function's code path does not include a `RETURN` statement, or all possible execution paths fail to reach one, despite the function being declared to return a value.

⚠️

Unhandled Conditional Paths

Within complex conditional logic (e.g., `IF/ELSE IF`, `CASE` statements), some branches may not include a `RETURN` statement, causing this error if such an unhandled path is executed.

⚠️

Mismatched Function Signature

The function is declared with a `RETURNS` type that requires an explicit value return, but its internal logic was designed without this expectation, leading to the omission of `RETURN`.

🛠️

Solutions

3 solutions available

1. Ensure a RETURN Statement Exists in All Code Paths easy

Add a RETURN statement to every possible execution branch within your function.

1

Review your PostgreSQL function's code. Identify all conditional blocks (IF, CASE, ELSIF) and loops (LOOP, WHILE, FOR).

Example of a function missing a return in an IF block:

sql
CREATE OR REPLACE FUNCTION get_user_status(user_id INT) RETURNS VARCHAR AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM users WHERE id = user_id AND is_active = TRUE) THEN
    RETURN 'Active';
  END IF;
  -- Missing return if user is not active
END;
$$ LANGUAGE plpgsql;

2

For each code path that doesn't explicitly return a value, add a RETURN statement. This could be a default return value or a specific condition.

Example of a corrected function:

sql
CREATE OR REPLACE FUNCTION get_user_status(user_id INT) RETURNS VARCHAR AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM users WHERE id = user_id AND is_active = TRUE) THEN
    RETURN 'Active';
  ELSE
    RETURN 'Inactive'; -- Added return for the ELSE path
  END IF;
END;
$$ LANGUAGE plpgsql;

3

If your function is designed to return NULL in certain cases, explicitly return NULL.

sql
CREATE OR REPLACE FUNCTION get_user_email(user_id INT) RETURNS VARCHAR AS $$
BEGIN
  IF user_id IS NULL THEN
    RETURN NULL;
  END IF;
  -- ... other logic ...
  RETURN email FROM users WHERE id = user_id;
END;
$$ LANGUAGE plpgsql;

2. Add a Default RETURN Statement at the End of the Function easy

Include a final RETURN statement that covers any execution paths not explicitly handled by earlier conditions.

1

At the very end of your function's `BEGIN...END` block, before the final `END;`, add a `RETURN` statement.

sql
CREATE OR REPLACE FUNCTION process_data(input_value INT) RETURNS INT AS $$
BEGIN
  IF input_value > 10 THEN
    RETURN input_value * 2;
  ELSIF input_value < 0 THEN
    RETURN 0;
  END IF;

  -- Default return if none of the above conditions are met
  RETURN input_value; 
END;
$$ LANGUAGE plpgsql;

2

Consider what the appropriate default return value should be. This could be a specific value, NULL, or a value derived from the function's parameters.

3. Define the Function's Return Type Explicitly medium

Ensure the `RETURNS` clause in your function definition accurately reflects the data type being returned.

1

Examine the `RETURNS` clause of your function definition. For example, `RETURNS INT`, `RETURNS VARCHAR`, `RETURNS SETOF my_table`, etc.

sql
-- Incorrect definition if the function actually returns a boolean
CREATE OR REPLACE FUNCTION is_valid(val INT) RETURNS INT AS $$
BEGIN
  IF val > 0 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;
$$ LANGUAGE plpgsql;

2

Verify that every `RETURN` statement within the function's body returns a value compatible with the declared `RETURNS` type.

sql
-- Corrected definition to match the actual return values
CREATE OR REPLACE FUNCTION is_valid(val INT) RETURNS INT AS $$
BEGIN
  IF val > 0 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END;
$$ LANGUAGE plpgsql;

3

If the function is supposed to return nothing (e.g., for side effects only), declare it as `RETURNS VOID`.

sql
CREATE OR REPLACE FUNCTION log_event(message TEXT) RETURNS VOID AS $$
BEGIN
  INSERT INTO event_log (message) VALUES (message);
  -- No explicit RETURN needed for VOID functions
END;
$$ LANGUAGE plpgsql;

🔗

Related Errors

5 related errors

PostgreSQL Error 2F005: Function Missing Return Value