Re: Need a better way to do my constraints

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!