Re: select where true, or select where input = '$var'

> On 19 Feb 2015, at 21:39, zach cruise <zachc1980(at)gmail(dot)com> wrote:
>
> i want to select based on input, but if input is not provided or if
> input is empty, then i want to select all rows.
>
> 1 select *
> 2 from table
> 3 if input = '' then
> 4 where true
> 5 else
> 6 where input = '$sanitized_variable'
> 7 end if;
> (syntax error at 3)

Well yeah, SQL doesn't have an if-statement and you don't need one here:

select *
from table
where ('$sanitized_variable' = '' and input is null)
or ('$sanitized_variable' <> '' and input = '$sanitized_variable');

That can be shortened, but I think the message is clearer this way.

Question though, when do you consider "input" empty? Is that when input = '' or when input is null?
In the latter case, what's the correct behaviour when '$sanitized_variable' = ''?

Cheers.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.