Adding a distinct "pattern" type to resolve the "~" commutator stalemate

Hi

It looks like we've failed to reach an agreement on how to
proceed on the issue with missing commutators for the various
text matching operators ("~", "~~", and their case-insensitive
variants). We do seem to have agreed, however, that adding
commutators for the non-deprecated operators which lack them
is generally a Good Idea.

Amidst the discussion, Alvaro suggested that we resolve the issue
by adding a distinct type for patterns as opposed to text. That'd
allow us to make "~" it's own commutator by defining both
text ~ pattern
and
pattern ~ text.

We'd of course need to keep the operator
text ~ text
and make it behave like
text ~ pattern.
Thus, if someone wrote
'a_pattern' ~ 'some_text'
(i.e. forgot to cast 'a_pattern' to type "pattern"), he wouldn't
get an error but instead unintended behaviour. If we want to avoid
that too, we'd have to name the new operators something other than
"~".

There's also the question of how we deal with "~~" (the operator
behind LIKE). We could either re-use the type "pattern" for that,
meaning that values of type "pattern" would represent any kind of
text pattern, not necessarily a regular expression. Alternatively,
we could represent LIKE pattern by a type distinct from "pattern",
say "likepattern". Finally, we could handle LIKE like we handle
SIMILAR TO, i.e. define a function that transforms a LIKE pattern
into a regular expression, and deprecate the "~~" operator and friends.

The last option looks appealing from a code complexity point of view,
but might severely harm performance of LIKE and ILIKE comparisons.

Comments? Opinions?

best regards,
Florian Pflug

Someone