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

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

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;

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

Hint

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

Practice

1. What is the main purpose of using CASE in PL/pgSQL?

easy

A. To choose different actions based on conditions

B. To create loops that repeat actions

C. To define new tables in the database

D. To permanently store data in variables

Solution

1. Step 1: Understand the role of CASE

   CASE is used to select one action from many based on conditions, like a traffic light deciding when to stop or go.

2. Step 2: Compare with other options

   Loops repeat actions, table creation defines structure, and variables store data, none of which is the main role of CASE.

3. Final Answer:

   To choose different actions based on conditions -> Option A

4. Quick Check:

   CASE chooses actions based on conditions [OK]

Hint: CASE picks actions by conditions, not loops or storage [OK]

Common Mistakes:

• Confusing CASE with loops
• Thinking CASE creates tables
• Assuming CASE stores data permanently

2. Which of the following is the correct way to end a CASE block in PL/pgSQL?

easy

A. END;

B. STOP;

C. FINISH CASE;

D. END CASE;

Solution

1. Step 1: Recall PL/pgSQL syntax for CASE

   In PL/pgSQL, a CASE block must be closed with END CASE; to mark its end clearly.

2. Step 2: Check other options

   END; ends blocks like functions, but CASE specifically needs END CASE;. FINISH CASE; and STOP; are invalid keywords.

3. Final Answer:

   END CASE; -> Option D

4. Quick Check:

   CASE ends with END CASE; [OK]

Hint: Always close CASE with END CASE; in PL/pgSQL [OK]

Common Mistakes:

• Using END; alone to close CASE
• Writing FINISH CASE; which is invalid
• Using STOP; which is not a PL/pgSQL keyword

3. Consider this PL/pgSQL snippet:

DECLARE
  grade CHAR := 'B';
  result TEXT;
BEGIN
  CASE grade
    WHEN 'A' THEN result := 'Excellent';
    WHEN 'B' THEN result := 'Good';
    ELSE result := 'Average';
  END CASE;
  RETURN result;
END;

What will be the returned value?

medium

A. 'Good'

B. 'Average'

C. 'Excellent'

D. NULL

Solution

1. Step 1: Identify the value of grade

   The variable grade is set to 'B'.

2. Step 2: Match grade in CASE

   CASE checks 'B', matches the second WHEN clause, so result becomes 'Good'.

3. Final Answer:

   'Good' -> Option A

4. Quick Check:

   grade 'B' returns 'Good' [OK]

Hint: Match CASE value to WHEN clause for output [OK]

Common Mistakes:

• Choosing ELSE when a WHEN matches
• Confusing variable assignment inside CASE
• Assuming NULL if no ELSE present

4. Identify the error in this PL/pgSQL CASE block:

DECLARE
  score INT := 85;
  grade TEXT;
BEGIN
  CASE
    WHEN score >= 90 THEN grade := 'A';
    WHEN score >= 80 THEN grade := 'B';
    ELSE grade := 'C';
  END;
  RETURN grade;
END;

medium

A. Incorrect variable declaration

B. Invalid comparison operators

C. Missing END CASE; to close CASE block

D. No ELSE clause present

Solution

1. Step 1: Check CASE block ending

   The CASE block is closed with END; but PL/pgSQL requires END CASE; to close CASE.

2. Step 2: Verify other parts

   Variable declarations and comparisons are correct, and ELSE clause is present.

3. Final Answer:

   Missing END CASE; to close CASE block -> Option C

4. Quick Check:

   CASE must end with END CASE; [OK]

Hint: Close CASE with END CASE;, not just END; [OK]

Common Mistakes:

• Using END; instead of END CASE;
• Thinking ELSE is optional here
• Misreading comparison operators

5. You want to write a PL/pgSQL function that returns 'Pass' if a student's score is 50 or more, 'Fail' if below 50, and 'Invalid' if the score is NULL. Which CASE structure correctly implements this?

hard

A. CASE score
WHEN NULL THEN RETURN 'Invalid';
WHEN >= 50 THEN RETURN 'Pass';
ELSE RETURN 'Fail';
END CASE;

B. CASE
WHEN score IS NULL THEN RETURN 'Invalid';
WHEN score >= 50 THEN RETURN 'Pass';
ELSE RETURN 'Fail';
END CASE;

C. CASE
WHEN score >= 50 THEN RETURN 'Pass';
WHEN score IS NULL THEN RETURN 'Fail';
ELSE RETURN 'Invalid';
END CASE;

D. CASE score
WHEN score >= 50 THEN RETURN 'Pass';
WHEN score < 50 THEN RETURN 'Fail';
ELSE RETURN 'Invalid';
END CASE;

Solution

1. Step 1: Handle NULL explicitly

   Since NULL cannot be matched by simple WHEN, use WHEN score IS NULL to check NULL values.

2. Step 2: Order conditions correctly

   Check NULL first, then score >= 50 for 'Pass', else 'Fail'. This matches

   CASE
     WHEN score IS NULL THEN RETURN 'Invalid';
     WHEN score >= 50 THEN RETURN 'Pass';
     ELSE RETURN 'Fail';
   END CASE;

   .

3. Final Answer:

   CASE with WHEN score IS NULL, then score >= 50, else Fail -> Option B

4. Quick Check:

   Use IS NULL to check NULL in CASE [OK]

Hint: Use WHEN score IS NULL to test NULL in CASE [OK]

Common Mistakes:

• Trying to match NULL with WHEN NULL
• Using CASE score with conditions inside WHEN
• Not checking NULL before other conditions