Thread: Searching the Database

Hi All,

Is there a tool/plugin for Postgres that i can use to "search" the
database.

Aka, if i have a database with a lot of content, can i search the
database like a search engine would?

Something like this maybe?:

psql> search mycolumn1, mycolumn2 from mytable
            where keywords="clock radio", scheme="simple";


where the database looks up the "scheme" and has a set of rules
associated with say "the 'simple' scheme" which says:

  * search the columns - and returns the results in the following
    order:
         * rows with "all the keywords together" are listed first
         * rows with "all the keywords in the columns somewhere"
         * rows with as many of the keywords as possible
         * rows with any keyword
         * ...

What this would provide is almost a "result set in order of relevance" -
much like search engine results.

Is there anything that can do the above? (or similar to it).

I guess i can always take the "keywords" and build about 10 SQL queries
from it, with the first query returning the most relevant reuslt etc...
but if there was something in the database that did that already, it
would be fantastic.

kind regards,

simran.

I don't think there is anything inbuilt to do what you want,
but contrib/tsearch may well be worth a look as might:
http://openfts.sourceforge.net
(I think this does relevance sorting).
hth,
- Stuart

> -----Original Message-----
> From: simran [mailto:simran@cse.unsw.edu.au]
> Sent: 22 August 2002 03:21
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Searching the Database
>
>
> Hi All,
>
> Is there a tool/plugin for Postgres that i can use to "search" the
> database.
>
> Aka, if i have a database with a lot of content, can i search the
> database like a search engine would?
>
> Something like this maybe?:
>
> psql> search mycolumn1, mycolumn2 from mytable
>             where keywords="clock radio", scheme="simple";
>
>
> where the database looks up the "scheme" and has a set of rules
> associated with say "the 'simple' scheme" which says:
>  
>   * search the columns - and returns the results in the following
>     order:
>          * rows with "all the keywords together" are listed first
>          * rows with "all the keywords in the columns somewhere"
>          * rows with as many of the keywords as possible
>          * rows with any keyword
>          * ...
>
> What this would provide is almost a "result set in order of
> relevance" -
> much like search engine results.
>
> Is there anything that can do the above? (or similar to it).
>
> I guess i can always take the "keywords" and build about 10
> SQL queries
> from it, with the first query returning the most relevant
> reuslt etc...
> but if there was something in the database that did that already, it
> would be fantastic.
>
> kind regards,
>
> simran.
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>