Re: sub-selects in CHECK

nconway(at)klamath(dot)dyndns(dot)org (Neil Conway) writes:
> Good point -- but given that SQL99 specifically mentions that this
> functionality should be available (Feature 671, "Subqueries in
> CHECK constraints"), there must be some reasonable behavior
> adopted by another DBMS...

It's effectively equivalent to a database-wide assertion, which is
another SQL feature that we don't support.

> In any case, there are already plenty of ways to create non-sensical
> constraints.

Certainly, but this one isn't really ill-defined, it's just very
difficult to support in any acceptably-efficient manner.

If you want to cheat horribly, ie have the condition checked only when a
single-row constraint would be checked, then you can stick the subselect
inside a function call. I don't think we are really adding any
functionality unless we can do better than that.

regards, tom lane