CASE in PL/pgSQL in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE expression in PL/pgSQL function

What is the output of the following PL/pgSQL function when called with input value 15?

CREATE OR REPLACE FUNCTION test_case(val INTEGER) RETURNS TEXT AS $$
DECLARE
  result TEXT;
BEGIN
  result := CASE 
    WHEN val < 10 THEN 'Less than 10'
    WHEN val <= 20 THEN 'Between 10 and 20'
    ELSE 'Greater than 20'
  END;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT test_case(15);

PostgreSQL

CREATE OR REPLACE FUNCTION test_case(val INTEGER) RETURNS TEXT AS $$
DECLARE
  result TEXT;
BEGIN
  result := CASE 
    WHEN val < 10 THEN 'Less than 10'
    WHEN val <= 20 THEN 'Between 10 and 20'
    ELSE 'Greater than 20'
  END;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT test_case(15);

A 'Greater than 20'

B 'Less than 10'

C 'Between 10 and 20'

D NULL

💡 Hint

Think about which WHEN condition matches the input value 15 first.

The CASE expression checks conditions in order. Since 15 is not less than 10 but is less than or equal to 20, it returns 'Between 10 and 20'.

📝 Syntax

intermediate

Identify the syntax error in CASE statement

Which option contains a syntax error in the CASE statement inside a PL/pgSQL block?

PostgreSQL

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

A Missing semicolon after END of CASE expression

B Missing ELSE clause in CASE expression

C No syntax error; code is correct

D Missing END CASE keyword

💡 Hint

Check if the CASE expression is properly terminated with a semicolon.

In PL/pgSQL, the CASE expression must end with a semicolon after END to complete the assignment statement.

❓ optimization

advanced

Optimizing multiple CASE statements in PL/pgSQL

You have multiple CASE statements checking the same variable in a PL/pgSQL function. Which approach is more efficient?

PostgreSQL

DECLARE
  status INTEGER := 2;
  message TEXT;
BEGIN
  -- Option 1: Multiple CASE statements
  IF status = 1 THEN
    message := 'Start';
  ELSIF status = 2 THEN
    message := 'Processing';
  ELSE
    message := 'Unknown';
  END IF;

  -- Option 2: Single CASE statement
  message := CASE status
    WHEN 1 THEN 'Start'
    WHEN 2 THEN 'Processing'
    ELSE 'Unknown'
  END;
END;

A Option 2 is more efficient because it uses a single CASE expression

B Option 1 is more efficient because IF statements are faster

C Both options have the same efficiency

D Option 1 is more readable and thus preferred

💡 Hint

Consider how many times the variable is evaluated and how concise the code is.

Using a single CASE expression evaluates the variable once and assigns the result directly, which is more efficient and cleaner.

🧠 Conceptual

advanced

Understanding CASE with NULL values in PL/pgSQL

What will be the output of this PL/pgSQL snippet when input_value is NULL?

DECLARE
  input_value INTEGER := NULL;
  output TEXT;
BEGIN
  output := CASE input_value
    WHEN 1 THEN 'One'
    WHEN NULL THEN 'Null'
    ELSE 'Other'
  END;
  RETURN output;
END;

PostgreSQL

DECLARE
  input_value INTEGER := NULL;
  output TEXT;
BEGIN
  output := CASE input_value
    WHEN 1 THEN 'One'
    WHEN NULL THEN 'Null'
    ELSE 'Other'
  END;
  RETURN output;
END;

A Raises an error due to NULL comparison

B 'Null'

C 'One'

D 'Other'

💡 Hint

Remember how NULL comparisons behave in SQL and PL/pgSQL CASE expressions.

In CASE expressions, WHEN NULL does not match NULL because NULL is not equal to anything, even NULL. So ELSE branch is taken.

🔧 Debug

expert

Debugging unexpected CASE behavior in PL/pgSQL

A developer wrote this PL/pgSQL function but it always returns 'Unknown' regardless of input. What is the bug?

CREATE OR REPLACE FUNCTION check_status(code TEXT) RETURNS TEXT AS $$
DECLARE
  result TEXT;
BEGIN
  result := CASE code
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Called as: SELECT check_status('a');

PostgreSQL

CREATE OR REPLACE FUNCTION check_status(code TEXT) RETURNS TEXT AS $$
DECLARE
  result TEXT;
BEGIN
  result := CASE code
    WHEN 'A' THEN 'Active'
    WHEN 'I' THEN 'Inactive'
    ELSE 'Unknown'
  END;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Called as: SELECT check_status('a');

A Variable 'result' is not initialized before assignment

B CASE expression is case-sensitive, so 'a' does not match 'A'

C Function lacks RETURNS clause

D Missing ELSE clause causes default to 'Unknown'

💡 Hint

Check how string comparisons work in CASE expressions.

The CASE expression compares strings exactly, so lowercase 'a' does not match uppercase 'A'.