From: no.spam@address.com
To: pgsql-sql@postgresql.org
Subject: Re: Denormalizing during select
Date: Tue, 25 Feb 2003 03:37:41 GMT
Message-ID: <m3pl5vsrmhi1rbn865huhqn29cjfmoal79@4ax.com> (raw)
References: <200302241433.49502.josh@agliodbs.com>
On Tue, 25 Feb 2003 01:22:42 +0000 (UTC), josh@agliodbs.com (Josh
Berkus) wrote:
>Can you post the start of this thread? I think there's a more
>efficient way using custom aggregates, but somhow I never
>received the original question.
I'm hoping there is... somehow, using a function to loop over every
row seems a bit inefficient? Here's my original question...
I have two tables (A and B) in a one-to-many relationship. When
retrieving data from one table (A), I need to denormalize the tables
during a select so that values of a column in the many table (B)
appear as a list in one field of the output. E.g.:
create table one_table (
name varchar(20),
age integer,
--
constraint one_table_pk
primary key (name)
);
create table many_table (
name varchar(20),
property varchar(15),
--
constraint many_table_pk
primary key (name, property),
constraint many_table_fk
foreign key (name) references one_table (name)
);
So one_table could contain:
name age
---- ---
Mickey Mouse 50
Donald Duck 60
Goofy 45
and many_table could contain:
name property
---- --------
Mickey Mouse Small
Mickey Mouse Black
Mickey Mouse Squeaks
Donald Duck Quacks
The query I'm trying to write would have as output something like:
name age properties
---- --- ----------
Mickey Mouse 50 Small, Black, Squeaks
Donald Duck 60 Quacks
Goofy 45 <null>
Could somebody please help point me in the right direction? I imagine
this is a common need...
Thanks,
...Edmund.
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: no.spam@address.com
Subject: Re: Denormalizing during select
In-Reply-To: <m3pl5vsrmhi1rbn865huhqn29cjfmoal79@4ax.com>
* 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