Re: fts, compond words?

On 12/5/05, Marcus Engene <mengpg(at)engene(dot)se> wrote:
> Oleg Bartunov wrote:
> > On Mon, 5 Dec 2005, Marcus Engene wrote:
> >
> >> Hi,
> >>
> >> I use the tsearch full text search with pg 8.0.3. It works great, but
> >> I wonder if it's possible to search for compound words?
> >> Ie if I search for "New York" i want to get a match on
> >> New York has traffic problems.
> >> but not on
> >> New axe murderer incident in brittish York.
> >>
> >> Is this possible?
> >>
> >> I don't use any wrapper, just
> >> select
> >> ...
> >> from
> >> ...
> >> where
> >> idxfti @@ to_tsquery('default', 'searchstring')
> >
> >
> >
> > ranking function is what you need. Read documentation.
> >
>
> Hi,
>
> I realized from the documentation that I'm not looking for
> compound words after all, I meant "exact phrase".
>
> I can't see how to make rank tell me which results has an
> exact phrase? Like "there must be a occurence of 'new' before
> 'york'" (stemmed not really exact phrase)?
>

What you'll want to do is check the original text for the exact phrase
after the tsearch2 index has given you some targets.

Given table foo:

CREATE TABLE foo (
id serial primary key,
txt text,
ts2 tsvector
);

use query:

SELECT id FROM foo WHERE ts2 @@ to_tsquery('new&york') AND txt ILIKE
'%new york%';

You can get rid of the '%'s if you want the entire txt column to match
the search phrase.

--
Mike Rylander
mrylander(at)gmail(dot)com
GPLS -- PINES Development
Database Developer
http://open-ils.org