Mike Martin <redtux1@gmail.com> writes:
> I was always under the impression that a case expression could only be
> on the right side of a where expression ie:
> WHERE fieldname=<cse expression>
> Is this a postgres extention, cant find any documentation on this
SQL has always had two forms of CASE: you can do
CASE
WHEN boolean_expression1 THEN value1
[ WHEN boolean_expression2 THEN value2 ... ]
[ ELSE valueN ]
END
or you can do
CASE test_value
WHEN comparison_value1 THEN value1
[ WHEN comparison_value2 THEN value2 ... ]
[ ELSE valueN ]
END
The latter is effectively the same as
CASE
WHEN test_value = comparison_value1 THEN value1
[ WHEN test_value = comparison_value2 THEN value2 ... ]
[ ELSE valueN ]
END
except test_value is only supposed to be evaluated once.
This goes back at least as far as SQL-92.
It is documented, see
https://www.postgresql.org/docs/current/functions-conditional.html
regards, tom lane