> This page location: PostgreSQL PL/pgSQL > CASE Statement
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PL/pgSQL CASE Statement

**Info:** The PL/pgSQL CASE statement works the same way on any PostgreSQL deployment, so what you learn here applies whether you're running Postgres yourself, on [Lakebase](https://www.databricks.com/product/lakebase), or on [Neon](https://neon.com). For enterprises building in the AI era, Lakebase delivers the best managed cloud Postgres, with the performance, security, and native Lakehouse integration that production workloads demand. For developers and startups who need to ship and scale fast, Neon is the Postgres platform built around branching, instant provisioning, and serverless economics.

**Summary**: in this tutorial, you will learn about the PL/pgSQL `case` that executes statements based on a certain condition.

## Introduction to PL/pgSQL CASE Statment

Besides the [if statement](https://neon.com/postgresql/plpgsql/plpgsql-if-else-statements), PostgreSQL provides the `case` statements that allow you to execute a block of code based on conditions.

The `case` statement selects a `when` section to execute from a list of `when` sections based on a condition.

The `case` statement has two forms:

- Simple `case` statement
- Searched `case` statement

Notice that you should not be confused about the `case` statement and [case expression](../postgresql-tutorial/postgresql-case). The `case` expression evaluates to a value while the `case` statement selects a section to execute based on conditions.

## 1) Simple case statement

Here's the basic syntax of the simple `case` statement:

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

The `search-expression` is an expression that evaluates to a result.

The `case` statement compares the result of the `search-expression` with the `expression` in each `when` branch using equal operator ( `=`) from top to bottom.

If the `case` statement finds a match, it will execute the corresponding `when` section. Additionally, it stops checking the remaining `when` sections

If the `case` statement cannot find any match, it will execute the `else` section.

The `else` section is optional. If the result of the `search-expression` does not match `expression` in the `when` sections and the `else` section does not exist, the `case` statement will raise a `case_not_found` exception.

The following example shows how to use a simple `case` statement:

```plsql
do $$
declare
	rate film.rental_rate%type;
	price_segment varchar(50);
begin
    -- get the rental rate
    select rental_rate into rate
    from film
    where film_id = 100;

	-- assign the price segment
	if found then
		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;

		raise notice '%', price_segment;
	else
		raise 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 the `price_segment` variable. If the rental rate is not one of these values, the `case` statement assigns the string Unspecified to the `price_segment` variable.

The following flowchart illustrates the simple `case` statement in this example:

![PL/pgSQL simple case statement](https://neon.com/postgresqltutorial/plpgsql-simple-case-statement.png)

## 2) Searched case statement

The following syntax shows the basic syntax of the searched `case` statement:

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

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

Subsequently, the case statement executes the corresponding `when` section and immediately stops searching for the remaining expressions.

If no expression evaluates to true, the `case` statement will execute the `else` section.

The `else` section is optional. If you omit the `else` section and there is no expression evaluated to `true`, the `case` statement will raise the `case_not_found` exception.

The following example illustrates how to use a simple `case` statement:

```plsql
do $$
declare
    total_payment numeric;
    service_level varchar(25) ;
begin
     select sum(amount) into total_payment
     from Payment
     where customer_id = 100;

	 if found then
	    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 the `payment` table.
- Then, assign the service level to the customer based on the total payment

The following diagram illustrates the logic:

![PL/pgSQL searched case statement](https://neon.com/postgresqltutorial/plpgsql-searched-case-statement.png)

Notice that the searched `case` statement is similar to the [if then elsif statement](https://neon.com/postgresql/plpgsql/plpgsql-if-else-statements).

## Summary

- Use the `case` statement to execute a section based on certain conditions.
- Use a simple `case` statement to compare a value with a list of values and if a match is found, execute a section.
- Use a searched `case` statement to evaluate a list of conditions and execute a section if the condition is true.

---

## Related docs (PostgreSQL PL/pgSQL)

- [Introduction](https://neon.com/postgresql/plpgsql/introduction-to-postgresql-stored-procedures)
- [Block Structure](https://neon.com/postgresql/plpgsql/plpgsql-block-structure)
- [Variables](https://neon.com/postgresql/plpgsql/plpgsql-variables)
- [Constants](https://neon.com/postgresql/plpgsql/plpgsql-constants)
- [IF-ELSE Statements](https://neon.com/postgresql/plpgsql/plpgsql-if-else-statements)
- [Loop Statements](https://neon.com/postgresql/plpgsql/plpgsql-loop-statements)
- [FOR Loop](https://neon.com/postgresql/plpgsql/plpgsql-for-loop)
- [WHILE Loop](https://neon.com/postgresql/plpgsql/pl-pgsql-while-loop)
- [EXIT Statement](https://neon.com/postgresql/plpgsql/plpgsql-exit)
- [CONTINUE Statement](https://neon.com/postgresql/plpgsql/pl-pgsql-continue)
- [Cursor](https://neon.com/postgresql/plpgsql/plpgsql-cursor)
- [Function Parameters](https://neon.com/postgresql/plpgsql/plpgsql-function-parameters)
- [Function Overloading](https://neon.com/postgresql/plpgsql/plpgsql-function-overloading)
- [Function Returns Table](https://neon.com/postgresql/plpgsql/plpgsql-function-returns-a-table)
- [Returns SETOF](https://neon.com/postgresql/plpgsql/plpgsql-returns-setof)
- [Record Types](https://neon.com/postgresql/plpgsql/plpgsql-record-types)
- [Row Types](https://neon.com/postgresql/plpgsql/pl-pgsql-row-types)
- [Error Messages](https://neon.com/postgresql/plpgsql/plpgsql-errors-messages)
- [Exception Handling](https://neon.com/postgresql/plpgsql/exception)
- [CREATE FUNCTION](https://neon.com/postgresql/plpgsql/create-function)
- [CREATE PROCEDURE](https://neon.com/postgresql/plpgsql/create-procedure)
- [DROP FUNCTION](https://neon.com/postgresql/plpgsql/drop-function)
- [DROP PROCEDURE](https://neon.com/postgresql/plpgsql/drop-procedure)
- [Stored Procedure with INOUT Parameters](https://neon.com/postgresql/plpgsql/stored-procedure-with-inout-parameters)
- [Introduction to Stored Procedures](https://neon.com/postgresql/plpgsql/introduction-to-postgresql-stored-procedures)
- [SELECT INTO](https://neon.com/postgresql/plpgsql/pl-pgsql-select-into)
- [ASSERT Statement](https://neon.com/postgresql/plpgsql/pl-pgsql-assert)
- [Dollar-Quoted String Constants](https://neon.com/postgresql/plpgsql/dollar-quoted-string-constants)