PL/pgSQL CASE Statement

Summary: In this tutorial, you’ll learn how to use the PL/pgSQL CASE statement to execute a block of code based on conditions.

The CASE statement allows you to execute code based on conditions.

PL/pgSQL offers two types of CASE statements:

• Simple CASE statement.
• Searched CASE statement.

Simple CASE statement #

Here’s the syntax of the simple CASE statement:

CASE expression
    WHEN value1 THEN
        statements
    WHEN value2 THEN
        statements
    ...
    ELSE
        statements
END CASE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The simple CASE statement compares an expression with values (value1, value2, etc.) sequentially from top to bottom.

If the result of the expression equals a value, the CASE statement executes the corresponding code and stops comparing the expression with the remaining values.

If the result of the expression does not equal any values, the CASE statement executes the code block in the ELSE branch.

The ELSE branch is optional. If you omit the ELSE branch and the expression does not equal any value, the simple CASE statement raises a CASE_NOT_FOUND exception.

The following example defines a new function called get_storage_cost that returns a storage cost based on a warehouse:

CREATE OR REPLACE FUNCTION get_storage_cost(name VARCHAR) 
RETURNS DEC
AS
$$
DECLARE
    v_storage_cost DEC;
BEGIN
    CASE name
        WHEN 'San Jose Warehouse' THEN v_storage_cost = 10;
        WHEN 'San Francisco Warehouse' THEN v_storage_cost = 15;
        WHEN 'Los Angeles Warehouse' THEN v_storage_cost = 12;
        ELSE
            v_storage_cost = 5;
    END CASE;
 RETURN v_storage_cost;
END;
$$
LANGUAGE PLPGSQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

In this get_storage_cost function, we use the simple CASE statement to return a different storage cost based on the warehouse name.

The following statement calls the get_storage_cost function to get the storage cost of the San Jose Warehouse:

SELECT get_storage_cost('San Jose Warehouse');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 get_storage_cost
------------------
               10Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Searched CASE statement #

Here’s the syntax of the searched CASE expression:

CASE
    WHEN boolean_expression_1 THEN
        statements;
    WHEN boolean_expression_2 THEN
        statements;
    ...
    ELSE
        statements;
END CASE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The searched CASE statement evaluates a set of boolean expressions.

If an expression is true, the CASE statement executes the corresponding code block and stops evaluating the remaining boolean expressions.

If no expression is true, the CASE statement executes the ELSE branch.

Since the ELSE branch is optional, you can omit it. However, if no expression is true and the ELSE branch is unavailable, the CASE statement will raise a CASE_NOT_FOUND exception.

CREATE OR REPLACE FUNCTION get_weight_type(id INT)
RETURNS VARCHAR
AS
$$
DECLARE
    v_gross_weight products.gross_weight%TYPE;
    v_weight_type VARCHAR;
BEGIN
    SELECT gross_weight INTO v_gross_weight
    FROM products
    WHERE product_id = id;

    IF NOT FOUND THEN
        RETURN NULL;
    END IF;

    CASE
        WHEN v_gross_weight < 1 THEN v_weight_type = 'Small';
        WHEN v_gross_weight >= 1 AND v_gross_weight < 5 THEN v_weight_type = 'Medium';
        ELSE v_weight_type = 'Big';
    END CASE;
    RETURN v_weight_type;
END;
$$ LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

How the function works:

First, retrieve the gross_weight of a product specified by id from the products table and assign it to the v_gross_weight variable:

SELECT gross_weight INTO v_gross_weight 
FROM products 
WHERE product_id = id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, return NULL if the product with the id does not exist:

IF NOT FOUND THEN 
    RETURN NULL; 
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, classify the weight of the product based on the gross weight using the searched CASE statement:

CASE
    WHEN v_gross_weight < 1 THEN v_weight_type = 'Small';
    WHEN v_gross_weight >= 1 AND v_gross_weight < 5 THEN v_weight_type = 'Medium';
    ELSE v_weight_type = 'Big';
END CASE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The CASE statement checks the gross_weight and assigns the value to the v_weight_type accordingly.

Finally, return the v_weight_type:

RETURN v_weight_type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the get_weight_type() function to get the weight type of the product with id 1:

SELECT get_weight_type(1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 get_weight_type
-----------------
 SmallCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the simple CASE statement to compare an expression to a set of values and execute the corresponding code.
• Use the searched CASE statement to evaluate a set of boolean expressions and execute the corresponding code for the first true expression.

Quiz #