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/