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

On 06/19/2011 05:02 PM, Florian Pflug wrote:
> On Jun19, 2011, at 22:10 , Tom Lane wrote:
>> Andrew Dunstan<andrew@dunslane.net>  writes:
>>> On 06/19/2011 02:56 PM, Robert Haas wrote:
>>>> On Sun, Jun 19, 2011 at 9:53 AM, Florian Pflug<fgp@phlo.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.
>>> 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.
>
>

People can store regular expressions in text fields now, and do, let me 
assure you. So the chances you'll encounter text ~ unknown or unknown ~ 
text  or text ~ text are 100%

cheers

andrew