The "case" statement

Syntax

Grammar
Diagram

plpgsql_case_stmt ::= plpgsql_searched_case_stmt
                      | plpgsql_simple_case_stmt

plpgsql_searched_case_stmt ::= CASE  plpgsql_searched_when_leg [ ... ] 
                                [ ELSE 
                                  [ plpgsql_executable_stmt [ ... ] ] ] 
                                END CASE

plpgsql_searched_when_leg ::= WHEN guard_expression THEN 
                              [ plpgsql_executable_stmt [ ... ] ]

plpgsql_simple_case_stmt ::= CASE target_expression  
                             plpgsql_simple_when_leg [ ... ]  
                             [ ELSE 
                               [ plpgsql_executable_stmt [ ... ] ] ]  
                             END CASE

plpgsql_simple_when_leg ::= WHEN candidate_expression THEN 
                            [ plpgsql_executable_stmt [ ... ] ]

plpgsql_case_stmt

plpgsql_searched_case_stmt

plpgsql_searched_when_leg

plpgsql_simple_case_stmt

plpgsql_simple_when_leg

Semantics

Just like the if statement, the case statement lets you specify one or several lists of executable statements so that a maximum of one of those lists will be selected. Each list is guarded by a boolean guard_expression—and each is tested in turn, in the order in which they are written.

The guard_expression is written:

in the searched case statement, explicitly as an boolean expression in each plpgsql_searched_when_leg
in the simple case statement, implicitly by writing the expression on the left hand side of the implied equality operator immediately after the case keyword that introduces the statement and by writing the expression on the right hand side of the implied equality operator immediately after the when keyword in each plpgsql_simple_when_leg.

When a guard_expression evaluates to false, the point of execution immediately moves to the next guard_expression, skipping the statement list that it guards. As soon as a guard_expression evaluates to true, the statement list that it guards is executed; and on completion of that list, control passes to the first statement after the end case of the case statement—skipping the evaluation of any remaining guard_expressions. This economical testing of the guard_expressions is common to many programming languages. It is a particular example of so-called short-circuit evaluation.

Critically, and in contrast to the if statement's semantics, the 20000 (case not found) error occurs if every guard expression (whether explicit or implicit) evaluates to false.

Here is the template of the simplest case statement:

case
  when <guard_expression> then
    <guarded statement list>
end case;

However, this degenerate form is not useful because it has the same effect as this:

assert <guard_expression>;
<guarded statement list>

In other words, an exception is guaranteed unless the guard_expression evaluates to true.

The two-leg form of the simple case statement is useful when the target expression is a boolean variable that (if the larger context is bug-free) will always be not null:

create procedure s.p(b in boolean)
  set search_path = pg_catalog, pg_temp
  language plpgsql
as $body$
begin
  case b
    when  true then
      <some actions>
    when false then
      <some alternative actions>
  end case;
end;
$body$;

Try this counter example:

call s.p(null);

This causes the 20000 error:

ERROR:  case not found
HINT:  CASE statement is missing ELSE part.
CONTEXT:  PL/pgSQL function s.p(boolean) line 3 at CASE

honoring the defined case statement semantics.

A case statement can always be rewritten as an if statement. But care must be taken to implement the case not found semantics when the to-be-rewritten case statement doesn't have a bare else branch.

Programmers argue about their preferences for the choice between a case statement and an if statement. See the tip at the send of the if statement page.