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 having to 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 of resources, but my approximation to your problem would be doing direct select instead of join select (in fact it is almost the 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(at)zorro(dot)isa-geek(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Alfonso Afonso
(personal)