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

On Jun19, 2011, at 22:10 , Tom Lane wrote:
> Andrew Dunstan  writes:
>> On 06/19/2011 02:56 PM, Robert Haas wrote:
>>> On Sun, Jun 19, 2011 at 9:53 AM, Florian Pflug  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.
> 
>> What do we do when we get text or unknown in place of pattern? How are 
>> we going to know if the pattern is supposed to be the left or right operand?
> 
> Yeah, this would result in
> 	SELECT 'something' ~ 'something';
> failing outright.  I don't think it's a good substitute for biting
> the bullet and choosing distinct operator names.

Yeah, well, the complaint (put forward mainly by Alvaro) that lead to
this approach in the first place was precisely that 'something' ~ 'anything'
*doesn't* give any indication of what constitutes the pattern and
what the text.

So I consider that to be a feature, not a bug.

BTW, arithmetical operators currently show exactly the same behaviour
postgres# select '1' + '1'
ERROR:  operator is not unique: unknown + unknown at character 12

The only argument against that I can see is that it poses
a compatibility problem if "~" remains the pattern matching
operator. I do believe, however, that the chance of unknown ~ unknown
appearing in actual applications is rather small - that'd only
happen if people used postgresql's regexp engine together with
purely external data.

best regards,
Florian Pflug