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.
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
Share your thoughts in the comments
Please Login to comment...