PL/pgSQL CASE

Master the PL/pgSQL CASE construct — Simple CASE for value matching, Searched CASE for predicate matching, and the critical distinction between CASE statement (ends with END CASE) and CASE expression (ends with END alone, used in assignments). Covers CASE_NOT_FOUND exception handling.

PL/pgSQL's CASE statement selects one of several blocks of code to execute, based on a condition or value match. It's the structured alternative to long IF ... ELSIF ... ELSIF chains, especially when you're matching one expression against multiple values.

CASE comes in two forms:

A common point of confusion: CASE appears in two different roles in PL/pgSQL.

This page focuses on the CASE statement. The CASE expression works the same way the SQL CASE does — and we'll show how to use it for assignment in the last example.

The search_expression is evaluated once. Each WHEN compares it (using =) against a value or list of values. The first match wins. If nothing matches and there's no ELSE, you get a CASE_NOT_FOUND error.

Match a day-of-week number to its name. The expression is matched against literal values:

DO $$
DECLARE
    day_num   INTEGER := 3;
    day_name  TEXT;
BEGIN
    CASE day_num
        WHEN 1 THEN day_name := 'Monday';
        WHEN 2 THEN day_name := 'Tuesday';
        WHEN 3 THEN day_name := 'Wednesday';
        WHEN 4 THEN day_name := 'Thursday';
        WHEN 5 THEN day_name := 'Friday';
        WHEN 6, 7 THEN day_name := 'Weekend';
        ELSE day_name := 'Invalid';
    END CASE;

    RAISE NOTICE 'Day %: %', day_num, day_name;
END
$$;

The WHEN 6, 7 form matches either value — useful for grouping cases that share a body. The ELSE handles anything outside 1-7.

No expression on the CASE line. Each WHEN has a full Boolean condition. The first true condition wins. Like the simple form, no match plus no ELSE raises CASE_NOT_FOUND.

Searched CASE is what you reach for when each branch tests something different — ranges, multiple variables, complex predicates. Anything an IF ... ELSIF chain could express.

Pick a shipping method based on the package weight (a range, not a fixed value — so this is genuine Searched CASE territory):

DO $$
DECLARE
    package_weight   NUMERIC := 3.5;
    shipping_method  VARCHAR(50);
BEGIN
    CASE
        WHEN package_weight <= 1 THEN
            shipping_method := 'Standard';
        WHEN package_weight <= 5 THEN
            shipping_method := 'Priority';
        WHEN package_weight <= 10 THEN
            shipping_method := 'Express';
        ELSE
            shipping_method := 'Special';
    END CASE;

    RAISE NOTICE 'Shipping Method: %', shipping_method;
END
$$;

The big advantage of Searched CASE over Simple CASE: each branch can test different variables and use complex predicates:

DO $$
DECLARE
    is_member    BOOLEAN := TRUE;
    order_total  NUMERIC := 250;
    membership_years INTEGER := 3;
    discount_pct NUMERIC;
BEGIN
    CASE
        WHEN NOT is_member THEN
            discount_pct := 0;
        WHEN order_total >= 500 THEN
            discount_pct := 20;
        WHEN order_total >= 200 AND membership_years >= 2 THEN
            discount_pct := 15;
        WHEN order_total >= 100 THEN
            discount_pct := 5;
        ELSE
            discount_pct := 0;
    END CASE;

    RAISE NOTICE 'Discount: %%%', discount_pct;
END
$$;

This is what Simple CASE can't do — each WHEN tests a different combination of variables.

The other use of CASE: as an expression that evaluates to a value. Useful inside SELECT, in RAISE NOTICE, and for direct assignment without an explicit := ... END CASE; block.

DO $$
DECLARE
    package_weight   NUMERIC := 3.5;
    shipping_method  VARCHAR(50);
BEGIN
    -- CASE here is an expression — evaluates to a value, used in assignment
    shipping_method := CASE
                           WHEN package_weight <= 1  THEN 'Standard'
                           WHEN package_weight <= 5  THEN 'Priority'
                           WHEN package_weight <= 10 THEN 'Express'
                           ELSE                            'Special'
                       END;          -- note: just END, not END CASE

    RAISE NOTICE 'Shipping Method: %', shipping_method;
END
$$;

Two differences from the statement form:

• Each WHEN ... THEN has a single expression after THEN, not a sequence of statements with semicolons.
• Closes with END alone — not END CASE.

If your branches each just assign a value to the same variable, the CASE-expression form is shorter. If branches do different things — multiple assignments, RAISE, side effects — you need the CASE statement.

If no branch matches and there's no ELSE, the CASE statement raises CASE_NOT_FOUND. You can catch it explicitly:

DO $$
DECLARE
    grade CHAR(1) := 'F';
    msg   TEXT;
BEGIN
    BEGIN
        CASE grade
            WHEN 'A' THEN msg := 'Excellent';
            WHEN 'B' THEN msg := 'Good';
            WHEN 'C' THEN msg := 'Average';
            -- no ELSE clause — anything else triggers CASE_NOT_FOUND
        END CASE;

        RAISE NOTICE '%', msg;
    EXCEPTION
        WHEN CASE_NOT_FOUND THEN
            RAISE NOTICE 'Unrecognized grade: %', grade;
    END;
END
$$;

Alternatively, just include an ELSE and avoid the exception path:

CASE grade
    WHEN 'A' THEN msg := 'Excellent';
    WHEN 'B' THEN msg := 'Good';
    WHEN 'C' THEN msg := 'Average';
    ELSE          msg := 'Unrecognized: ' || grade;
END CASE;

1. Use Simple CASE for value matching, Searched CASE for predicate matching. If each WHEN tests a fresh condition, you want Searched.
2. Always include ELSE — defensive programming. Even if you think every value is covered, an unexpected one will raise CASE_NOT_FOUND and abort the function.
3. Use cumulative conditions in Searched CASE for ranges. Once a higher threshold has failed, you don't need to repeat its lower bound. WHEN x <= 5 THEN reads better than WHEN x > 1 AND x <= 5 THEN when previous WHENs already excluded x <= 1.
4. Reach for the CASE expression when assigning a single variable. Shorter than the full statement form.
5. Don't confuse END with END CASE. The expression closes with END; the statement closes with END CASE.
6. If you'd write a long IF/ELSIF chain matching one variable, switch to CASE. Cleaner intent, easier to scan.

• CASE selects one of several blocks of code based on a match. Two flavors: Simple (match one expression against values) and Searched (each branch tests a different condition).
• The statement form ends with END CASE; and runs blocks of statements. The expression form ends with just END and produces a value for assignment.
• If no branch matches and there's no ELSE: the statement raises CASE_NOT_FOUND; the expression evaluates to NULL.
• Use Simple CASE for value matching (like a switch statement), Searched CASE for range/predicate matching.
• Always include an ELSE branch unless you specifically want unmatched values to raise an exception.
• The CASE expression is often a cleaner way to assign a variable than a multi-line CASE statement.