On 6/5/2017 5:32 PM, David G. Johnston wrote:
On Mon, Jun 5, 2017 at 5:15 PM, Ken Tanzer <ken.tan...@gmail.com
<mailto:ken.tan...@gmail.com>>wrote:
From the docs:
https://www.postgresql.org/docs/9.6/static/sql-createtable.html
<https://www.postgresql.org/docs/9.6/static/sql-createtable.html>
"Currently, CHECK expressions cannot contain subqueries nor
refer to variables other than columns of the current row. The
system column tableoid may be referenced, but not any other
system column.
I wonder if that should say "should not," or be followed by
something like this:
Make it say "must not" and I'd agree to change the word "cannot" and
leave the rest. Adding a note regarding functions seems appropriate.
Aside from being a bit more verbose there is nothing useful that
writing this as "CHECK function()" provides that you don't also get by
writing "CREATE TRIGGER". In a green field we'd probably lock down
CHECK a bit more but there is too much code that is technically wrong
but correctly functioning that we don't want to break. IOW, we cannot
mandate that the supplied function be immutable even though we
should. And we don't even enforce immutable execution if a function
is defined that way.
indeed, any sort of constraint that invokes a function call which looks
at other tables could later be invalidated if those other tables change,
and postgres would be none the smarter. the same goes for trigger
based checks.
--
john r pierce, recycling bits in santa cruz