Thread: Need a better way to do my constraints
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" :-)
On 20 Jul 2010, at 16:19, Gauthier, Dave wrote:
> 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
needsto 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
sophisticatedapproach to this than the check constraints and plpgsql you see above.
I think your best bet would be to define an ENUM type with those values and store the CSV data as an array of that
type.I think it would automatically reject any invalid values that way.
A foreign key constraint would be nicer to have, but I don't see any straightforward way to unnest your CSV data in
sucha way that you could apply one to it.
Alban Hertroys
--
Screwing up is an excellent way to attach something to the ceiling.
!DSPAM:737,4c45c239286211821273955!