Control Flow: IF and CASE

Conditional statements allow your PL/pgSQL code to take different paths based on data values.

IF … THEN … END IF

IF condition THEN
  statements;
END IF;

Example:

IF v_salary > 50000 THEN
  RAISE NOTICE 'High earner';
END IF;

IF … THEN … ELSE … END IF

IF v_score >= 70 THEN
  v_result := 'Pass';
ELSE
  v_result := 'Fail';
END IF;

IF … ELSIF … ELSE … END IF

IF v_score >= 90 THEN
  v_grade := 'A';
ELSIF v_score >= 80 THEN
  v_grade := 'B';
ELSIF v_score >= 70 THEN
  v_grade := 'C';
ELSE
  v_grade := 'F';
END IF;

Note: PL/pgSQL uses ELSIF (one word), just like Oracle PL/SQL.

Comparison Operators

Operator	Meaning
=	Equal
<> or !=	Not equal
<, >	Less / greater than
<=, >=	Less / greater than or equal
IS NULL	Value is NULL
IS NOT NULL	Value is not NULL

Logical Operators

IF v_age >= 18 AND v_verified = TRUE THEN
  -- allow access
END IF;

IF v_status = 'admin' OR v_is_owner = TRUE THEN
  -- allow edit
END IF;

NULL in Conditions

NULL is never equal to anything — always use IS NULL / IS NOT NULL:

-- Wrong:
IF v_name = NULL THEN ...

-- Correct:
IF v_name IS NULL THEN ...

Simple CASE Statement

CASE v_day
  WHEN 'Mon' THEN RAISE NOTICE 'Monday';
  WHEN 'Tue' THEN RAISE NOTICE 'Tuesday';
  ELSE             RAISE NOTICE 'Other day';
END CASE;

Searched CASE Statement

CASE
  WHEN v_temp < 0  THEN RAISE NOTICE 'Freezing';
  WHEN v_temp < 15 THEN RAISE NOTICE 'Cold';
  WHEN v_temp < 25 THEN RAISE NOTICE 'Mild';
  ELSE                  RAISE NOTICE 'Hot';
END CASE;

If no WHEN matches and there is no ELSE, a CASE_NOT_FOUND exception is raised.

CASE Expression (in assignments)

CASE can also appear on the right side of an assignment or inside a RAISE:

v_label := CASE
              WHEN v_score >= 70 THEN 'Pass'
              ELSE 'Fail'
            END;

Practical Example