Re: Flattening a subquery

Ryan Campbell <rynwndy@yahoo.com> said:

> 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.

You need to define your own aggregate function. Check the mailing list
archives for exactly what you want (IIRC look for concat_text or concat).
Defining your own aggregate isn't difficult, there are instructions in the
online docs and an example in my notes at http://techdocs.postgresql.org/
(there might be an example in the docs too, I can't recall)

HTH

- Richard Huxton