array containing references

Started by Renaud Tthonnartabout 25 years ago3 messagesgeneral
Jump to latest
#1Renaud Tthonnart
thonnart@amwdb.u-strasbg.fr

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

#2Renaud Tthonnart
thonnart@amwdb.u-strasbg.fr
In reply to: Renaud Tthonnart (#1)
Re: array containing references

Renaud Tthonnart wrote:

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}');

Or more simply:
How can I get ihe id of the tuple for which the array contains 30 for
example

#3Renaud Tthonnart
thonnart@amwdb.u-strasbg.fr
In reply to: Renaud Tthonnart (#2)
Re: array containing references

Michael Ansley wrote:

Use the array stuff in the contrib directory. There are functions
there to do this.

Cheers...

MikeA

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}');

Or more simply:
How can I get ihe id of the tuple for which the array contains 30 for
example

Thank you for your help but could you be more clear?
Where exactly do I search to find array stuff.
I went on the website of postgresql in directory contrib of download but
I didn't find it.

Cheers, Renaud THONNART