From: no.spam@address.com
Subject: Re: Denormalizing during select
Message-ID: <m3pl5vsrmhi1rbn865huhqn29cjfmoal79@4ax.com>
References: <200302241433.49502.josh@agliodbs.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 64
Date: Tue, 25 Feb 2003 03:37:41 GMT
Organization: Cox Communications
To: pgsql-sql@postgresql.org

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.