Functions returning SETOF in PostgreSQL - Step-by-Step Execution

Concept Flow - Functions returning SETOF

Call function with parameters

↓

Function starts execution

↓

Loop or query returns multiple rows

↓

Each row is yielded one by one

↓

Result set accumulates

↓

Function ends, returns full set

The function is called, executes a query or loop that yields multiple rows, and returns them as a set.

Execution Sample

PostgreSQL

CREATE FUNCTION get_even_numbers(max_num INT) RETURNS SETOF INT AS $$
BEGIN
  FOR i IN 2..max_num BY 2 LOOP
    RETURN NEXT i;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

This function returns all even numbers from 2 up to the given max_num.

Execution Table

Step	i	Condition	Action	Output
1	2	2 <= max_num	RETURN NEXT 2	2
2	4	4 <= max_num	RETURN NEXT 4	2, 4
3	6	6 <= max_num	RETURN NEXT 6	2, 4, 6
4	8	8 <= max_num	RETURN NEXT 8	2, 4, 6, 8
5	10	10 <= max_num	RETURN NEXT 10	2, 4, 6, 8, 10
6	12	12 <= max_num is False	Exit loop	2, 4, 6, 8, 10

💡 Loop ends when i exceeds max_num, function returns accumulated set

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
i	undefined	2	4	6	8	10	12
Output Set	empty	[2]	[2,4]	[2,4,6]	[2,4,6,8]	[2,4,6,8,10]	[2,4,6,8,10]

Key Moments - 2 Insights

Why does the function use RETURN NEXT instead of RETURN?

RETURN NEXT adds one row to the output set without ending the function, allowing multiple rows to be returned as shown in execution_table rows 1-5.

What happens when the loop condition becomes false?

The loop exits (see execution_table row 6), and the function returns the full set of rows collected so far.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'i' at step 3?

A 4

B 8

C 6

D 10

💡 Hint

Check the 'i' column in execution_table row 3.

At which step does the loop condition become false and the function stops adding rows?

A Step 5

B Step 6

C Step 4

D Step 7

💡 Hint

Look at the 'Condition' and 'Action' columns in execution_table row 6.

If max_num was 6 instead of 10, how many rows would the function return?

A 3

B 5

C 6

D 2

💡 Hint

Check variable_tracker for values of 'i' up to 6.

Concept Snapshot

CREATE FUNCTION name(params) RETURNS SETOF type AS $$
BEGIN
  LOOP or query yielding rows
  RETURN NEXT row; -- adds row to output
END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

Use RETURN NEXT to return multiple rows one by one.
Function returns all rows as a set after finishing.

Full Transcript

Functions returning SETOF in PostgreSQL allow returning multiple rows from a single function call. The function runs a loop or query that yields rows one by one using RETURN NEXT. Each RETURN NEXT adds a row to the output set without ending the function. When the loop or query finishes, the function returns the full set of rows. This is useful to return lists or tables from functions. The example function get_even_numbers returns all even numbers up to a given maximum by looping from 2 to max_num in steps of 2, returning each number with RETURN NEXT. The execution table shows each step where a number is added to the output. The variable tracker shows how the loop variable and output set change over time. Key moments include understanding why RETURN NEXT is used instead of RETURN, and when the loop ends. The visual quiz tests understanding of loop steps, termination, and output size changes.