Re: Simplifying Text Search

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
RETURNS NULL ON NULL INPUT IMMUTABLE
LANGUAGE SQL
AS $$ SELECT trim(replace($1, '%', ' & '), '& ')::tsquery; $$;

UPDATE pg_operator SET oprname = '#~~#' WHERE oprcode = 'textlike'::regproc;

CREATE FUNCTION textlike_ts(text, text) RETURNS boolean
RETURNS NULL ON NULL INPUT IMMUTABLE
LANGUAGE SQL
AS $$ SELECT $1 @@ likepattern_to_tsquery($2) AND $1 #~~# $2; $$;

CREATE OPERATOR ~~ (
PROCEDURE = textlike_ts,
LEFTARG = text,
RIGHTARG = text
);

Maybe something like this could be useful for people who cannot readily change
their application code. (Of course it is not meant to solve the issue of how
to make the text-search functionality itself easier to access.)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/