Re: Simplifying Text Search

On 11/14/07, Peter Eisentraut <peter_e@gmx.net> wrote:
> I wrote:
> > What we'd need is a way to convert a LIKE pattern into a tsquery
> > ('%foo%bar%' => 'foo & bar').  Then you might even be able to sneak
> > index-optimized text search into existing applications.  Might be worth a
> > try.
>
> Here is how this could work:
>
> CREATE FUNCTION likepattern_to_tsquery(text) RETURNS tsquery

[...]

But that coversion itself is fundamentally flawed, is the problem.

'foo bar'
'fooandbar'
'barfoo and foobar'

'%foo%bar%' matches all 3.
'foo & bar' matches only the first.

If the application currently using LIKE actually wants a word-based
search, it should probably just convert to using tsearch wholesale,
since it doesn't work as intended now.  If it actually wants wildcard
matching behavior, it can't use tsearch at all.