On Wed, Apr 25, 2012 at 11:33 AM, <atrigent@ccs.neu.edu> wrote:
> Seeing as Postgres does not allow sub-queries in CHECK constraints yet, it
> doesn't make any sense to me for it to allow function calls, since functions
> can perform queries.
This is why functions must be marked as one of VOLATILE, STABLE, or
IMMUTABLE. Only IMMUTABLE functions can be used in CHECK constraints.
It's a feature that expressions including subqueries are automatically
detected as not being immutable and automatically barred.
Functions do not have this feature and must be manually marked by the
user with the correct state. This is a useful escape hatch in cases
where an expression can not be proven to be immutable but the user
knows that due to the design of his or her application it is in fact
immutable -- for instance queries that query from tables that the user
is certain will never be modified.
The database cannot detect every possible erroneous usage, at least
not without being less useful. It's a balancing act of providing the
user with as many safety nets as possible without imposing too many
restrictions. Too many safety nets and you can't do some things, too
few and you spend too much time checking for or debugging problems.
--
greg