Re: Flattening a subquery - Mailing list pgsql-general

From Richard Huxton
Subject Re: Flattening a subquery
Date
Msg-id 20010419084151.317EB2A3A7@mainbox.archonet.com
Whole thread Raw
In response to Flattening a subquery  (Ryan Campbell <rynwndy@yahoo.com>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Matthew Hixson
Date:
Subject: syntax error 44 : ->
Next
From: Anand Raman
Date:
Subject: Re: plpgsql problem