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 по дате отправления:

Предыдущее
От: "Richard Huxton"
Дата:
Сообщение: Re: Object
Следующее
От: Renaud Tthonnart
Дата:
Сообщение: Re: Object