Re: Boolean operators without commutators vs. ALL/ANY

On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:
> So I the end, I had to wrap the sub-query in a SQL-language
> function and use that in the check constraint. While this
> solved my immediate problem, the necessity of doing that
> highlights a few problems
>
> (A) "~" is an extremely bad name for the regexp-matching
> operators, since it's visual form is symmetric but it's
> behaviour isn't. This doesn't only make its usage very
> error-prone, it also makes it very hard to come up with
> sensible name for an commutator of "~". I suggest that we
> add "=~" as an alias for "~", "~=" as an commutator
> for "=~", and deprecate "~". The same holds for "~~".

Does any other database or programming language implement it this way?

> (B) There should be a way to use ANY()/ALL() with the
> array elements becoming the left arguments of the operator.
> Ideally, we'd support "ANY(<array>) <operator> <value>",
> but if that's not possible grammar-wise, I suggest we extend
> the OPERATOR() syntax to allow
>  <value> OPERATOR(COMMUTATOR <operator>) ANY(<array>).
> OPERATOR(COMMUTATOR <operator>) would use the COMMUTATOR
> of the specified operator if one exists, and otherwise
> use the original operator with the arguments swapped.

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. I'm
not crazy about the proposed syntax, though; it seems cumbersome, and
it's really only needed for SOME/ALL/ANY, not in general operator
expressions. Since ANY is a reserved keyword, I believe we could
allow something like "expr op ANY BACKWARD ( ... )" -- or some other
keyword in lieu of BACKWARD if you prefer.

Hath the spec anything to say about this?

> (C) Why do we forbid sub-queries in CHECK constraints?
> I do realize that any non-IMMUTABLE CHECK constraint is
> a foot-gun, but since we already allow STABLE and even
> VOLATILE functions to be used inside CHECK constraint,
> forbidding sub-queries seems a bit pointless...

Dunno. Maybe it's just an implementation restriction?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company