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" :-)