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 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.

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 such a 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!