Functions returning TABLE in PostgreSQL - Mini Project: Build & Apply

Create a PostgreSQL Function Returning a Table

📖 Scenario: You work in a small bookstore database. You want to create a function that returns a list of books with their titles and prices.

🎯 Goal: Build a PostgreSQL function named get_books that returns a table with columns title (text) and price (numeric).

📋 What You'll Learn

Create a table named books with columns title (text) and price (numeric).

Insert three specific books into the books table.

Create a function named get_books that returns a table with columns title and price.

The function should return all rows from the books table.

💡 Why This Matters

🌍 Real World

Functions returning tables are useful to package reusable queries that return multiple rows and columns, like reports or filtered data.

💼 Career

Database developers and backend engineers often write such functions to simplify complex queries and improve code reuse.

1

Create the books table and insert data

Create a table called books with columns title of type text and price of type numeric. Then insert these three rows exactly: ('The Hobbit', 15.99), ('1984', 12.50), and ('Clean Code', 33.00).

PostgreSQL

-- Create the books table and insert data
-- Your code here

Hint

Use CREATE TABLE books (title text, price numeric); and then INSERT INTO books (title, price) VALUES (...), (...), (...);

2

Define the function header returning a table

Write the header of a PostgreSQL function named get_books that returns a table with columns title of type text and price of type numeric. Use RETURNS TABLE(title text, price numeric).

PostgreSQL

CREATE TABLE books (
    title text,
    price numeric
);

INSERT INTO books (title, price) VALUES
('The Hobbit', 15.99),
('1984', 12.50),
('Clean Code', 33.00);

-- Write the function header for get_books returning a table
-- Your code here

Hint

Use CREATE FUNCTION get_books() RETURNS TABLE(title text, price numeric) LANGUAGE sql AS $$ to start the function.

3

Write the function body to return all books

Inside the function body, write a SQL query that selects title and price from the books table.

PostgreSQL

CREATE TABLE books (
    title text,
    price numeric
);

INSERT INTO books (title, price) VALUES
('The Hobbit', 15.99),
('1984', 12.50),
('Clean Code', 33.00);

CREATE FUNCTION get_books()
RETURNS TABLE(title text, price numeric)
LANGUAGE sql
AS $$
-- Write the SELECT query to return title and price from books
-- Your code here

Hint

Use SELECT title, price FROM books; inside the function body.

4

Complete the function definition

Close the function definition by adding $$; after the SELECT statement to complete the function.

PostgreSQL

CREATE TABLE books (
    title text,
    price numeric
);

INSERT INTO books (title, price) VALUES
('The Hobbit', 15.99),
('1984', 12.50),
('Clean Code', 33.00);

CREATE FUNCTION get_books()
RETURNS TABLE(title text, price numeric)
LANGUAGE sql
AS $$
SELECT title, price FROM books;
-- Close the function with $$;
-- Your code here

Hint

End the function with $$; to close the function body.

Practice

1. What does a PostgreSQL function declared with RETURNS TABLE do?

easy

A. Returns no result, only performs actions

B. Returns only a single scalar value

C. Returns multiple rows and columns as a table result

D. Returns a JSON object

Solution

1. Step 1: Understand the purpose of RETURNS TABLE

   The RETURNS TABLE clause defines that the function will return a set of rows with specified columns, like a table.

2. Step 2: Compare with other return types

   Unlike scalar returns or void, RETURNS TABLE returns multiple rows and columns.

3. Final Answer:

   Returns multiple rows and columns as a table result -> Option C

4. Quick Check:

   RETURNS TABLE means multiple rows/columns [OK]

Hint: RETURNS TABLE means function outputs rows and columns [OK]

Common Mistakes:

• Thinking RETURNS TABLE returns a single value
• Confusing RETURNS TABLE with RETURNS VOID
• Assuming it returns JSON automatically

2. Which of the following is the correct syntax to declare a PostgreSQL function returning a table with columns id INT and name TEXT?

easy

A. CREATE FUNCTION f() RETURNS TABLE(id TEXT, name INT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION f() RETURNS SETOF RECORD AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION f() RETURNS INT AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION f() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check RETURNS TABLE syntax

   CREATE FUNCTION f() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql; correctly declares RETURNS TABLE(id INT, name TEXT) matching the column names and types.

2. Step 2: Verify RETURN QUERY usage

   CREATE FUNCTION f() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT 1, 'a'; END; $$ LANGUAGE plpgsql; uses RETURN QUERY SELECT 1, 'a'; which returns rows matching the table structure.

3. Final Answer:

   Correct RETURNS TABLE syntax and return statement -> Option D

4. Quick Check:

   RETURNS TABLE with matching columns and RETURN QUERY [OK]

Hint: RETURNS TABLE needs column names/types and RETURN QUERY [OK]

Common Mistakes:

• Using RETURNS SETOF RECORD without column definition
• Swapping column types in RETURNS TABLE
• Returning scalar instead of query

3. Given this function:

CREATE FUNCTION get_numbers() RETURNS TABLE(num INT) AS $$ BEGIN RETURN QUERY SELECT generate_series(1,3); END; $$ LANGUAGE plpgsql;

What will be the output of SELECT * FROM get_numbers();?

medium

A. 3 rows with values 1, 2, 3

B. 1 row with value 3

C. Error: function does not return a table

D. Empty result set

Solution

1. Step 1: Understand generate_series(1,3)

   This function generates rows with values 1, 2, and 3.

2. Step 2: RETURN QUERY returns all rows

   The function returns all rows from generate_series(1,3) as a table with column num.

3. Final Answer:

   3 rows with values 1, 2, 3 -> Option A

4. Quick Check:

   generate_series(1,3) returns 3 rows [OK]

Hint: generate_series returns multiple rows, so function returns them all [OK]

Common Mistakes:

• Thinking it returns a single row
• Confusing RETURNS TABLE with scalar return
• Expecting an error due to missing RETURNS SETOF

4. Identify the error in this function:

CREATE FUNCTION get_data() RETURNS TABLE(id INT, val TEXT) AS $$ BEGIN RETURN SELECT 1, 'a'; END; $$ LANGUAGE plpgsql;

medium

A. Missing RETURN QUERY before SELECT

B. Wrong column types in RETURNS TABLE

C. Function must return VOID

D. Missing LANGUAGE declaration

Solution

1. Step 1: Check RETURN statement in RETURNS TABLE function

   In PL/pgSQL, to return rows from a query, use RETURN QUERY, not just RETURN.

2. Step 2: Identify missing RETURN QUERY

   The function uses RETURN SELECT which is invalid syntax; it should be RETURN QUERY SELECT.

3. Final Answer:

   Missing RETURN QUERY before SELECT -> Option A

4. Quick Check:

   Use RETURN QUERY to return rows in RETURNS TABLE [OK]

Hint: Use RETURN QUERY, not RETURN, to return table rows [OK]

Common Mistakes:

• Using RETURN instead of RETURN QUERY for table results
• Omitting LANGUAGE plpgsql
• Incorrect RETURNS TABLE column types

5. You want to create a function that returns a table of user IDs and their uppercase names from a users table with columns id INT and name TEXT. Which function definition correctly achieves this?

hard

A. CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, LOWER(name) FROM users; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION get_upper_users() RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION get_upper_users() RETURNS TABLE(id TEXT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Match RETURNS TABLE columns with SELECT output

   The function returns id INT and name TEXT, so the SELECT must output these types. CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; matches this.

2. Step 2: Check the transformation applied

   CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; uses UPPER(name) to convert names to uppercase as required.

3. Step 3: Verify other options

   CREATE FUNCTION get_upper_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, LOWER(name) FROM users; END; $$ LANGUAGE plpgsql; uses LOWER instead of UPPER, CREATE FUNCTION get_upper_users() RETURNS SETOF record AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; returns SETOF record (no column definition), CREATE FUNCTION get_upper_users() RETURNS TABLE(id TEXT, name TEXT) AS $$ BEGIN RETURN QUERY SELECT id, UPPER(name) FROM users; END; $$ LANGUAGE plpgsql; mismatches id type (TEXT instead of INT).

4. Final Answer:

   Correctly returns id and uppercase name as a table -> Option B

5. Quick Check:

   RETURNS TABLE with matching columns and UPPER(name) [OK]

Hint: Match RETURNS TABLE columns and use RETURN QUERY with correct SELECT [OK]

Common Mistakes:

• Using LOWER instead of UPPER
• Mismatching column types in RETURNS TABLE
• Using SETOF record instead of RETURNS TABLE