Re: Simplifying Text Search

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> [ replace LIKE with this: ]

> 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; $$;

Cute trick, but as-is this will not make anything go any faster, because
it doesn't expose any opportunities for indexing the @@ operation.
I think what you'd really need is something like

$$ SELECT to_tsvector('english', $1) @@ likepattern_to_tsquery($2) AND $1 #~~# $2; $$;

which will win if there is an expression index on to_tsvector('english',
<textcolumn>). (You can substitute your preferred configuration of
course, but you don't get to rely on default_text_search_config, because
that would render the expression non-immutable and thus non-indexable.)

This points up the same old notational problem that there is no good
place in the operator notation to mention which text search
configuration you want to use. Simon's suggestion of a three-parameter
function at least addresses that issue.

regards, tom lane