array containing references
От | Renaud Tthonnart |
---|---|
Тема | array containing references |
Дата | |
Msg-id | 3A9CF441.4E6B584B@amwdb.u-strasbg.fr обсуждение исходный текст |
Список | 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
В списке pgsql-general по дате отправления: