Обсуждение: constraint definition on an array column?

Поиск
Список
Период
Сортировка

constraint definition on an array column?

От
"Gauthier, Dave"
Дата:

Hi:

 

Is it possible to define a constraint that checks all elements of an array type column?

Example...

 

create table foo (asset text, owner_uids text[]);

 

I also have a  plperlu function that checks to see if a given uid is valid (returns a 0/1).  It’s called validate_uid(text),  This function could be modified to handle a list of uids if that was needed.

 

I want something like...

 

constraint validate_all_owner_uids check (select validate_uid(owner_uid) = 1)

 

...but owner_uid is an array, not any one distinct value. 

 

1)     Is there a way to pass in a list for the plperlu to evaluate (return 0 if any uid is invalid)?

2)     Is there some sort of syntactical support for defining constraints on array columns?

 

Thanks in Advance!  

Re: constraint definition on an array column?

От
Merlin Moncure
Дата:
On Fri, Sep 11, 2009 at 10:34 AM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:
> Hi:
>
> Is it possible to define a constraint that checks all elements of an array
> type column?
>
> Example...
>
>
>
> create table foo (asset text, owner_uids text[]);
>
>
>
> I also have a  plperlu function that checks to see if a given uid is valid
> (returns a 0/1).  It’s called validate_uid(text),  This function could be
> modified to handle a list of uids if that was needed.
>
>
>
> I want something like...
>
>
>
> constraint validate_all_owner_uids check (select validate_uid(owner_uid) =
> 1)
>
>
>
> ...but owner_uid is an array, not any one distinct value.
>
>
>
> 1)     Is there a way to pass in a list for the plperlu to evaluate (return
> 0 if any uid is invalid)?
>
> 2)     Is there some sort of syntactical support for defining constraints on
> array columns?

there doesn't have to be.  since a constraint can invoke a function
(preferably immutable), you can do anything you want regarding
validating the array.

merlin