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!
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
