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

On Jun19, 2011, at 20:56 , Robert Haas wrote:
> 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.

Hm, yeah, that though crossed my mind too. A distinct type is only
a first step in that direction though - we'd also need a way to
attach a parsed representation of a value to a varlena. If you have
an idea how to accomplish that, by all means, out with it! ;-)
The XML would also benefit greatly...

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

That depends on whether we expect to eventually make LIKE
use the regex matching machinery. If we do, then it's not really
shoehorning. If we don't, then yeah, using a single type seems
unwise, especially in the light of your idea of keeping a parsed
representation of regexp's around.

> I do think this may be the long route to solving this problem, though.

Yeah - but maybe also the one with the largest benefit in the long run.
We're also just at the beginning of a release cycle, so I think we
have time enough to figure this out...

> Is it really this hard to agree on a commutator name?

So far, every suggestion has been met with fierce opposition, so, um,
yeah it is I'd say...

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

Maybe not. But then, if the name is unintuitive enough to impair
readability anyway, then people might just as well define a custom
operator in their database. Since we're capable of inlining SQL
functions, there won't even be a difference in performance. The only
real benefit of having this is core is that you don't have to
go search the catalog to find the meaning of such an operator if
you encounter it in an SQL statement.

best regards,
Florian Pflug