CASE in PL/pgSQL in PostgreSQL

Introduction

The CASE statement helps you choose different actions based on conditions inside your PL/pgSQL code. It works like a simple decision maker.

When you want to run different code depending on a variable's value.

When you need to return different results based on conditions inside a function.

When you want to replace multiple IF-ELSE statements with cleaner code.

When you want to categorize data inside a stored procedure.

When you want to handle multiple possible cases in your database logic.

Syntax

PostgreSQL

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END CASE;

The CASE statement checks each WHEN condition in order.

If none of the WHEN conditions match, the ELSE part runs (optional).

Examples

This example assigns letter grades based on a numeric score.

PostgreSQL

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  ELSE 'F'
END CASE;

This example uses CASE with a variable to return text based on the day.

PostgreSQL

CASE day_of_week
  WHEN 'Monday' THEN 'Start of week'
  WHEN 'Friday' THEN 'End of workweek'
  ELSE 'Midweek'
END CASE;

Sample Program

This code block sets a score, uses CASE to find the grade, and prints the result.

PostgreSQL

DO $$
DECLARE
  score INTEGER := 85;
  grade TEXT;
BEGIN
  grade := CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'F'
  END CASE;
  RAISE NOTICE 'Score: %, Grade: %', score, grade;
END $$;

Important Notes

CASE in PL/pgSQL must end with END CASE; inside procedural blocks.

You can use CASE to assign values or control flow inside functions and DO blocks.

Summary

CASE helps you choose actions based on conditions.

It is cleaner than many IF-ELSE statements.

Use END CASE; to close the CASE block in PL/pgSQL.