Re: Indexing of LIKE queries is broken in current sources

> After a little bit of thought I think I see a way out that avoids
> opening the function-selectivity can of worms.  Let's translate LIKE
> to an operator same as we always did (and add an operator for ILIKE).
> The forms with an ESCAPE clause can be translated to the same operators
> but with a righthand argument that is a call of a new function
>                 escape_for_like(pattern, escape)
> escape_for_like would interpret the pattern with the given escape
> character and translate it into a pattern that uses the standard escape
> character, ie backslash.  After constant folding, this looks just like
> the old style of LIKE call and none of the optimizer code has to change
> at all, except to add a case for ILIKE which will be a trivial addition.
> LIKE itself gets simpler too, since the pattern matcher needn't cope
> with nonstandard escape characters.  Seems like a good subdivision of
> labor within LIKE.
> Thoughts?  I'm willing to take responsibility for making this happen
> if you agree it's a good solution.

Hmm. It's a great solution, though I'm disappointed that the seemingly
more direct function-call strategy turned out to be a dead end (at least
for now).

It seems that "~~*" should be the operator for ILIKE.
                   - Thomas

btw, I notice that psql has trouble with \dd when you try to show any
operator which contains an asterisk.

\dd *

results in an error.

\dd ~*

shows every function, type, and operator.

I've tried surrounding it with single- and double-quotes, but that
didn't seem to work around it.