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_tab WHERE pk = NEW.array_value[_i];
IF NOT FOUND THEN
RAISE EXCEPTION '..........';
END IF;
END LOOP;
RETURN NEW;
END; $$ LANGUAGE plpgsql;

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

Regards
Pavel Stehule