RETURN and RETURN NEXT in PostgreSQL

Introduction

RETURN and RETURN NEXT let you send results back from a function in PostgreSQL. They help you give one or many rows as output.

When you want a function to send back a single result.

When you want a function to send back multiple rows one by one.

When you write a function that returns a set of rows instead of just one.

When you want to build a custom query inside a function and return its results.

Syntax

PostgreSQL

RETURN expression;
RETURN NEXT expression;

RETURN sends back a single result and ends the function.

RETURN NEXT adds one row to the output but keeps the function running to add more rows.

Examples

Returns the single value 5 and ends the function.

PostgreSQL

RETURN 5;

Adds one row to the output but the function can continue to add more rows.

PostgreSQL

RETURN NEXT row_variable;

Returns the numbers 1, 2, and 3 as separate rows.

PostgreSQL

FOR i IN 1..3 LOOP
  RETURN NEXT i;
END LOOP;
RETURN;

Sample Program

This function returns three numbers as separate rows using RETURN NEXT. The final RETURN ends the function.

PostgreSQL

CREATE OR REPLACE FUNCTION get_numbers() RETURNS SETOF integer AS $$
BEGIN
  RETURN NEXT 10;
  RETURN NEXT 20;
  RETURN NEXT 30;
  RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_numbers();

Important Notes

Use RETURN NEXT inside loops to return many rows one at a time.

Use RETURN alone to return a single value or to end a function that returns multiple rows.

Functions that return SETOF need RETURN NEXT to add rows to the result set.

Summary

RETURN sends back one result and stops the function.

RETURN NEXT adds a row to the output but keeps the function running.

Use RETURN NEXT to return multiple rows from a function.