RETURN and RETURN NEXT in PostgreSQL - Step-by-Step Execution

Concept Flow - RETURN and RETURN NEXT

Start Function

↓

Execute Statements

↓

RETURN NEXT row

↓

More Statements?

↓

Loop

↓

Function Ends, returns all rows

The function runs statements, each RETURN NEXT adds a row to the output. RETURN ends the function and returns all collected rows.

Execution Sample

PostgreSQL

CREATE FUNCTION get_numbers() RETURNS SETOF INT AS $$
BEGIN
  RETURN NEXT 1;
  RETURN NEXT 2;
  RETURN 3;
END;
$$ LANGUAGE plpgsql;

This function returns a set of integers by adding rows with RETURN NEXT and then ends with RETURN.

Execution Table

Step	Action	Output Rows Collected	Function State	Notes
1	Start function execution	[]	Running	Function begins
2	RETURN NEXT 1	[1]	Running	Row 1 added to output set
3	RETURN NEXT 2	[1, 2]	Running	Row 2 added to output set
4	RETURN 3	[1, 2, 3]	Ending	Row 3 added and function ends
5	Function returns all collected rows	[1, 2, 3]	Ended	Output sent to caller

💡 Function ends after RETURN statement, returning all collected rows.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Output Rows	[]	[1]	[1, 2]	[1, 2, 3]	[1, 2, 3]

Key Moments - 2 Insights

Why does RETURN NEXT add a row but not end the function?

RETURN NEXT adds a row to the output set but keeps the function running to add more rows, as shown in steps 2 and 3 of the execution_table.

What happens when RETURN is used inside a set-returning function?

RETURN adds the final row and immediately ends the function, returning all collected rows, as seen in step 4 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output rows collected after step 3?

A [1]

B [1, 2]

C [1, 2, 3]

D []

💡 Hint

Check the 'Output Rows Collected' column at step 3 in the execution_table.

At which step does the function end and return all rows?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Look for the step where 'Function State' changes to 'Ending' in the execution_table.

If we remove the RETURN statement at step 4, what happens to the output?

A Function returns no rows

B Function runs forever

C Only rows from RETURN NEXT are returned

D An error occurs

💡 Hint

Without RETURN, the function does not end early but returns rows added by RETURN NEXT, as shown in variable_tracker.

Concept Snapshot

RETURN NEXT adds a row to the output set but keeps the function running.
RETURN ends the function and returns all collected rows.
Use RETURN NEXT multiple times to build a set.
Use RETURN once to finish and return the set.
Common in set-returning functions in PostgreSQL.

Full Transcript

In PostgreSQL, functions that return multiple rows use RETURN NEXT to add each row to the output set without ending the function. The function continues running, allowing more rows to be added. When the function reaches a RETURN statement, it adds the final row and immediately ends, returning all collected rows to the caller. This process lets you build a set of rows step-by-step inside the function. The execution table shows each step: starting the function, adding rows with RETURN NEXT, and ending with RETURN. The variable tracker shows how the output rows list grows after each RETURN NEXT and RETURN. Beginners often confuse RETURN NEXT with RETURN; RETURN NEXT adds rows but does not end the function, while RETURN ends it. The visual quiz tests understanding of these steps and their effects on output.