Introduction to PL/pgSQL CASE Statment

Besides theif statement, PostgreSQL provides thecase statements that allow you to execute a block of code based on conditions.

Thecase statement selects awhen section to execute from a list ofwhen sections based on a condition.

Thecase statement has two forms:

• Simplecase statement
• Searchedcase statement

Notice that you should not be confused about thecase statement andcase expression. Thecase expression evaluates to a value while thecase statement selects a section to execute based on conditions.

1) Simple case statement

Here’s the basic syntax of the simplecase statement:

case search-expression   when expression_1 [, expression_2, ...]then      when-statements  [ ... ]  [else      else-statements ]END case;

Thesearch-expression is an expression that evaluates to a result.

Thecase statement compares the result of thesearch-expression with theexpression in eachwhen branch using equal operator (=) from top to bottom.

If thecase statement finds a match, it will execute the correspondingwhen section. Additionally, it stops checking the remainingwhen sections

If thecase statement cannot find any match, it will execute theelse section.

Theelse section is optional. If the result of thesearch-expression does not matchexpression in thewhen sections and theelse section does not exist, thecase statement will raise a case_not_found exception.

The following example shows how to use a simplecase statement:

do $$declarerate film.rental_rate%type;price_segmentvarchar(50);begin    -- get the rental rate    select rental_rateinto rate    from film    where film_id= 100;-- assign the price segmentif foundthencase 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;raise notice'%', price_segment;elseraise notice'film not found';    end if;end; $$

Output:

NOTICE:  High End

How it works.

First, select the rental rate of the film with id 100.

Second, assign price segment to the price_segment variable if the film id 100 exists or a message otherwise.

Based on the rental rates 0.99, 2.99, or 4.99, the case statement assigns mass, mainstream, or high-end to theprice_segment variable. If the rental rate is not one of these values, thecase statement assigns the string Unspecified to theprice_segment variable.

The following flowchart illustrates the simplecase statement in this example:

2) Searched case statement

The following syntax shows the basic syntax of the searchedcase statement:

case    when boolean-expression-1 then      statements  [when boolean-expression-2 then      statements    ... ]  [else      statements ]end case;

In this syntax, thecase statement evaluates the boolean expressions sequentially from top to bottom until it finds an expression that evaluates totrue

Subsequently, the case statement executes the correspondingwhen section and immediately stops searching for the remaining expressions.

If no expression evaluates to true, thecase statement will execute theelse section.

Theelse section is optional. If you omit theelse section and there is no expression evaluated totrue, thecase statement will raise the case_not_found exception.

The following example illustrates how to use a simplecase statement:

do $$declare    total_paymentnumeric;    service_levelvarchar(25) ;begin     select sum(amount)into total_payment     from Payment     where customer_id= 100; if foundthen    case   when total_payment> 200 then               service_level= 'Platinum' ;           when total_payment> 100 then           service_level= 'Gold' ;           else               service_level= 'Silver' ;        end case;raise notice'Service Level: %', service_level;     else    raise notice'Customer not found'; end if;end; $$

How it works:

• First, select the total payment paid by the customer id 100 from thepayment table.
• Then, assign the service level to the customer based on the total payment

The following diagram illustrates the logic:

Notice that the searchedcase statement is similar to theif then elsif statement.

Summary

• Use thecase statement to execute a section based on certain conditions.
• Use a simplecase statement to compare a value with a list of values and if a match is found, execute a section.
• Use a searchedcase statement to evaluate a list of conditions and execute a section if the condition is true.

Last updated onFebruary 7, 2024

On this page

• Introduction to PL/pgSQL CASE Statment
• 1) Simple case statement
• 2) Searched case statement
• Summary