Hi I've recently wanted to define a check constraint on an array column that verifies that all array entries match some regular expression. Unfortunately, t
The most natural way of expressing such a check would be CHECK ('<regexp>' ~ ANY(field)), but that doesn't work, because "~" expects the *value* to be the left argument and the *pattern* to be the right. The next try was CHECK (ANY(field) ~ '<regexp>'), but that doesn't even parse. Ok, so then use UNNEST() and BOOL_AND() I figured, and wrote CHECK ((SELECT BOOL_AND(v ~ '<regexp>') FROM UNNEST(field) v)). But that of course lead to nothing but ERROR: cannot use subquery in check constraint 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 "~~". We might want to do this starting with 9.1. (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. (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... best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers