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

Need a 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

Need a 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

Need a 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

Need a hint?

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