CASE in PL/pgSQL in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the CASE statement in PL/pgSQL?

The CASE statement lets you choose between different actions based on conditions, like a traffic light deciding what to do next.

beginner

Write the basic syntax of a simple CASE statement in PL/pgSQL.

CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default_result END;

intermediate

How does a searched CASE differ from a simple CASE in PL/pgSQL?

A searched CASE checks conditions that can be any boolean expressions, while a simple CASE compares one expression to fixed values.

beginner

Can you use CASE inside a PL/pgSQL function to return different results?

Yes, CASE is often used inside functions to return different outputs based on input or other conditions.

intermediate

What happens if no WHEN condition matches and there is no ELSE in a CASE statement?

The CASE returns NULL if no WHEN matches and there is no ELSE clause.

Which keyword starts a CASE statement in PL/pgSQL?

A CASE

B IF

C SWITCH

D SELECT

The CASE statement always starts with the keyword CASE.

In a simple CASE, what does the expression after CASE do?

A It defines the ELSE result

B It is ignored

C It must be a boolean

D It is compared to each WHEN value

The expression after CASE is compared to each WHEN value to find a match.

What does ELSE do in a CASE statement?

A Ends the CASE statement

B Defines the default result if no WHEN matches

C Starts a new CASE

D Is required for CASE to work

ELSE provides a default result if none of the WHEN conditions match.

Which of these is a searched CASE example?

A CASE WHEN x > 10 THEN 'big' ELSE 'small' END

B CASE x WHEN 1 THEN 'one' ELSE 'other' END

C CASE x END

D CASE WHEN THEN END

A searched CASE uses WHEN with conditions like 'x > 10'.

What result does CASE return if no WHEN matches and ELSE is missing?

A First WHEN result

B Error

C NULL

D Empty string

Without ELSE, CASE returns NULL if no WHEN matches.

Explain how to use a simple CASE statement in PL/pgSQL with an example.

Think of CASE as a way to pick a result based on one value.

Describe the difference between simple CASE and searched CASE in PL/pgSQL.

One compares values directly, the other checks conditions.