CODEWITHSUNDEEP
postgresqlconditional-statementscaseplpgsql
Yohan Hirimuthugoda
Yohan Hirimuthugoda

Reputation: 1051

PostgreSQL CASE usage in functions

Can't we use CASE condition outside SQL SELECT statements?

E.g.:

CASE 
  WHEN old.applies_to = 'admin' THEN _applies_to = 'My Self'
  ELSE _applies_to = initcap(old.applies_to)
END
 _summary = _summary || '<li>Apply To: ' || _applies_to || '</li>';

I get the following error:

ERROR:  syntax error at or near "_summary"
LINE 86: _summary = _summary || '<li>Apply To: ' || _applies ...

Upvotes: 28

Views: 44903

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656431

This concerns the conditional control structure CASE of the procedural language PL/pgSQL, to be used in PL/pgSQL functions, procedures, or DO statements.
Not to be confused with the CASE expression of SQL. Different language! Subtly different syntax rules.

While SQL CASE can be embedded in SQL expressions inside PL/pgSQL code, you cannot have stand-alone SQL CASE expressions (would be nonsense).

-- inside a plpgsql code block:
CASE 
   WHEN old.applies_to = 'admin' THEN
      _applies_to := 'My Self';
   ELSE
      _applies_to := initcap(old.applies_to);
END CASE;

You must use PL/pgSQL statements, terminated with a semicolon (;) and END CASE; to close it.

Is ELSE required?

PL/pgSQL CASE expects an ELSE branch and raises an error if it's missing when reached. The manual:

If no match is found, the ELSE statements are executed; but if ELSE is not present, then a CASE_NOT_FOUND exception is raised.

You can use an empty ELSE:

CASE 
   WHEN old.applies_to = 'admin' THEN
      _applies_to := 'My Self';
   ELSE
      --  do nothing
END CASE;

This is different for SQL CASE where ELSE is optional. If the ELSE keyword is present an expression must be given. Without ELSE, it defaults to NULL. Like:

CASE WHEN old.applies_to = 'admin' THEN 'My Self'
  -- ELSE null  -- optional
END;

Upvotes: 45

Related Questions