Array-fields and many-to-many relations

From: Alke <alke(at)iol(dot)it>
To: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Array-fields and many-to-many relations
Date: 1999-08-23 13:37:37
Message-ID: 37C14EA1.9F6EF833@iol.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Browse pgsql-hackers by date

  From Date Subject
Next Message Alke 1999-08-23 13:40:58 OID and PL/pgSQL trigger :-(
Previous Message José Soares 1999-08-23 13:03:29 ERROR: pull_var_clause: Cannot handle node type 108