RETURN and RETURN NEXT in PostgreSQL - Mini Project: Build & Apply

Using RETURN and RETURN NEXT in PostgreSQL Functions

📖 Scenario: You are working with a PostgreSQL database for a small bookstore. You want to create a function that returns multiple book titles one by one.

🎯 Goal: Create a PostgreSQL function that uses RETURN NEXT to return multiple rows of book titles, and then uses RETURN to finish the function.

📋 What You'll Learn

Create a function named get_books that returns a set of text values.

Use RETURN NEXT to return each book title inside the function.

Use RETURN to end the function after all titles are returned.

💡 Why This Matters

🌍 Real World

Returning multiple rows from a function is useful when you want to send a list of items, like book titles, from the database to an application.

💼 Career

Database developers often write functions that return multiple rows using RETURN NEXT and RETURN to build APIs or reports.

1

Create the books array

Create a variable called books as an array of text with these exact book titles: 'The Hobbit', '1984', 'Pride and Prejudice'.

PostgreSQL

# Create the books array variable
-- Your code here

Need a hint?

Use DECLARE to start variable declarations and assign the array with ARRAY[...].

2

Add a counter variable

Add a variable called i of type integer initialized to 1 to use as a counter.

PostgreSQL

DECLARE
    books text[] := ARRAY['The Hobbit', '1984', 'Pride and Prejudice'];
    -- Add counter variable i here
    -- Your code here

Need a hint?

Declare i as an integer and set it to 1.

3

Use RETURN NEXT in a loop

Write a WHILE loop that runs while i is less than or equal to the length of books. Inside the loop, use RETURN NEXT books[i] to return each book title, then increment i by 1.

PostgreSQL

DECLARE
    books text[] := ARRAY['The Hobbit', '1984', 'Pride and Prejudice'];
    i integer := 1;
BEGIN
    -- Add WHILE loop with RETURN NEXT here
    -- Your code here
END;

Need a hint?

Use array_length(books, 1) to get the number of elements in the array.

4

Complete the function with RETURN

Add RETURN after the loop to finish the function.

PostgreSQL

DECLARE
    books text[] := ARRAY['The Hobbit', '1984', 'Pride and Prejudice'];
    i integer := 1;
BEGIN
    WHILE i <= array_length(books, 1) LOOP
        RETURN NEXT books[i];
        i := i + 1;
    END LOOP;
    -- Add RETURN here
    -- Your code here
END;

Need a hint?

Use RETURN; to end the function after returning all rows.