CASE in PL/pgSQL in PostgreSQL - Mini Project: Build & Apply

Using CASE in PL/pgSQL to Categorize Sales

📖 Scenario: You work in a retail company database. You want to categorize sales amounts into 'Low', 'Medium', and 'High' categories to better understand sales performance.

🎯 Goal: Create a PL/pgSQL function that uses a CASE statement to categorize sales amounts into three categories: 'Low' for sales less than 100, 'Medium' for sales between 100 and 500, and 'High' for sales above 500.

📋 What You'll Learn

Create a table called sales with columns sale_id (integer) and amount (numeric).

Insert exactly three rows into sales with amounts 50, 200, and 600.

Create a PL/pgSQL function called categorize_sale that takes a numeric parameter sale_amount and returns a text category using a CASE statement.

Use the CASE statement inside the function to return 'Low', 'Medium', or 'High' based on the sale_amount.

Create a query that selects sale_id, amount, and the category by calling categorize_sale(amount) for each sale.

💡 Why This Matters

🌍 Real World

Categorizing sales data helps businesses quickly understand performance levels and make decisions.

💼 Career

Database developers and analysts often write functions with CASE statements to classify and transform data inside the database.

1

Create the sales table and insert data

Create a table called sales with columns sale_id as integer and amount as numeric. Then insert three rows with sale_id values 1, 2, 3 and amount values 50, 200, and 600 respectively.

PostgreSQL

-- Create the sales table and insert three rows
-- Your code here

Need a hint?

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

2

Create the categorize_sale function header

Create a PL/pgSQL function called categorize_sale that takes one parameter sale_amount of type numeric and returns text. Start the function with CREATE OR REPLACE FUNCTION categorize_sale(sale_amount NUMERIC) RETURNS TEXT AS $$ and declare the function language as plpgsql.

PostgreSQL

CREATE TABLE sales (
    sale_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (sale_id, amount) VALUES
(1, 50),
(2, 200),
(3, 600);

-- Create the function header for categorize_sale
-- Your code here

Need a hint?

Use CREATE OR REPLACE FUNCTION with the correct parameter and return type, and specify LANGUAGE plpgsql at the end.

3

Add the CASE statement inside the function

Inside the categorize_sale function, use a CASE statement to return 'Low' if sale_amount is less than 100, 'Medium' if it is between 100 and 500 inclusive, and 'High' if it is greater than 500. Use RETURN to return the category.

PostgreSQL

CREATE TABLE sales (
    sale_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (sale_id, amount) VALUES
(1, 50),
(2, 200),
(3, 600);

CREATE OR REPLACE FUNCTION categorize_sale(sale_amount NUMERIC) RETURNS TEXT AS $$
BEGIN
    -- Use CASE statement to return category
    -- Your code here
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use RETURN CASE WHEN ... THEN ... ELSE ... END; inside the function body.

4

Query sales with categories using the function

Write a SQL query that selects sale_id, amount, and a new column category which calls the categorize_sale(amount) function for each row in the sales table.

PostgreSQL

CREATE TABLE sales (
    sale_id INTEGER,
    amount NUMERIC
);

INSERT INTO sales (sale_id, amount) VALUES
(1, 50),
(2, 200),
(3, 600);

CREATE OR REPLACE FUNCTION categorize_sale(sale_amount NUMERIC) RETURNS TEXT AS $$
BEGIN
    RETURN CASE
        WHEN sale_amount < 100 THEN 'Low'
        WHEN sale_amount BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'High'
    END;
END;
$$ LANGUAGE plpgsql;

-- Write a query to select sale_id, amount, and category using categorize_sale(amount)
-- Your code here

Need a hint?

Use SELECT sale_id, amount, categorize_sale(amount) AS category FROM sales; to get the categorized sales.