RETURN and RETURN NEXT in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this function using RETURN NEXT?

Consider the following PostgreSQL function that returns a set of integers using RETURN NEXT. What will be the output when calling SELECT * FROM generate_numbers();?

PostgreSQL

CREATE OR REPLACE FUNCTION generate_numbers() RETURNS SETOF INTEGER AS $$
DECLARE
  i INTEGER := 1;
BEGIN
  WHILE i <= 3 LOOP
    RETURN NEXT i;
    i := i + 1;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

A 1, 2, 3

B 3

C Empty result

D 1

💡 Hint

RETURN NEXT adds a row to the result set without ending the function.

The function loops from 1 to 3, each time returning the current value with RETURN NEXT. This accumulates the rows 1, 2, and 3 in the result set.

❓ query_result

intermediate

What happens if RETURN is used inside a loop?

Given this PostgreSQL function, what will be the output of SELECT * FROM return_in_loop();?

PostgreSQL

CREATE OR REPLACE FUNCTION return_in_loop() RETURNS SETOF INTEGER AS $$
DECLARE
  i INTEGER := 1;
BEGIN
  WHILE i <= 3 LOOP
    RETURN i;
    i := i + 1;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

A 1, 2, 3

B 1

C 3

D Empty result

💡 Hint

RETURN immediately ends the function and returns a single value.

The RETURN statement inside the loop exits the function on the first iteration, returning only 1.

📝 Syntax

advanced

Which function definition correctly uses RETURN NEXT to return multiple rows?

Select the function that correctly returns multiple rows using RETURN NEXT in PostgreSQL.

A CREATE FUNCTION f() RETURNS SETOF INTEGER AS $$ BEGIN RETURN NEXT 1; RETURN NEXT 2; RETURN; END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION f() RETURNS SETOF INTEGER AS $$ BEGIN RETURN 1; RETURN 2; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION f() RETURNS INTEGER AS $$ BEGIN RETURN NEXT 1; RETURN NEXT 2; END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION f() RETURNS SETOF INTEGER AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

💡 Hint

RETURN NEXT is used to add rows to a set-returning function.

Option A correctly uses RETURN NEXT twice to add two rows to the result set. Option A uses RETURN which ends the function immediately. Option A has wrong return type. Option A returns only one row.

🔧 Debug

advanced

Why does this function return no rows?

This function is intended to return numbers 1 to 3 using RETURN NEXT, but returns no rows. Why?

PostgreSQL

CREATE OR REPLACE FUNCTION faulty_function() RETURNS SETOF INTEGER AS $$
DECLARE
  i INTEGER := 1;
BEGIN
  WHILE i <= 3 LOOP
    RETURN NEXT i;
    RETURN;
    i := i + 1;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

A RETURN NEXT cannot be used inside loops.

B The variable i is not incremented, causing infinite loop.

C The RETURN statement after RETURN NEXT ends the function on first iteration.

D The function lacks a RETURN QUERY statement.

💡 Hint

RETURN ends the function immediately, stopping further output.

The RETURN after RETURN NEXT causes the function to exit after the first row, so only one row is returned. The loop never continues.

🧠 Conceptual

expert

How do RETURN and RETURN NEXT differ in set-returning functions?

In PostgreSQL set-returning functions, what is the key difference between RETURN and RETURN NEXT?

A RETURN adds a row to the result set; RETURN NEXT ends the function immediately.

B RETURN is used only in SQL functions; RETURN NEXT only in PL/pgSQL.

C Both RETURN and RETURN NEXT add rows to the result set but differ in syntax.

D RETURN ends the function and returns a single value; RETURN NEXT adds a row to the result set and continues.

💡 Hint

Think about when the function stops returning rows.

RETURN immediately ends the function and returns a single value, while RETURN NEXT adds a row to the output but allows the function to continue returning more rows.