Why advanced PL/pgSQL matters in PostgreSQL - See It in Action

Why advanced PL/pgSQL matters

📖 Scenario: You work at a company that uses PostgreSQL to manage its data. You want to learn how advanced PL/pgSQL features can help you write better database functions that are faster, easier to maintain, and more powerful.

🎯 Goal: Build a simple PL/pgSQL function step-by-step that demonstrates the value of advanced features like variables, control structures, and error handling.

📋 What You'll Learn

Create a PL/pgSQL function with input parameters

Declare variables inside the function

Use IF statements to control logic

Implement exception handling with BEGIN...EXCEPTION blocks

💡 Why This Matters

🌍 Real World

Advanced PL/pgSQL lets you write powerful database functions that run inside PostgreSQL, improving performance and maintainability.

💼 Career

Database developers and backend engineers use PL/pgSQL to implement business logic close to the data, making applications faster and more reliable.

1

Create a basic PL/pgSQL function

Write a PL/pgSQL function named calculate_discount that takes one parameter price of type numeric and returns a numeric. Start the function with the correct CREATE FUNCTION syntax and declare the function body with BEGIN and END.

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
BEGIN
    -- Your code here
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use CREATE FUNCTION with the function name and parameter. Use BEGIN and END to define the function body.

2

Add a variable to hold the discount

Inside the calculate_discount function, declare a variable named discounted_price of type numeric. Initialize it to price. Use the DECLARE section for the variable declaration.

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
DECLARE
    -- Declare discounted_price here
BEGIN
    RETURN price;
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use DECLARE to create variables before BEGIN. Initialize discounted_price with price.

3

Use IF statement to apply discount

Add an IF statement inside the function body that checks if price is greater than 100. If true, set discounted_price to 90% of price. Otherwise, keep it as price. Use the IF ... THEN ... ELSE ... END IF; syntax.

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
DECLARE
    discounted_price numeric := price;
BEGIN
    -- Add IF statement here
    RETURN discounted_price;
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use IF to check the price and update discounted_price accordingly.

4

Add exception handling for invalid input

Wrap the function body inside a BEGIN ... EXCEPTION ... END block. Add an EXCEPTION WHEN others THEN clause that returns NULL if any error occurs (for example, if price is NULL).

PostgreSQL

CREATE FUNCTION calculate_discount(price numeric) RETURNS numeric AS $$
DECLARE
    discounted_price numeric := price;
BEGIN
    -- Add exception handling here
    IF price > 100 THEN
        discounted_price := price * 0.9;
    ELSE
        discounted_price := price;
    END IF;
    RETURN discounted_price;
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use a nested BEGIN ... EXCEPTION ... END block to catch errors and return NULL.