From: Richard Huxton <dev@archonet.com>
To: josh@agliodbs.com
Cc: sqllist <pgsql-sql@postgresql.org>
Subject: Re: List Concatination
Date: Fri, 09 Mar 2001 11:16:04 +0000
Message-ID: <3AA8BB73.5647B248@archonet.com> (raw)
References: <3AA86767.698DDFFF@agliodbs.com>
Josh Berkus wrote:
> I have an interesting problem. For purpose of presentation to users,
> I'd like to concatinate a list of VARCHAR values from a subtable. To
> simplify my actual situation:
>
> What I'd like to be able to do is present a list of clients and their
> comma-seperated contacts in paragraph form, hence:
>
> Client Contacts
> McMurphy Assoc. Jim Henson, Pat O'Gannet, Kelley Moore
>
> Ross Construction Sara Vaugn, Bill Murray, Peter Frump,
> Siskel Ebert
>
Well, basically you can use a standard join, order it and eliminate
duplicate client names in the application. That's the "proper" way.
But - if you don't care about the order of contacts you can define an
aggregate function:
create aggregate catenate(sfunc1=textcat, basetype=text, stype1=text, initcond1='');
Then group by client and catenate(firstname || ' ' || lastname)
You'll want to read the CREATE AGGREGATE page in the reference manual,
replace textcat with your own routine that adds a comma and you'll need
a finalisation routine to strip the final trailing comma.
Note that this is probably not a good idea - the ordering of the
contacts will not be well-defined. When I asked about this Tom Lane was
quite surprised that it worked, so no guarantees about long-term suitability.
- Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, josh@agliodbs.com
Subject: Re: List Concatination
In-Reply-To: <3AA8BB73.5647B248@archonet.com>
References: <3AA86767.698DDFFF@agliodbs.com> <3AA8BB73.5647B248@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox