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 ...
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.
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
ELSEstatements are executed; but ifELSEis not present, then aCASE_NOT_FOUNDexception 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;
ERROR: case not found. HINT: CASE statement is missing ELSE part. If you need I'll post relevant script part.