Sue Fitt <sue@inf.ed.ac.uk> writes:
> I am working on a database of gradually increasing complexity, with
> several tables connected by foreign keys. Validity of some fields is
> also checked by triggers etc, some in plpgsql and some in plperl. I now
> have the situation where I have a table X with a field which uses a
> domain 'word'. The characters possible in this domain (a-z plus a number
> of others) are listed in table Y. I then have a plperl function
> check_word which uses a query to get the characters from table Y, and
> compiles these characters into a variable which is used to check the
> contents of the domain.
The function is called by a check constraint I take it? We really don't
support check constraints that look at anything except the current row
of the current table. Anything else introduces dependencies that the
system does not know about and can't promise to honor, as you're finding
out :-(. An example of the kind of problem you'll face is that changes
to the content of table Y will not result in rechecking the constraints
on other tables, even though changing Y might've caused those
constraints to fail.
I think you'd be best off hard-wiring the list of allowed characters
into a check constraint associated with the domain 'word', eg
create domain word as text
check (value ~ '^[a-zA-Z0-9_]+$');
The separate table listing the allowed characters might be good for some
things, but not this.
regards, tom lane
