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.

Practice

1. What does the RETURN statement do in a PostgreSQL function?

easy

A. It loops through all rows in a table.

B. It adds a row to the output but keeps the function running.

C. It sends back one result and stops the function.

D. It creates a new table in the database.

Solution

1. Step 1: Understand the purpose of RETURN

   The RETURN statement immediately sends a single result back to the caller and ends the function execution.

2. Step 2: Compare with RETURN NEXT

   Unlike RETURN NEXT, which adds rows and continues, RETURN stops the function after sending one result.

3. Final Answer:

   It sends back one result and stops the function. -> Option C

4. Quick Check:

   RETURN = sends one result and stops [OK]

Hint: RETURN sends one result and stops function immediately [OK]

Common Mistakes:

• Confusing RETURN with RETURN NEXT
• Thinking RETURN returns multiple rows
• Assuming RETURN continues function execution

2. Which of the following is the correct syntax to return multiple rows from a PostgreSQL function?

easy

A. Use RETURN NEXT inside a loop to add each row to the output.

B. Use RETURN ROW to return multiple rows.

C. Use RETURN ALL to return all rows at once.

D. Use RETURN inside a loop to return each row.

Solution

1. Step 1: Identify how to return multiple rows

   RETURN NEXT is used inside loops to add each row to the output without stopping the function.

2. Step 2: Check syntax options

   RETURN alone stops the function after one row; RETURN ALL and RETURN ROW are not valid PostgreSQL syntax.

3. Final Answer:

   Use RETURN NEXT inside a loop to add each row to the output. -> Option A

4. Quick Check:

   RETURN NEXT = add rows, keep running [OK]

Hint: Use RETURN NEXT in loops to return multiple rows [OK]

Common Mistakes:

• Using RETURN instead of RETURN NEXT for multiple rows
• Assuming RETURN ALL or RETURN ROW exist
• Not placing RETURN NEXT inside a loop

3. Consider this PostgreSQL function snippet:

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

What will be the output when this function is called?

medium

A. Syntax error due to RETURN NEXT usage

B. [3]

C. No output, function ends without returning

D. [1, 2, 3]

Solution

1. Step 1: Analyze the loop with RETURN NEXT

   The loop runs from 1 to 3, and each iteration adds the current number to the output using RETURN NEXT.

2. Step 2: Understand the final RETURN

   The final RETURN ends the function after all rows have been added, so the output is all numbers collected.

3. Final Answer:

   [1, 2, 3] -> Option D

4. Quick Check:

   RETURN NEXT adds rows; final RETURN stops function [OK]

Hint: RETURN NEXT inside loop collects rows; final RETURN stops [OK]

Common Mistakes:

• Thinking only last value is returned
• Confusing RETURN NEXT with RETURN
• Expecting syntax error from RETURN NEXT

4. You wrote this function:

CREATE FUNCTION test_func() RETURNS SETOF integer AS $$
DECLARE
  i integer := 1;
BEGIN
  RETURN i;
  RETURN NEXT i + 1;
END;
$$ LANGUAGE plpgsql;

What is the problem with this function?

medium

A. RETURN NEXT is used after RETURN, so it never executes.

B. RETURN cannot be used in functions returning SETOF.

C. Variable i is not initialized properly.

D. Function lacks a LOOP to return multiple rows.

Solution

1. Step 1: Check order of RETURN and RETURN NEXT

   RETURN immediately ends the function, so RETURN NEXT after it never runs.

2. Step 2: Understand function behavior

   Because RETURN is first, only one row is returned and the rest is ignored.

3. Final Answer:

   RETURN NEXT is used after RETURN, so it never executes. -> Option A

4. Quick Check:

   RETURN stops function; code after it is skipped [OK]

Hint: RETURN stops function; code after it won't run [OK]

Common Mistakes:

• Assuming RETURN NEXT runs after RETURN
• Thinking RETURN can't be used in SETOF functions
• Believing variable initialization causes error

5. You want to create a function that returns all even numbers from 1 to 10. Which of these function bodies correctly uses RETURN NEXT and RETURN to achieve this?

hard

A. FOR i IN 1..10 LOOP
IF i % 2 = 0 THEN
RETURN i;
END IF;
END LOOP;

B. FOR i IN 1..10 LOOP
IF i % 2 = 0 THEN
RETURN NEXT i;
END IF;
END LOOP;
RETURN;

C. FOR i IN 1..10 LOOP
RETURN NEXT i;
END LOOP;
RETURN;

D. RETURN NEXT 2;
RETURN 4;
RETURN NEXT 6;
RETURN NEXT 8;
RETURN NEXT 10;
RETURN;

Solution

1. Step 1: Identify correct use of RETURN NEXT in loop with condition

   FOR i IN 1..10 LOOP
     IF i % 2 = 0 THEN
       RETURN NEXT i;
     END IF;
   END LOOP;
   RETURN;

   loops 1 to 10, adds only even numbers with RETURN NEXT, then ends with RETURN.

2. Step 2: Check other options for errors

   FOR i IN 1..10 LOOP
     IF i % 2 = 0 THEN
       RETURN i;
     END IF;
   END LOOP;

   uses RETURN inside loop, stopping after first even number.

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

   returns all numbers, not just even.

   RETURN NEXT 2;
   RETURN 4;
   RETURN NEXT 6;
   RETURN NEXT 8;
   RETURN NEXT 10;
   RETURN;

   mixes RETURN NEXT and RETURN; RETURN 4 adds 4 and stops, returning only [2,4].

3. Final Answer:

   FOR i IN 1..10 LOOP IF i % 2 = 0 THEN RETURN NEXT i; END IF; END LOOP; RETURN; -> Option B

4. Quick Check:

   RETURN NEXT adds rows conditionally; RETURN ends function [OK]

Hint: Use RETURN NEXT inside loop with condition; end with RETURN [OK]

Common Mistakes:

• Using RETURN inside loop stopping early
• Returning all numbers instead of filtering
• Mixing RETURN and RETURN NEXT causing early termination