On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <f...@phlo.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
