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

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

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

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;

Hint

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

Practice

1. Why are utility functions important in PostgreSQL databases?

easy

A. They help reuse code and simplify queries

B. They slow down query execution significantly

C. They make the database schema more complex

D. They prevent any data from being updated

Solution

1. Step 1: Understand the purpose of utility functions

   Utility functions are designed to perform common tasks repeatedly without rewriting code.

2. Step 2: Recognize benefits in query simplification

   By using utility functions, queries become simpler and easier to maintain because logic is centralized.

3. Final Answer:

   They help reuse code and simplify queries -> Option A

4. Quick Check:

   Utility functions = reuse and simplify [OK]

Hint: Utility functions = reuse code + simplify queries [OK]

Common Mistakes:

• Thinking utility functions slow down queries
• Believing they complicate the database schema
• Assuming they block data updates

2. Which of the following is the correct syntax to create a simple utility function in PostgreSQL that returns the square of an integer?

easy

A. CREATE FUNCTION square(x INT) RETURNS INT AS 'RETURN x * x;' LANGUAGE sql;

B. CREATE FUNCTION square(x INT) RETURNS INT BEGIN RETURN x * x; END;

C. CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION square(x INT) RETURNS INT AS $$ RETURN x * x; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Identify correct function creation syntax

   PostgreSQL requires the function body inside $$ with BEGIN and END for plpgsql language.

2. Step 2: Check for proper RETURNS and LANGUAGE clauses

   CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql; correctly uses RETURNS INT, BEGIN...END block, and LANGUAGE plpgsql.

3. Final Answer:

   CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   Correct plpgsql function syntax = CREATE FUNCTION square(x INT) RETURNS INT AS $$ BEGIN RETURN x * x; END; $$ LANGUAGE plpgsql; [OK]

Hint: Use $$ with BEGIN...END and specify LANGUAGE plpgsql [OK]

Common Mistakes:

• Omitting BEGIN and END in plpgsql functions
• Missing LANGUAGE clause
• Incorrect function body syntax

3. Given the function below, what will be the output of SELECT multiply_by_two(5);?

CREATE FUNCTION multiply_by_two(n INT) RETURNS INT AS $$ BEGIN RETURN n * 2; END; $$ LANGUAGE plpgsql;

medium

A. 5

B. NULL

C. Error: function not found

D. 10

Solution

1. Step 1: Understand the function logic

   The function takes an integer n and returns n multiplied by 2.

2. Step 2: Apply the input value 5

   Calling multiply_by_two(5) returns 5 * 2 = 10.

3. Final Answer:

   10 -> Option D

4. Quick Check:

   5 * 2 = 10 [OK]

Hint: Multiply input by 2 as function defines [OK]

Common Mistakes:

• Confusing input and output values
• Assuming function returns input unchanged
• Expecting NULL or error without reason

4. You wrote this utility function but get an error when calling it:

CREATE FUNCTION add_five(n INT) RETURNS INT AS $$ BEGIN RETURN n + 5 END; $$ LANGUAGE plpgsql;

What is the likely cause of the error?

medium

A. Wrong data type for parameter

B. Missing semicolon after RETURN statement

C. Incorrect function name

D. LANGUAGE clause is missing

Solution

1. Step 1: Check function syntax carefully

   In plpgsql, each statement must end with a semicolon; the RETURN line lacks one.

2. Step 2: Identify missing semicolon causing syntax error

   Adding a semicolon after RETURN n + 5 fixes the error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option B

4. Quick Check:

   Semicolon needed after RETURN [OK]

Hint: Always end statements with semicolon in plpgsql [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Assuming function name causes error
• Ignoring LANGUAGE clause presence

5. You want to create a utility function that returns the length of a text string but returns 0 if the input is NULL. Which function definition correctly implements this behavior?

hard

A. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN IF s IS NULL THEN RETURN 0; ELSE RETURN LENGTH(s); END IF; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN RETURN COALESCE(s, 0); END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ RETURN LENGTH(s) OR 0; $$ LANGUAGE sql;

D. CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN RETURN LENGTH(s); END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand requirement for NULL input handling

   The function must return 0 if input is NULL, else return string length.

2. Step 2: Analyze each option for NULL safety

   CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN IF s IS NULL THEN RETURN 0; ELSE RETURN LENGTH(s); END IF; END; $$ LANGUAGE plpgsql; explicitly checks for NULL and returns 0, fulfilling the requirement correctly.

3. Final Answer:

   CREATE FUNCTION safe_length(s TEXT) RETURNS INT AS $$ BEGIN IF s IS NULL THEN RETURN 0; ELSE RETURN LENGTH(s); END IF; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Explicit NULL check returns 0 [OK]

Hint: Use IF NULL THEN return 0 ELSE length END IF [OK]

Common Mistakes:

• Not handling NULL input explicitly
• Using COALESCE incorrectly with LENGTH
• Assuming LENGTH returns 0 for NULL