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

On Sun, Jun 19, 2011 at 9:53 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> 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.

That's kind of a neat idea. There might be an efficiency benefit to
having a regex type that is precompiled by the input function.

> 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.

I don't believe it would be a very good idea to try to shoehorn
multiple kinds of patterns into a single pattern type.

I do think this may be the long route to solving this problem, though.
Is it really this hard to agree on a commutator name? I mean, I'm
not in love with anything that's been suggested so far, but I could
live with any of them. An unintuitive operator name for
matches-with-the-arguments-reversed is not going to be the worst wart
we have, by a long shot...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company