Re: List Concatination

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