Re: Foreign keys on array elements

> Hi,
> 
> Can you put a foreign key constraint on an array column that says that 
> each element of the array must match a primary key?
> 
> If not, is this a TODO perhaps?
> 
> Chris
> 
Hello, 

Using array values for foreign key is very special. I not sure, so all 
people need it. More interesting is CHECK on array. But you can write 
simply trigger.

CREATE OR REPLACE FUNCTION check_() RETURNS TRIGGER AS $$
DECLARE _v integer;
BEGIN FOR _i IN array_lower(NEW.array_value,1) ..            array_upper(NEW.array_value,1)  LOOP   PERFORM 1 FROM
some_tabWHERE pk = NEW.array_value[_i];   IF NOT FOUND THEN     RAISE EXCEPTION '..........';   END IF; END LOOP;
RETURNNEW;
 
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER foo BEFORE INSERT OR UPDATE ON ... FOR EACH ROW EXECUTE PROCEDURE check_();

Regards
Pavel Stehule