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

zach cruise wrote
> sorry, corrected below:
>
> 1 select *
> 2 from table
> 3 if '$sanitized_variable' = '' then -- $variable not provided or
> undefined or empty,
> 4 where true -- so select every row/record
> 5 else
> 6 where input = '$sanitized_variable' -- variable provided or defined
> or not-empty, so select only matching rows/records where input is a
> column/field
> 7 end if;

You cannot directly put that kind of logic in SQL. You can normalize the
input so that if it is "not provided or undefined" you convert it into
"empty" and then write a single query that recognizes the "empty" input as
being the "select-all" form and anything non-empty input as being a limited
form.

SELECT ... FROM ... WHERE ($1::text = '') OR ($1::text = input_col)

COALESCE(...) could also possibly be useful...

David J.

--
View this message in context: http://postgresql.nabble.com/select-where-true-or-select-where-input-var-tp5838612p5838638.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.