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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general