Re: Array values and foreign keys

Le mer 27/10/2004 à 11:59, Pierre-Frédéric Caillaud a écrit :
>
>
>     You can't express it directly with a CHECK constraint but you can do this
> :
>
>     - add CHECK( test_array( yourcolumn )) in your table definition
>     - create function test_array which takes an array and looks if all its
> elements are in your table T2, I do something like comparing the length of
> the array to SELECT count(1) FROM T2 WHERE key IN array
>     You can do it other ways but you'll have to use a function.
>
>

Fine. I got it right after fiddling a little bit. The function is
something like:

CREATE FUNCTION test_array (smallint[]) RETURNS bool AS '
   select case when count(1) = array_upper($1,1) then true
               else false
          end from t2 where cle = any($1);
' LANGUAGE SQL;

It compares the length of the array to the number of elements actually
found in the reference table.

--

=======================================
Daniel Savard

=======================================