From: Herouth Maoz <herouth@oumail.openu.ac.il>
To: frank <frank@x9media.com>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] set type and in clause
Date: Wed, 20 Jan 1999 12:11:27 +0200
Message-ID: <l03110703b2cb562a749a@[147.233.159.109]> (raw)
In-Reply-To: <36A50668.4C0CD67B@x9media.com>
At 0:25 +0200 on 20/1/99, frank wrote:
>
> i suppose it´s been asked before (i´ve seen the question, but not the
> answer, in the mailing list archives):
>
> how do you create a set type, i.e. an attribute which is, say ´set of
> char´; and then you´d want to run some query like
>
> select . . . from . . . where xxx in yyy
>
> is such a data type supported by postgresql? do other dbms´s support it?
According to the relational model, a set is a separate table which is
connected to your main table by a foreign key. That is, you hold two tables:
Main table:
P.Key 1:1 Data
----- --------
10 Microsoft
20 Adobe
30 FSF
...
Secondary table:
F.Key 1:N Data
----- --------
10 Office
10 Access
20 Photoshop
30 gcc
10 Windows
20 Acrobat
30 bison
30 flex
In this manner, you have the strings 'Office', 'Access' and 'Windows'
associated with the record for 'Microsoft', the strings 'Photoshop' and
'Acrobat' associated with the record for 'Adobe', and the strings 'gcc',
'bison' and 'flex' associated with the record for 'FSF'.
Suppose the names of the fields are 'p' for the primary key, 'company' form
the 1:1 data, 'f' for the foreign key and 'product' for the 1:N data, you
can write a query like this:
SELECT company, product
FROM main, secondary
WHERE p=f.
The result would be:
Microsoft Office
Microsoft Access
Microsoft Windows
Adobe Photoshop
Adobe Acrobat
FSF gcc
FSF bison
FSF flex
The point I am trying to get across is that this, in fact, represents a set
associated with each record. You get the data you wanted. The problem is
that in the result you get repetitions, because that's how the relational
model works. If you want to isolate the set, you can use a frontend which
drops the repetitions and creates the following report from them:
Microsoft Office
Access
Windows
Adobe Photoshop
Acrobat
FSF gcc
bison
flex
Nevertheless, the data represented by this report is the same data that you
got from the original query. It merely shows association with the company.
So, if you wanted to test whether something belongs or does not belong to a
set that is associated with one of your software vendors, you can do a
subquery on the table. For example, to get the companies which have in
their set of products a product called 'Acrobat', you write:
SELECT company
FROM main
WHERE 'Acrobat' IN (
SELECT product
FROM secondary
WHERE p=f );
I'd like to note that having the set of related products included in the
main table is, well, not relational.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: herouth@oumail.openu.ac.il, frank@x9media.com
Subject: Re: [SQL] set type and in clause
In-Reply-To: <l03110703b2cb562a749a@[147.233.159.109]>
References: <36A50668.4C0CD67B@x9media.com> <l03110703b2cb562a749a@[147.233.159.109]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox