Re: Immutable functions, Exceptions and the Query Optimizer

Cochise Ruhulessin wrote:
> Regarding your question about what the CHECK constraint should achieve, I=
 had abstracted by use case
> into Books/Book Types, which may have caused some vagueness. The actual u=
se case are the following
> tables.

[...]
=20
> CREATE TABLE persons(
>     person_id int8 NOT NULL PRIMARY KEY,
>     place_of_birth_id int8
>         REFERENCES features (feature_id)
>         ON UPDATE CASCADE
>         ON DELETE RESTRICT
>         INITIALLY IMMEDIATE,
>     CHECK (features_get_feature_code(place_of_birth_id) ~ 'PC.*|ADM.*|PP.=
*')
> );
>=20
>=20
> The CHECK constraint should achieve that "persons.place_of_birth_id" is a=
lways a country, or a
> (first_order) adminitrative division, or a city (which is defined by "fea=
tures.gtype_id").
>=20
> Though this could be done by creating a multi-column foreign key on
> ("features.feature_id","features.gtype_id"), this would violate the princ=
iples of normalization.

True; but if you don't mind that, it would be a nice solution
since you already have a unique index on features(feature_id, feature_code)=
.

> Of course this could also be achieved by a TRIGGER, but that seems a litt=
le redundant to me.

I think a trigger is the best solution here.
Why is it more redundant than a CHECK constraint?
Both will do about the same thing, with the advantage
that the trigger solution would be correct and won't
give you any trouble at dump/reload time.

Yours,
Laurenz Albe