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