Hy all!

I posted this mail on the general and sql mailing-list, but I got no
aswer, so I try to post it here;
if the question is bad-explained, please ask me more details...

I want to make a many-to-many relation on my db, and I would like to use

an array-field of the external keys...
This is what I whoul like to create:

CREATE TABLE Groups(
    IDGroup             SERIAL,  -- Primary key
    ...
);

CREATE TABLE Customers(
    IDGroups             INT4[],  -- Multiple foreign keys
    ...
);

SELECT Customers.* FROM Customers WHERE IDSearchedGroup IN
Customers.IDGroups;

IDSearchedGruop is obviously a parameter.

Well, this query doesn't work because in the SELECT ... the operator
"IN" can work only on subselect, not on array... how can I check if an
element belongs to an array or not? Performance are not critical,
because I have few groups for each Customer...

Yes, I know that the common way to make many-to-many relations is adding

a support table... but I don't like the conventional solution to this
problem... any ideas???


Paolo



Mon, 23 Aug 1999 15:37:37 +0200
Alke <alke@iol.it>
Array-fields and many-to-many relations
Alke <alke@iol.it> writes:
> Well, this query doesn't work because in the SELECT ... the operator
> "IN" can work only on subselect, not on array... how can I check if an
> element belongs to an array or not?

contrib/array has a slightly ugly solution to your problem.

			regards, tom lane

Mon, 23 Aug 1999 10:23:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Array-fields and many-to-many relations 