On Thu, 12 Dec 2002, Francois Suter wrote:
> Hi all,
>
> I don't know who is responsible for the PostgreSQL's interactive
> documentation so I'm writing to the general ML.
>
> As you probably all noticed, the idocs are pretty limited because it is not
> possible to search several words at the same time (either with AND or OR),
> as explained in the search help.
>
> Since I recently had to develop something similar (in PHP), here is the code
> (attached file), if it can be useful and make the idocs better. It's maybe
> not the most efficient algorithm in the world, because it's the first
> version and I didn't yet have time to optimize it, but it does the job.
>
> Usage is as follows:
>
> - assume the search text field is called "search"
> - assume the text field in the database is called "text"
>
> you can write something like that:
>
> $query = "SELECT * FROM table_name WHERE
> ".searchString("search","text",true,false);
>
> The third parameter is true for a case-insensitive search (using ILIKE). The
> fourth parameters forces all words in the search string to be considered
> with "AND".
>
> The way it works is that it takes the search string and tokenizes it around
> blanks and + signs. Blanks are interpreted as ORs and + as ANDs. Hence for a
> search like "word1+word2 word3", it will return the following string:
>
> "text" ILIKE '%word1%' AND "text" ILIKE '%word2%' OR "text" ILIKE '%word3%'
>
> Pretty rough, but it works. Hope this can help somehow.
Actually, has anyone looked at throwing htdig on top of the idocs site?
The newest betas are pretty stable (we are using them in production at
work) and allow phrase searching, and can do either an ALL, AND, or
Boolean type of search. It includes soundex fuzzy matching, as well as
many other features (i.e. expands work to works, working, worked, etc...)
It's a great search engine and VERY fast. I love postgresql, but for this
kind of thing, I prefer htdig.