Re: [WIP] Support for "ANY/ALL(array) op scalar" (Was: Re: Boolean operators without commutators vs. ALL/ANY)

On Jun16, 2011, at 04:19 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> Comments are extremely welcome, especially ones regarding
>> the overall approach taken in this patch. If people consider
>> that to be acceptable, I'd try to add the missing features
>> and add documentation.
>
> Quite honestly, I don't like this one bit and would rather you not
> pursue the idea. There is no such syntax in the standard, and
> presumably that's not because the SQL committee never thought of it.
> They may have some incompatible idea in mind for the future, who knows?

The SQL standard doesn't have CREATE OPERATOR though, so for them
the asymmetry of the ANY/ALL constructs don't translate to a missing
feature, though. For us, however it does. We might try to doge that
by decreeing that boolean operators better have commutators, but that
doesn't help for non-core-defined operators.

So I'd very much like us to provide some way to get the effect
of "ANY/ALL op scalar" without having to resort to UNNEST and BOOL_AND/OR.
But I'm absolutely not wedded to the syntax "ANY/ALL op scalar".

One other idea I've been kicking around is to generate commutators
automatically for all binary operators that return boolean. If no name
for the commutator is specified (i.e. if CREATE OPERATOR is called without
a value for COMMUTATOR), it's be named "COMMUTATOR <op>", and would thus
be useably only via OPERATOR(...).

Implementation-wise we'd need to add a flag to FmgrInfo which tells
the fmgr to swap the function's arguments, and would need to centralize
the translation of operator OIDs to FmgrInfos.

best regards,
Florian Pflug