How to Use RETURN in PL/pgSQL Functions

In PL/pgSQL, use the RETURN statement to send a value back from a function. For functions returning a single value, use RETURN value;. For functions returning sets, use RETURN NEXT or RETURN QUERY.

Syntax

The RETURN statement in PL/pgSQL is used to exit a function and optionally return a value. The syntax depends on the function's return type:

• Scalar return: RETURN expression; returns a single value.
• Set-returning functions: RETURN NEXT expression; adds a row to the result set, and RETURN QUERY query; returns rows from a query.

sql

RETURN expression;

-- For set-returning functions:
RETURN NEXT expression;
RETURN QUERY SELECT ...;

Example

This example shows a simple PL/pgSQL function that returns the square of an integer using RETURN. It demonstrates returning a single scalar value.

sql

CREATE OR REPLACE FUNCTION square_number(n integer) RETURNS integer AS $$
BEGIN
  RETURN n * n;
END;
$$ LANGUAGE plpgsql;

-- Usage:
SELECT square_number(5);

Output

 square_number 
---------------
            25
(1 row)

Common Pitfalls

Common mistakes when using RETURN in PL/pgSQL include:

• Using RETURN without a value in functions that expect a return value.
• Forgetting to use RETURN NEXT or RETURN QUERY in set-returning functions.
• Placing RETURN too early, which prevents the rest of the function from running.

sql

/* Wrong: Missing return value in scalar function */
CREATE OR REPLACE FUNCTION bad_func() RETURNS integer AS $$
BEGIN
  RETURN; -- Error: no value returned
END;
$$ LANGUAGE plpgsql;

/* Correct: Return a value */
CREATE OR REPLACE FUNCTION good_func() RETURNS integer AS $$
BEGIN
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

Quick Reference

Usage	Description	Example
RETURN expression;	Returns a single value and exits the function.	RETURN 42;
RETURN NEXT expression;	Adds a row to the result set in set-returning functions.	RETURN NEXT row_variable;
RETURN QUERY query;	Returns rows from a query in set-returning functions.	RETURN QUERY SELECT * FROM table;
RETURN;	Exits a void function without returning a value.	RETURN;

Key Takeaways

Use RETURN with a value to output from scalar PL/pgSQL functions.

Use RETURN NEXT or RETURN QUERY to return rows in set-returning functions.

Always match RETURN usage to the function's declared return type.

Avoid placing RETURN too early to ensure all code runs as intended.

Void functions can use RETURN without a value to exit early.