Skip site navigation (1) Skip section navigation (2)

Re: Array values and foreign keys

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 (view raw or flat)
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

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



In response to

pgsql-general by date

Next:From: Thomas HallgrenDate: 2004-10-27 20:07:48
Subject: Re: Reasoning behind process instead of thread based
Previous:From: Steven KlassenDate: 2004-10-27 19:32:48
Subject: Re: QMail

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group