Thread: select where true, or select where input = '$var'
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) i also looked at 'case' but i don't think it applies here. http://www.postgresql.org/docs/9.3/static/functions-conditional.html http://dba.stackexchange.com/questions/41067/getting-select-to-return-a-constant-value-even-if-zero-rows-match
On 2/19/2015 12:39 PM, zach cruise 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.
(metalanguage) if input is provided, then query("SELECT stuff FROM
table WHERE whatever = $INPUT")
else, query("SELECT stuff
FROM table")
in other words, make the decision as to what query to execute OUTSIDE of
sql by invoking different queries based on your application's 'input'.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
> 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. I think you can just use OR: SELECT * FROM table WHERE (input = '' OR input = ?) This is assuming that `input` is a column in your table and ? is the user input, based on the query you provided. But are you sure that's what you mean? Also, if the `input` column can contain nulls you might also want: SELECT * FROM table WHERE (input IS NULL OR input = '' OR input = ?) Paul
> On 19 Feb 2015, at 21:39, zach cruise <zachc1980@gmail.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.
On 2/19/2015 12:39 PM, zach cruise 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. something unclear here, is INPUT a variable in your application program, or is it a field in the table? -- john r pierce 37N 122W somewhere on the middle of the left coast
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; On 2/19/15, John R Pierce <pierce@hogranch.com> wrote: > On 2/19/2015 12:39 PM, zach cruise 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. > > something unclear here, is INPUT a variable in your application program, > or is it a field in the table? > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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.