Re: Question about WHERE CASE

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