Thread: Question about WHERE CASE

Hi,

where requires a boolean expression, for instance
select * from stuff where true;

Hence "CASE WHEN $1=dir THEN TRUE ELSE metadir=$1 END" returns a boolean value everything is fine.

Regards martin

I have come across the following construct which works in postgres

WHERE CASE WHEN $1=dir THEN TRUE ELSE metadir=$1 END

via (https://stackoverflow.com/questions/49859153/filter-with-yes-no-and-all-in-postgres)

case when ('yes'= lower($1)) then (phone_number is not null)      when ('no'=lower($1)) then (phone_number is null)      else true end

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

thanks

Mike

-- 

Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: Dr. Dirk Goldner, ppa. Melanie Lillich

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