CASE in PL/pgSQL in PostgreSQL - Step-by-Step Execution

Concept Flow - CASE in PL/pgSQL

Start

↓

Evaluate CASE expression or conditions

↓

Match WHEN condition?

No→Check next WHEN

Yes↓

Execute THEN result

↓

Exit CASE

↓

If no WHEN matched, execute ELSE (optional)

↓

End CASE

The CASE statement checks conditions one by one and executes the matching THEN part, or ELSE if no match.

Execution Sample

PostgreSQL

DECLARE
  grade CHAR := 'B';
  result TEXT;
BEGIN
  CASE grade
    WHEN 'A' THEN result := 'Excellent';
    WHEN 'B' THEN result := 'Good';
    ELSE result := 'Needs Improvement';
  END CASE;
END;

This code assigns a text result based on the grade value using CASE.

Execution Table

Step	grade	Condition Checked	Condition Result	Action Taken	result
1	'B'	grade = 'A'	False	Check next WHEN	NULL
2	'B'	grade = 'B'	True	result := 'Good'	Good
3	'B'	N/A	N/A	Exit CASE	Good

💡 Condition grade = 'B' matched, so CASE exits after assigning 'Good' to result.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
grade	'B'	'B'	'B'	'B'
result	NULL	NULL	'Good'	'Good'

Key Moments - 2 Insights

Why does the CASE stop checking conditions after the second WHEN?

Because the condition grade = 'B' is True at step 2 in the execution_table, CASE executes that THEN and exits immediately without checking further.

What happens if none of the WHEN conditions match?

If no WHEN matches, the ELSE part runs if present, as shown in the flow after checking all WHENs with no match.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of 'result' after step 1?

A 'Excellent'

B 'Good'

C NULL

D 'Needs Improvement'

💡 Hint

Check the 'result' column in execution_table row for step 1.

At which step does the CASE statement exit?

A Step 3

B Step 1

C Step 2

D After ELSE

💡 Hint

Look at the 'Action Taken' column where it says 'Exit CASE'.

If grade was 'C' instead of 'B', what would 'result' be after CASE?

A 'Good'

B 'Needs Improvement'

C 'Excellent'

D NULL

💡 Hint

Refer to the ELSE action in the concept_flow and execution_table logic.

Concept Snapshot

CASE in PL/pgSQL:
- Checks conditions in order
- Executes THEN for first TRUE WHEN
- ELSE runs if no WHEN matches
- Stops checking after first match
- Syntax: CASE expr WHEN val THEN ... ELSE ... END CASE;

Full Transcript

The CASE statement in PL/pgSQL evaluates conditions one by one. It compares the given expression or conditions to each WHEN clause. When it finds a match, it executes the corresponding THEN part and stops checking further. If no WHEN matches, it executes the ELSE part if provided. This helps choose actions based on different values clearly and simply.