Function creation syntax in PostgreSQL - Mini Project: Build & Apply

Create a Simple PostgreSQL Function

📖 Scenario: You are working as a database assistant for a small bookstore. The store wants to have a quick way to calculate the total price of books including tax.

🎯 Goal: Build a PostgreSQL function named calculate_total_price that takes a book price as input and returns the price including a fixed tax rate.

📋 What You'll Learn

Create a function named calculate_total_price

The function should accept one parameter named price of type numeric

The function should return a numeric value

Inside the function, calculate the total price by adding 10% tax to the input price

Use the LANGUAGE plpgsql syntax for the function

💡 Why This Matters

🌍 Real World

Functions in databases help automate calculations like pricing, taxes, and discounts, saving time and reducing errors.

💼 Career

Knowing how to write database functions is useful for database administrators, backend developers, and data analysts to implement business logic inside the database.

1

Create the function header

Write the first line to create a function named calculate_total_price that accepts one parameter called price of type numeric and returns numeric.

PostgreSQL

-- Write the function header here
-- Your code here

Need a hint?

Use CREATE FUNCTION function_name(parameter_name parameter_type) RETURNS return_type syntax.

2

Add the function body start

Add the AS $$ line and start the BEGIN block after the function header.

PostgreSQL

CREATE FUNCTION calculate_total_price(price numeric) RETURNS numeric
AS $$
BEGIN
-- Your code here

Need a hint?

Use AS $$ to start the function body and BEGIN to start the block.

3

Write the calculation and return statement

Inside the BEGIN block, write a RETURN statement that returns the price plus 10% tax (price * 1.10).

PostgreSQL

CREATE FUNCTION calculate_total_price(price numeric) RETURNS numeric
AS $$
BEGIN
-- Your code here

Need a hint?

Use RETURN price * 1.10; to add 10% tax.

4

Close the function body and specify language

Add the END; line, the closing $$, and specify LANGUAGE plpgsql; to complete the function.

PostgreSQL

CREATE FUNCTION calculate_total_price(price numeric) RETURNS numeric
AS $$
BEGIN
    RETURN price * 1.10;
-- Your code here

Need a hint?

Close the block with END;, then close the body with $$ and specify the language.