PostgreSQL – CASE Statement
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.
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";
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.
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);