> 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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
