Thread: NEWBEE: 'WHERE true' question

Hello,

I have just started learning about PostgreSQL.  I am fairly familiar
with MySQL/Perl DBI.

I have some perl code that I downloaded that talks to a pgsql database
and am trying to understand one of the queries in this code.

A snippet from this code is as follows:

...
  $where = 'true';
  if ($form->{number}) {
    $callback .= "&number=$form->{number}";
    $where .= " AND number ~* '$form->{number}'";
  }
  if ($form->{description}) {
    $callback .= "&description=$form->{description}";
    $where .= " AND description ~* '$form->{description}'";
  }

  $query = qq|SELECT id, number, description, onhand, unit, sellprice
                FROM parts
                WHERE $where
                ORDER BY $form->{sort}
             |;
...

Now if the two 'if' conditionals are false, the query becomes:

SELECT id, number, description, onhand, unit, sellprice
                FROM parts
                WHERE true
                ORDER BY number


What does it mean here for the WHERE to be true?  What is being tested
for true?  Is this just a 'place holder', if you will, for the WHERE
field in case the two 'if' conditionals are false and so the WHERE field
is not blank?

Thanks,
Murrah Boswell

On Wed, Nov 15, 2000 at 09:16:37PM -0700, some SMTP stream spewed forth:
> Hello,
>
*snip*
> A snippet from this code is as follows:
>
> ...
>   $where = 'true';
>   if ($form->{number}) {
>     $callback .= "&number=$form->{number}";
>     $where .= " AND number ~* '$form->{number}'";
>   }
>   if ($form->{description}) {
>     $callback .= "&description=$form->{description}";
>     $where .= " AND description ~* '$form->{description}'";
>   }
>
>   $query = qq|SELECT id, number, description, onhand, unit, sellprice
>                 FROM parts
>                 WHERE $where
>                 ORDER BY $form->{sort}
>              |;
> ...
>
> Now if the two 'if' conditionals are false, the query becomes:
>
> SELECT id, number, description, onhand, unit, sellprice
>                 FROM parts
>                 WHERE true
>                 ORDER BY number
>
>
> What does it mean here for the WHERE to be true?  What is being tested
> for true?  Is this just a 'place holder', if you will, for the WHERE
> field in case the two 'if' conditionals are false and so the WHERE field
> is not blank?
>

Er, I hope this helps.
< http://www.postgresql.org/docs/user/sql-select.htm#SQL-WHERE >

It looks like the 'true' is just in there to ensure that all rows are
returned.
I can't think of why someone wolud do that..


G'luck and
cheers
gh

> Thanks,
> Murrah Boswell
>

I have done this on occasion.  I do it for the reason Murrah Boswell
suggested, namely "so the WHERE field is not blank."  Murrah asked for an
explanation, so here goes:
Every ANDed and ORed part of a WHERE clause evaluates to a true or false
value.  A query returns rows for which the entire WHERE clause evaluates to
true.  For example, if you have the following table tbl_names:
ID | Name
-------------
1 | Joe
2 | Mary
3 | Joe

and a the following SQL statement:
SELECT * FROM tbl_names WHERE Name = 'Joe' AND ID = 3;

for row (1) the WHERE clause evaluates to True AND False (=False)
so the row is not returned

for row (2) the WHERE clause evaluates to False AND False (=False)
so the row is not returned

for row (3) the WHERE clause evaluates to True AND True (=True)
so the row is returned

You can take any SQL WHERE clause, enclose it in parentheses, tack "AND
True" on to the end of it, and it will return exactly what it did before.
If you are building a dynamic SQL statement that may have no real WHERE
conditions, you can use this as an easy way to ensure that your statement
will always be syntactically correct.

David Boerwinkle

-----Original Message-----
From: GH <grasshacker@over-yonder.net>
Cc: PGSQL Novice List <pgsql-novice@postgresql.org>
Date: Wednesday, November 15, 2000 9:39 PM
Subject: Re: [NOVICE] NEWBEE: 'WHERE true' question


>On Wed, Nov 15, 2000 at 09:16:37PM -0700, some SMTP stream spewed forth:
>> Hello,
>>
>*snip*
>> A snippet from this code is as follows:
>>
>> ...
>>   $where = 'true';
>>   if ($form->{number}) {
>>     $callback .= "&number=$form->{number}";
>>     $where .= " AND number ~* '$form->{number}'";
>>   }
>>   if ($form->{description})

>>     $callback .= "&description=$form->{description}";
>>     $where .= " AND description ~* '$form->{description}'";
>>   }
>>
>>   $query = qq|SELECT id, number, description, onhand, unit, sellprice
>>                 FROM parts
>>                 WHERE $where
>>                 ORDER BY $form->{sort}
>>              |;
>> ...
>>
>> Now if the two 'if' conditionals are false, the query becomes:
>>
>> SELECT id, number, description, onhand, unit, sellprice
>>                 FROM parts
>>                 WHERE true
>>                 ORDER BY number
>>
>>
>> What does it mean here for the WHERE to be true?  What is being tested
>> for true?  Is this just a 'place holder', if you will, for the WHERE
>> field in case the two 'if' conditionals are false and so the WHERE field
>> is not blank?
>>
>
>Er, I hope this helps.
>< http://www.postgresql.org/docs/user/sql-select.htm#SQL-WHERE >
>
>It looks like the 'true' is just in there to ensure that all rows are
>returned.
>I can't think of why someone wolud do that..
>
>
>G'luck and
>cheers
>gh
>
>> Thanks,
>> Murrah Boswell
>>
>