Re: using text search

Hi Rafal

Maybe you should think or consider to have normalized database to do this kind of select.

I mean that if the keyword is a key you could add a column to table orders and have it ordered and indexed without
havingto reevaluate the query every time. 

About your question, if you are searching a text inside another text you could obtain bizarre results and consume a lot
ofresources, but my approximation to your problem would be doing direct select instead of join select (in fact it is
almostthe same, but more readable on this cases), like: 

select o.* from orders as o, keywords as k
where o.info like ('%' || k.phrase || '%')

Hope help you.

Bye for now

El 22/04/2013, a las 14:15, Rafał Pietrak <rafal@zorro.isa-geek.com> escribió:

> ... or not (I'm not quite sure)
>
> Hello,
>
> I have the following tables:
>
> CREATE TABLE orders (info text, ....);
> CREATE TABLE keywords (phrase text, .....);
>
> And I need to find all the ORDERS rows, which conain a PHRASE present in the info column ... like so:
> SELECT o.* from orders o join keywords k on (o.info ~~ '%' || k.phrase || '%');
>
> ... only this does not work, since:
> ERROR:  argument of JOIN/ON must be type boolean, not type text
>
> is this possible in SQL? Or may be this a job for "ts_something()" (havent' learned to use them, yet)???
>
> -R
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Alfonso Afonso
(personal)