On Tue, 2008-09-02 at 15:30 -0700, Richard Broersma wrote: > I am curious if the motivation is still valid for intentionally > omitting check sub-queries. (what was the motivation to begin with?) > > Since we can effectively work around this limitation by doing the same > thing with a function in a CHECK constraint, why would we want to Wow, I assumed you needed an immutable function for that to work. Then I tried it: => create table foo(i int check (random() > 0.5)); My question is not why don't we allow subqueries in CHECK, my question is why do we allow stable/volatile functions? As I understand it, CHECK is meant for simple declarative tuple constraints. It's not designed for sophisticated inter-relation constraints -- or even intra-relation constraints, for that matter. Consider: CREATE TABLE foo( ... CHECK ((SELECT COUNT(*) FROM foo) < 10) ); We'd need some big locks for that to actually be a true declaration. All of this can be solved with triggered procedures, where you can define the locks as needed. Regards, Jeff Davis
