PL/pgSQL – CASE Statement

In PostgreSQL’s PL/pgSQL, you can use the CASE statement to perform conditional branching based on specific conditions. The CASE statement allows you to evaluate different conditions and execute different code blocks depending on the outcome of those conditions. It’s similar to the IF statement but provides more flexibility when you need to test multiple conditions.

Here’s the basic syntax of the CASE statement in PL/pgSQL:

CASE WHEN condition1 THEN 
   -- Code to execute if condition1 is true 
WHEN condition2 THEN 
   -- Code to execute if condition2 is true 
...
...
...
ELSE 
   -- Code to execute if none of the conditions are true (optional) 
END CASE;

where

• condition1, condition2, etc. are the conditions that you want to evaluate. If a condition is true, the corresponding code block will be executed. You can have multiple WHEN clauses, each with its own condition.
• -- Code to execute is the code that will be executed if the condition associated with the WHEN clause is true.
• ELSE is optional. If none of the conditions in the WHEN clauses are true, the code block following ELSE will be executed. You can have only one ELSE block, and it comes at the end.
• END CASE; marks the end of the CASE block.

Here’s an example of a PL/pgSQL function that uses the CASE statement to determine the day of the week based on an input number:

CREATE OR REPLACE FUNCTION get_day_of_week(day_num INT) RETURNS TEXT AS $$ 
DECLARE 
    day_name TEXT; 
BEGIN 
   CASE 
      WHEN day_num = 1 THEN 
          day_name := 'Sunday'; 
      WHEN day_num = 2 THEN 
          day_name := 'Monday'; 
      WHEN day_num = 3 THEN 
          day_name := 'Tuesday'; 
      WHEN day_num = 4 THEN 
          day_name := 'Wednesday'; 
      WHEN day_num = 5 THEN 
          day_name := 'Thursday'; 
      WHEN day_num = 6 THEN 
          day_name := 'Friday'; 
      WHEN day_num = 7 THEN 
          day_name := 'Saturday'; 
      ELSE day_name := 'Invalid day number'; 
   END CASE; 
   RETURN day_name; 
END; 
$$ LANGUAGE plpgsql;

In this example:

• The function get_day_of_week accepts an integer parameter day_num.
• The CASE statement evaluates the value of day_num and assigns the corresponding day name to the day_name variable.
• If day_num doesn’t match any of the specified conditions, the ELSE block is executed, and “Invalid day number” is assigned to day_name.
• The function returns the day_name variable as text.

You can call this function and pass different values for day_num to get the corresponding day name:

SELECT get_day_of_week(1); 
   -- Returns 'Sunday' 
SELECT get_day_of_week(4); 
   -- Returns 'Wednesday' 
SELECT get_day_of_week(8); 
   -- Returns 'Invalid day number'

The CASE statement is a powerful tool for conditional branching in PL/pgSQL and is especially useful when you need to evaluate multiple conditions and execute different code blocks based on those conditions.