RETURN and RETURN NEXT in PostgreSQL - Time & Space Complexity

Time Complexity: RETURN and RETURN NEXT

O(n)

Understanding Time Complexity

When using RETURN and RETURN NEXT in PostgreSQL functions, it's important to understand how the function's execution time changes as it processes more rows.

We want to see how the number of rows returned affects the time the function takes.

Scenario Under Consideration

Analyze the time complexity of this PostgreSQL function using RETURN NEXT.

CREATE FUNCTION get_numbers("limit" INT) RETURNS SETOF INT AS $$
DECLARE
  i INT := 1;
BEGIN
  WHILE i <= "limit" LOOP
    RETURN NEXT i;
    i := i + 1;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

This function returns numbers from 1 up to the given limit, one by one using RETURN NEXT.

Identify Repeating Operations

Look at what repeats in this function.

• Primary operation: The WHILE loop that calls RETURN NEXT for each number.
• How many times: Exactly as many times as the input limit value.

How Execution Grows With Input

As the limit grows, the function does more RETURN NEXT calls.

Input Size (limit)	Approx. Operations (RETURN NEXT calls)
10	10
100	100
1000	1000

Pattern observation: The number of operations grows directly with the input size.

Final Time Complexity

Time Complexity: O(n)

This means the time to run the function grows in a straight line with the number of rows it returns.

Common Mistake

[X] Wrong: "RETURN NEXT returns all rows at once, so time does not grow with more rows."

[OK] Correct: RETURN NEXT sends rows one at a time inside the loop, so more rows mean more calls and more time.

Interview Connect

Understanding how RETURN NEXT affects execution time helps you write efficient set-returning functions and shows you can think about performance in real database tasks.

Self-Check

What if we replaced RETURN NEXT with building an array and returning it once at the end? How would the time complexity change?