Re: pg_dump ordering in 8.1.3

Yes it is done using a check constraint. I'm reluctant to hardwire
though as I have more complicated examples as well, using e.g. further
columns in the table of characters to subdivide the characters and
perform parse checks dependent on the type of character. I'll have to
look at this some more.

Sue

Tom Lane wrote:
> Sue Fitt <sue(at)inf(dot)ed(dot)ac(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>