SELECT woes

Yuck.

My suggestion would be to install the tsearch or tsearch2 module, and
modify the on-insert trigger to include the contents of all the fields
in your table. There are examples in the tsearch readme, but you can
set the trigger on multiple columns. Then that query would probably be
very fast and would turn into
select * from table where master_idx ## 'my|name|is|bill'
You can also do more powerful levels of grouping and boolean logic.
Jeff
On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote:

> I wish to SELECT * FROM testTable WHERE any field contains any word in
> the
> string "my name is bill"
> All I can come up with is this.
> SELECT * FROM testTable WHERE testfeild LIKE '%my%' OR testfield2 LIKE
> '%my%' OR testfeild LIKE '%name%' OR testfield2 LIKE '%name%' OR
> testfeild LIKE '%is%' OR testfield2 LIKE '%is%' OR testfeild LIKE
> '%bill%' OR testfield2 LIKE '%bill%'
>
> Is there a better/more efficient way?
>
> Kind regards
> Kevin
>
> --
> ______
> (_____ \
> _____) ) ____ ____ ____ ____
> | ____/ / _ ) / _ | / ___) / _ )
> | | ( (/ / ( ( | |( (___ ( (/ /
> |_| \____) \_||_| \____) \____)
> Kevin Waterson
> Port Macquarie, Australia
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

I wish to SELECT * FROM testTable WHERE any field contains any word in the
string "my name is bill"
All I can come up with is this.
SELECT * FROM testTable WHERE testfeild LIKE '%my%' OR testfield2 LIKE '%my%' OR testfeild LIKE '%name%' OR testfield2 LIKE '%name%' OR testfeild LIKE '%is%' OR testfield2 LIKE '%is%' OR testfeild LIKE '%bill%' OR testfield2 LIKE '%bill%'

Is there a better/more efficient way?

Kind regards
Kevin

--
______
(_____ \
_____) ) ____ ____ ____ ____
| ____/ / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \____) \_||_| \____) \____)
Kevin Waterson
Port Macquarie, Australia

This one time, at band camp, Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> wrote:

> Yuck.
>
> My suggestion would be to install the tsearch or tsearch2 module, and
> modify the on-insert trigger to include the contents of all the fields
> in your table. There are examples in the tsearch readme, but you can
> set the trigger on multiple columns. Then that query would probably be
> very fast and would turn into
> select * from table where master_idx ## 'my|name|is|bill'
> You can also do more powerful levels of grouping and boolean logic.
> Jeff
> On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote:

Then it would not be compatible with other databases.

Kind regards
Kevin

--
______
(_____ \
_____) ) ____ ____ ____ ____
| ____/ / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \____) \_||_| \____) \____)
Kevin Waterson
Port Macquarie, Australia

* Kevin Waterson <kevin(at)oceania(dot)net> [0952 09:52]:
> This one time, at band camp, Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> wrote:
>
> > Yuck.
> >
> > My suggestion would be to install the tsearch or tsearch2 module, and
> > modify the on-insert trigger to include the contents of all the fields
> > in your table. There are examples in the tsearch readme, but you can
> > set the trigger on multiple columns. Then that query would probably be
> > very fast and would turn into
> > select * from table where master_idx ## 'my|name|is|bill'
> > You can also do more powerful levels of grouping and boolean logic.
> > Jeff
> > On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote:
>
> Then it would not be compatible with other databases.

True,
but I don't think there is an standard SQL way of doing what you want.

The alternative would be to fidn all rows that contain 'bill'
and then have your application dump the ones that don't contain the other
words - that would be more portable but is really clanky, and
adds complexity to the client.

--
An idea is not responsible for the people who believe in it.
Rasputin :: Jack of All Trades - Master of Nuns

On Tuesday, September 2, 2003, at 04:58 AM, Kevin Waterson wrote:

> This one time, at band camp, Jeffrey Melloy
> <jmelloy(at)visualdistortion(dot)org> wrote:
>
>> Yuck.
>>
>> My suggestion would be to install the tsearch or tsearch2 module, and
>> modify the on-insert trigger to include the contents of all the fields
>> in your table. There are examples in the tsearch readme, but you can
>> set the trigger on multiple columns. Then that query would probably
>> be
>> very fast and would turn into
>> select * from table where master_idx ## 'my|name|is|bill'
>> You can also do more powerful levels of grouping and boolean logic.
>> Jeff
>> On Tuesday, September 2, 2003, at 02:51 AM, Kevin Waterson wrote:
>
> Then it would not be compatible with other databases.
>
You didn't list that as a constraint. :)
> Kind regards
> Kevin
>
>
> --
> ______
> (_____ \
> _____) ) ____ ____ ____ ____
> | ____/ / _ ) / _ | / ___) / _ )
> | | ( (/ / ( ( | |( (___ ( (/ /
> |_| \____) \_||_| \____) \____)
> Kevin Waterson
> Port Macquarie, Australia
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly