From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | <recampbell(at)bigfoot(dot)com>, "Ryan Campbell" <rynwndy(at)yahoo(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Flattening a subquery |
Date: | 2001-04-19 08:41:51 |
Message-ID: | 20010419084151.317EB2A3A7@mainbox.archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Ryan Campbell <rynwndy(at)yahoo(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Diana Cionoiu | 2001-04-19 09:23:29 | client ssl |
Previous Message | Toomas Gavrilin | 2001-04-19 08:31:57 | oid |