|
Hi Everyone: v8.3.4 on Linux I need to revamp the way
I've done check constraints on a table. This is an example of the sort of thing
I've done... create table foo ( col1 text, col2 text, constraint c1_constr check
(col1 in ('yes','no')), constraint c2_constr check
(validate_c2(col2) = 'OK') ); ...with validate_c2 having
been declared... create or replace function
validate_c2 (text) returns text as $$ declare c2_csv alias for $1; c2_lst text[]; x int; begin c2_lst =
string_to_array(c2_csv,','); for x in
array_lower(c2_lst,1).. array_upper(c2_lst,1) loop if c2_lst[x] not in
('red','orange','yellow','green','blue','violet') then return 'NO';
end if; end loop; return 'OK'; end; $$ language plpgsql ; As you can see, the
constraint on col1 is a simple check that the value is in a list. But the
constraint on col2 needs to check that each element in a csv is in a list. I'd like to have one table
that contains all the valid values for both column constraints and perhaps use
a more sophisticated approach to this than the check constraints and plpgsql
you see above. One of the reasons for this
is better access to the valid values in error messages I return to the users
upon their hitting a constraint violation. These error messages need to tell
them what the valid values are. The desire for a single table renders moot the
need to identify the correct constraint value table to read in this regard. Another reason for the
table(s) containing the valid values is ease of modification of the lists (no
need to alter the hard coded constraint or change the stored proc). All ideas will be rewarded
with a "Thank You", but the winner also gets a "Very Much"
:-) |
