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
