Open In App

PostgreSQL – CASE Statement

Improve
Improve
Like Article
Like
Save
Share
Report

PostgreSQL provides you with CASE statements that allow you to execute a block of code conditionally. There are two forms of the CASE statement: 

  • Simple CASE statement
  • Searched CASE statement

Simple CASE statement

Syntax:
CASE search-expression
   WHEN expression_1 [, expression_2, ...] THEN
      when-statements
  [ ... ]
  [ELSE
      else-statements ]
END CASE;

The search-expression is an expression that will evaluate with the expression in each WHEN branch using equality operand (=). If a match found, the when-statements in the corresponding WHEN branch are executed. The subsequent expressions underneath will not be evaluated.

The else-statements in the ELSE branch are executed if no match found. The ELSE branch is optional. If no match found and there is no ELSE branch, PostgreSQL will raise the CASE_NOT_FOUND exception.

Let’s look into some examples in our sample dvdrental database.

Example:

Here we will create a new function named get_price_segment that accepts p_film_id as the argument. Based on the rental rate of the film, it returns the price segment: mass, mainstream, high end. In case the price is not 0.99, 2.99 or 4.99, the function returns unspecified.

CREATE OR REPLACE FUNCTION get_price_segment(p_film_id integer)
   RETURNS VARCHAR(50) AS $$
DECLARE 
    rate   NUMERIC;
    price_segment VARCHAR(50);
BEGIN
      -- get the rate based on film_id
    SELECT INTO rate rental_rate 
    FROM film 
    WHERE film_id = p_film_id;
        
     CASE rate
    WHEN 0.99 THEN
            price_segment = 'Mass';
    WHEN 2.99 THEN
            price_segment = 'Mainstream';
    WHEN 4.99 THEN
            price_segment = 'High End';
    ELSE
        price_segment = 'Unspecified';
    END CASE;
    
   RETURN price_segment;
END; $$
LANGUAGE plpgsql;

Now test the get_price_segment() function using the statement:

SELECT get_price_segment(123) AS "Price Segment";

Output:

Searched CASE statement

Syntax:
CASE
    WHEN boolean-expression-1 THEN
      statements
  [ WHEN boolean-expression-2 THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

The searched CASE statement executes statements based on the result of Boolean expressions in each WHEN clause. PostgreSQL evaluates the Boolean expressions sequentially from top to bottom until one expression is true. Then the evaluation stops and the corresponding statement are executed. The control is passed to the next statement after the END CASE.

In case no true result found, the statements in the ELSE clause are executed. The ELSE clause is optional. If you omit the ELSE clause and there is no true result, PostgreSQL will raise the CASE_NOT_FOUND exception.

Example:

The get_customer_service function accepts p_customer_id as the argument. It first gets the total payment paid by the customer from the payment table. Then based on the total payment, the function uses the searched CASE statement to return the service level platinum, gold, and silver.

CREATE OR REPLACE FUNCTION get_customer_service (p_customer_id INTEGER) 
    RETURNS VARCHAR (25) AS $$ 
DECLARE
 total_payment NUMERIC ; 
        service_level VARCHAR (25) ;
BEGIN
 -- get the rate based on film_id
     SELECT
 INTO total_payment SUM (amount)
     FROM
 payment
     WHERE
 customer_id = p_customer_id ; 
  
   CASE
      WHEN total_payment > 200 THEN
         service_level = 'Platinum' ;
      WHEN total_payment > 100 THEN
     service_level = 'Gold' ;
      ELSE
         service_level = 'Silver' ;
   END CASE ;

   RETURN service_level ;
END ; $$ LANGUAGE plpgsql;

Now let’s test the above function using the below statement:

SELECT
  148 AS customer,
    get_customer_service (148)
UNION

   SELECT
  178 AS customer,
    get_customer_service (178)
UNION
   SELECT
  81 AS customer,
    get_customer_service (81);

Output:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads