Re: Subqueries in Check() -- Still Intentionally Omitted? - Mailing list pgsql-general

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