PL/pgSQL RETURN

Master PL/pgSQL RETURN statement — scalar returns from CREATE FUNCTION, the required LANGUAGE plpgsql clause, intermediate variables, recursive functions like factorial with proper BIGINT, multiple return paths in IF/ELSIF chains, and composite returns via %ROWTYPE. Anonymous DO blocks have no RETURN — that's a function-only construct.

The RETURN statement ends a function and sends a value back to the caller. The exact form depends on what the function returns:

• Scalar return — RETURNS integer, RETURNS text, etc. Use RETURN expression.
• Composite return — RETURNS table_name or RETURNS some_record_type. Use RETURN row_variable or RETURN ROW(...).
• Set-returning — RETURNS SETOF type or RETURNS TABLE(...). Use RETURN NEXT / RETURN QUERY (covered in later modules).

This page covers scalar and composite returns. Set-returning patterns belong in the Functions module that comes later in this series.

Inside a function with a scalar return type:

The RETURN statement immediately ends the function and sends expression back to the caller. Anything after a RETURN that's executed is unreachable code (Postgres won't catch this — it's on you).

The simplest case: a function that adds two numbers and returns the result:

CREATE OR REPLACE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

SELECT add_numbers(10, 20) AS result;

A function with intermediate variables. The body computes the sum, then divides by count, then returns the average:

CREATE OR REPLACE FUNCTION get_average(a NUMERIC, b NUMERIC, c NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
    avg   NUMERIC;
BEGIN
    total := a + b + c;
    avg   := total / 3;
    RETURN avg;
END;
$$ LANGUAGE plpgsql;

SELECT get_average(80, 90, 100) AS class_average;

A function can call itself. The classic factorial demonstrates recursion plus a base case via RETURN 1 to terminate:

CREATE OR REPLACE FUNCTION factorial(n INTEGER)
RETURNS BIGINT AS $$
BEGIN
    -- Guard against negative input
    IF n < 0 THEN
        RAISE EXCEPTION 'factorial undefined for negative input: %', n;
    END IF;

    -- Base case
    IF n <= 1 THEN
        RETURN 1;
    END IF;

    -- Recursive case
    RETURN n * factorial(n - 1);
END;
$$ LANGUAGE plpgsql;

SELECT factorial(5)  AS five_fact,
       factorial(10) AS ten_fact;

Note RETURNS BIGINT — factorials grow fast. factorial(20) is already in the hundreds of trillions; INTEGER would overflow well before that.

A function can have several RETURN statements. The first one reached terminates the function:

CREATE OR REPLACE FUNCTION grade_letter(score INTEGER)
RETURNS CHAR AS $$
BEGIN
    IF score >= 90 THEN RETURN 'A';
    ELSIF score >= 80 THEN RETURN 'B';
    ELSIF score >= 70 THEN RETURN 'C';
    ELSIF score >= 60 THEN RETURN 'D';
    ELSE RETURN 'F';
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT grade_letter(95) AS top,
       grade_letter(72) AS mid,
       grade_letter(45) AS low;

A function can return an entire row by declaring RETURNS table_name and using a %ROWTYPE variable:

CREATE OR REPLACE FUNCTION get_employee(p_id INTEGER)
RETURNS employees AS $$
DECLARE
    emp employees%ROWTYPE;
BEGIN
    SELECT *
    INTO   emp
    FROM   employees
    WHERE  employee_id = p_id;

    RETURN emp;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_employee(1);

The function returns one row of the employees table. Calling it via SELECT * FROM get_employee(1) expands the composite into individual columns.

Two ways out of a function:

For a "this input is invalid" case (negative factorial input, division by zero, etc.) RAISE EXCEPTION is the right tool — RETURN-ing a sentinel value like -1 is fragile because callers may forget to check.

1. Always include LANGUAGE plpgsql — without it, Postgres tries to parse the body as plain SQL.
2. Use CREATE OR REPLACE FUNCTION for deployment — re-running the script updates the function rather than erroring.
3. Use sized return types when meaningful — BIGINT for factorials, NUMERIC(p,s) for monetary calculations, etc.
4. Validate inputs with RAISE EXCEPTION rather than returning sentinel values — callers can't forget to check exceptions.
5. Avoid unreachable code after RETURN — Postgres won't catch it but it confuses readers.
6. For composite returns, prefer RETURNS table_name when the shape matches a real table; it gives clearer naming and self-documents.

• RETURN expression ends a function and sends the value back to the caller.
• Anonymous DO blocks have no caller — RETURN belongs only inside CREATE FUNCTION definitions.
• LANGUAGE plpgsql is required at the end of CREATE FUNCTION — omitting it breaks parsing.
• Functions can have multiple RETURN statements; the first one reached terminates the function.
• For composite (row) returns, declare RETURNS table_name and return a %ROWTYPE variable.
• For invalid inputs, prefer RAISE EXCEPTION over returning sentinel values.