Flattening a subquery - Mailing list pgsql-general

From Ryan Campbell
Subject Flattening a subquery
Date
Msg-id 20010419020730.21568.qmail@web4503.mail.yahoo.com
Whole thread Raw
In response to Re: Inheritance question  ("Oliver Elphick" <olly@lfix.co.uk>)
Responses Re: Flattening a subquery
List pgsql-general
I have two entities:

create table families (
   family_id   number primary key,
   family_name varchar(100) not null
);

create table members (
   member_id   number primary key,
   family_id   number references family_id,
   first_name  varchar(100)
);

I'd like to display a list of families with a list of
members who are in each family.  Something like this:

Campbell (Ryan, Wendy, Henry, Thomas)

What are the possible solutions to this?  It would be
nice to be able to do something like this:

select family_name, '(' || join(select member_name
where family_id = f.family_id, ', ') || ')' as
family_members
from family f

The 'join' is meant in the sense of the PERL, TCL or
Javascript join function: taking a list of items and
appending them together into a string with a given
delimiter.  Perhaps 'flatten' would be less confusing.

Is there already something like this out there?  Would
it be possible to write something like it?  Or is
there an existing method that allows me to achieve the
same result (without having to result to doing it by
hand in a stored proc or in my program)?  I like this
solution because it's generic, but perhaps I'm taking
the wrong approach.

Thanks,
Ryan

__________________________________________________
Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices
http://auctions.yahoo.com/

pgsql-general by date:

Previous
From: "Brett W. McCoy"
Date:
Subject: Re: Problems with new DBD
Next
From: Tim Frank
Date:
Subject: Re: Question Two: DB access