From: | Andreas Seltenreich <andreas+pg(at)gate450(dot)dyndns(dot)org> |
---|---|
To: | Ketema Harris <ketema(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Foreign Keys With Arrays |
Date: | 2006-02-07 07:58:03 |
Message-ID: | 87mzh38ut0.fsf@gate450.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Ketema Harris writes:
> If I have a column that is of type int4[] (array of integers) is it possible
> to require that each integer within that array is a foreign key?
I'm afraid you'll have to define a custom check-constraint to do that.
E.g.
--8<---------------cut here---------------start------------->8---
scratch=# create table foo (a int);
CREATE TABLE
scratch=# insert into foo values (1);
INSERT 6856486 1
scratch=# insert into foo values (2);
INSERT 6856487 1
scratch=# create table bar (a int[]);
CREATE TABLE
scratch=# create function valid_array(a int4[]) returns boolean as $$
scratch$# begin
scratch$# for i in array_lower(a, 1)..array_upper(a,1) loop
scratch$# if not exists (select 1 from foo where foo.a = a[i]) then
scratch$# return 'f';
scratch$# end if;
scratch$# end loop;
scratch$# return 't';
scratch$# end $$ language plpgsql;
CREATE FUNCTION
scratch=# alter table bar add constraint foo check(valid_array(a));
ALTER TABLE
scratch=# insert into bar values ('{1,2}');
INSERT 6856494 1
scratch=# insert into bar values ('{1,3}');
ERROR: new row for relation "bar" violates check constraint "foo"
scratch=#
--8<---------------cut here---------------end--------------->8---
regards,
Andreas
--
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Seltenreich | 2006-02-07 08:09:40 | Re: Foreign Keys With Arrays |
Previous Message | Ketema Harris | 2006-02-06 20:34:28 | Foreign Keys With Arrays |