From: | Daniel Savard <dsavard(at)cids(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Array values and foreign keys |
Date: | 2004-10-27 19:44:57 |
Message-ID: | 1098906301.20209.6.camel@dirac.cids.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
=======================================
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Hallgren | 2004-10-27 20:07:48 | Re: Reasoning behind process instead of thread based |
Previous Message | Steven Klassen | 2004-10-27 19:32:48 | Re: QMail |