Re: Boolean operators without commutators vs. ALL/ANY - Mailing list pgsql-hackers

On Jun14, 2011, at 14:29 , Robert Haas wrote:
> On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug <fgp@phlo.org> wrote:
>> On Jun13, 2011, at 05:44 , Tom Lane wrote:
>>> Robert Haas <robertmhaas@gmail.com> writes:
>>>> On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
>>>>> (B) There should be a way to use ANY()/ALL() with the
>>>>> array elements becoming the left arguments of the operator.
>>>
>>>> It seems to me that if we provided some way of handling this, your
>>>> first proposal would be moot; and I have to say I like the idea of
>>>> allowing this a lot more than tinkering with the operator names.
>>>
>>> There are syntactic reasons not to do that.  It'd be a lot easier just
>>> to provide a commutator operator for ~.
>>
>> My suggestion would be the add a commutator for "~" as a short-term
>> solution (preferably in 9.1).
>
> I don't think we want to bump catversion again before release if we
> can avoid it.  And I don't see this as being a terribly urgent problem
> - it's not like this is a new regression, and I can't remember hearing
> any complaints about it prior to two days ago.

Hm, OK, that makes sense...

>> Since "~" doesn't inspire any obvious names for a possible commutator,
>> I suggest adding "=~" and "~=".
>>
>> Is there any support for that proposal?
>
> I'm OK with adding a commutator but I guess I don't see the point of
> adding a synonym for ~ along the way.  The existing use of ~ is
> consistent with, for example, awk, so it's not like we've dreamed up
> something utterly crazy that we now need to fix.  I'd suggest we just
> come up with some arbitrary variant, like ~~ or <~ or #~ or
> !#!%@~bikeshed++!.


That, however, I'm not at all happy with. Quite frankly, operator
naming is already a bit of a mess, and readability of queries
suffers as a result. The geometric types are especially vile
offenders in this regard, but the various array-related operators
aren't poster children either.

I think we should try to work towards more mnemonic operator
naming, not add to the mess by defining commutator pairs whose
names bear no visual resemblance whatsoever to one each other.

I'm not wedded to "=~", it's just the only name I could come
up which (a) has a natural commutator (b) gives visual indication of which argument constitutes the     text and which
thepattern (c) there is precedent for. 

BTW, there's actually precedent for a commutator of "~", namely
"@". Some of the geometric types (polygon, box, circle, point,
path) use "~" as a commutator for "@" (which stands for "contains").
But IMHO that mainly proves that the geometric types are vile
offenders when it comes to readability...

The pair ("@", "~" ) is also the only pair of commutators whose
names are totally unrelated to each other. Given a suitable
definition of a reverse() function for text [1], the following query

select       o1.oprleft::regtype || ' ' || o1.oprname || ' ' || o1.oprright::regtype as opr,       o2.oprleft::regtype
||' ' || o2.oprname || ' ' || o2.oprright::regtype as com,       o1.oprcode as opr_code,       o2.oprcode as com_code 
from pg_operator o1
join pg_operator o2 on o1.oprcom = o2.oid or o2.oprcom = o1.oid
where       o1.oid < o2.oid and       o1.oprname <> reverse(translate(o2.oprname, '<>', '><')) and       o1.oprname <>
translate(o2.oprname,'<>', '><'); 

produces
       opr        |        com        |      opr_code       |     com_code
-------------------+-------------------+---------------------+-------------------polygon @ polygon | polygon ~ polygon
|poly_contained      | poly_containbox @ box         | box ~ box         | box_contained       | box_containcircle @
circle  | circle ~ circle   | circle_contained    | circle_containpoint @ path      | path ~ point      | on_ppath
     | path_contain_ptpoint @ polygon   | polygon ~ point   | pt_contained_poly   | poly_contain_ptpoint @ circle    |
circle~ point    | pt_contained_circle | circle_contain_pt 
(6 rows)

best regards,
Florian Pflug

[1] I used
create or replace function reverse(text) returns text as $$
select string_agg(substring($1, i, 1), '') from generate_series(length($1), 1, -1) i
$$ language sql immutable;