Why utility functions matter in PostgreSQL - See It in Action

Why Utility Functions Matter in PostgreSQL

📖 Scenario: You are managing a small online bookstore database. You often need to calculate the total price of books including tax and apply discounts. Writing the same calculation repeatedly in your queries is tiring and error-prone.Utility functions help by letting you write the calculation once and reuse it everywhere easily.

🎯 Goal: Create a utility function in PostgreSQL that calculates the final price of a book after applying tax and discount. Then use this function in a query to get the final prices for all books.

📋 What You'll Learn

Create a table books with columns id, title, and price

Insert three books with exact titles and prices

Create a utility function calculate_final_price that takes price, tax_rate, and discount as inputs and returns the final price

Write a SELECT query that uses calculate_final_price to show each book's title and final price with 10% tax and 5% discount

💡 Why This Matters

🌍 Real World

Utility functions are used in real databases to avoid repeating complex calculations and to keep queries clean and consistent.

💼 Career

Knowing how to write and use utility functions is important for database developers and analysts to write maintainable and efficient SQL code.

1

Create the books table and insert data

Create a table called books with columns id as integer primary key, title as text, and price as numeric. Then insert these three books exactly: (1, 'Learn SQL', 30.00), (2, 'PostgreSQL Basics', 45.50), and (3, 'Advanced Databases', 60.00).

PostgreSQL

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

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Create the utility function calculate_final_price

Create a function called calculate_final_price that takes three parameters: price (numeric), tax_rate (numeric), and discount (numeric). It should return a numeric value calculated as price + (price * tax_rate) - (price * discount). Use LANGUAGE SQL and IMMUTABLE.

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Learn SQL', 30.00),
  (2, 'PostgreSQL Basics', 45.50),
  (3, 'Advanced Databases', 60.00);

-- Create the function calculate_final_price here
-- Your code here

Need a hint?

Use CREATE FUNCTION with LANGUAGE SQL. The function returns the final price after tax and discount.

3

Use the utility function in a SELECT query

Write a SELECT query that shows each book's title and the final price using the calculate_final_price function. Use tax_rate as 0.10 (10%) and discount as 0.05 (5%).

PostgreSQL

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Learn SQL', 30.00),
  (2, 'PostgreSQL Basics', 45.50),
  (3, 'Advanced Databases', 60.00);

CREATE FUNCTION calculate_final_price(price NUMERIC, tax_rate NUMERIC, discount NUMERIC) RETURNS NUMERIC AS $$
  SELECT price + (price * tax_rate) - (price * discount);
$$ LANGUAGE SQL IMMUTABLE;

-- Write the SELECT query using calculate_final_price
-- Your code here

Need a hint?

Use the function in the SELECT list to calculate the final price for each book.

4

Add a comment explaining why utility functions matter

Add a SQL comment at the top of your code explaining in one sentence why utility functions like calculate_final_price matter in databases.

PostgreSQL

-- Add a comment explaining why utility functions matter
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  price NUMERIC
);

INSERT INTO books (id, title, price) VALUES
  (1, 'Learn SQL', 30.00),
  (2, 'PostgreSQL Basics', 45.50),
  (3, 'Advanced Databases', 60.00);

CREATE FUNCTION calculate_final_price(price NUMERIC, tax_rate NUMERIC, discount NUMERIC) RETURNS NUMERIC AS $$
  SELECT price + (price * tax_rate) - (price * discount);
$$ LANGUAGE SQL IMMUTABLE;

SELECT title, calculate_final_price(price, 0.10, 0.05) AS final_price FROM books;

Need a hint?

Write a comment starting with -- at the top of your code.