From: | Renaud Tthonnart <thonnart(at)amwdb(dot)u-strasbg(dot)fr> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | array containing references |
Date: | 2001-02-28 12:51:13 |
Message-ID: | 3A9CF441.4E6B584B@amwdb.u-strasbg.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have a table with an attribute that is an array that references an
other table.
How can I search all the row referenced by my array?
Here's a little example that will explain better what I want to do:
CREATE TABLE xxx
(
id int,
nom varchar(10),
ref int[],
PRIMARY KEY(id)
);
INSERT INTO xxx VALUES( 1,'aaa','{10,20,30,50}');
INSERT INTO xxx VALUES( 2,'bbb','{10,30,40}');
INSERT INTO xxx VALUES( 3,'ccc','{20,40}');
CREATE TABLE yyy
(
id int,
nom varchar(10),
PRIMARY KEY(id)
);
INSERT INTO yyy VALUES(10,'y1');
INSERT INTO yyy VALUES(20,'y2');
INSERT INTO yyy VALUES(30,'y3');
INSERT INTO yyy VALUES(40,'y4');
INSERT INTO yyy VALUES(50,'y5');
SELECT yyy.id
FROM xxx,yyy
WHERE yyy.id = xxx.ref;
==>Of course, that qwery doesn't work.
But how can I do that without doing this:
SELECT yyy.id
FROM xxx,yyy
WHERE yyy.id = xxx.ref[1]
UNION
SELECT yyy.id
FROM xxx,yyy
WHERE yyy.id = xxx.ref[2]
UNION
SELECT ...
...
UNION
SELECT yyy.id
FROM xxx,yyy
WHERE yyy.id = xxx.ref[...];
Thanks in advance,
Renaud THONNART
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2001-02-28 12:52:10 | Re: pg_dumpall -> fails |
Previous Message | Juan Ramón Cortabitarte | 2001-02-28 12:28:30 | store procedure in pl/pgsql |