Re: Simplifying Text Search

Simon Riggs wrote:
> Something Tom Dunstan just mentioned has made me ask the question "Why
> does our full text search feature look so strange?". It's the
> operator-laden syntax that causes the problem.
>
> By any stretch, this query is difficult for most people to understand:
>
> SELECT * FROM text_table
> WHERE to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
>
> Wouldn't it be much simpler to just have a function, so we can write
> this query like this?
>
> SELECT * FROM text_table
> WHERE text_search('haystack needle haystack', 'needle');
>
> We then explain to people that while the above is nice, it will presume
> that both the function inputs are Text, which isn't any good for complex
> searches, indexing and dictionaries etc.., so then we move to:
>
> SELECT * FROM text_table
> WHERE text_search('haystack needle haystack'::tsvector,
> 'needle'::tsquery);
>
> or perhaps
>
> SELECT * FROM text_table
> WHERE full_text_search('haystack needle haystack', 'needle & hay');
>
> which would automatically do the conversions to tsvector and tsquery for
> us. No more tedious casting, easy to read.

There's a text @@ text operator, so you can write just:

SELECT * FROM tstable where data @@ 'needle';

No need to cast.

Unfortunately, that form can't use a GIN index, I think. But that's
another issue, which I don't think your proposal would fix...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com