Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2FGUmx27402 for ; Thu, 15 Mar 2001 11:30:48 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [207.172.28.154] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4.1) with ESMTP id 25385 for pgsql-sql@postgresql.org; Thu, 15 Mar 2001 08:30:44 -0800 Message-ID: <3AB0EDFE.998C387C@agliodbs.com> Date: Thu, 15 Mar 2001 08:29:50 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 CC: sqllist Subject: Re: List Concatination References: <3AA86767.698DDFFF@agliodbs.com> <3AA8BB73.5647B248@archonet.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/312 X-Sequence-Number: 1976 Richard, I wanted to thank you for the concatination suggestion ... on testing, a custom aggregate *was* faster than procedural concatination ... much faster. > 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. Actually, if you use a sub-select as your data source, you can control both the appearance and the order of the catenated values: SELECT client, catenate(con_name) FROM (SELECT client, (firstname || ' ' || lastname || ', ' FROM contacts ORDER BY lastname ASC) AS con_list GROUP BY client; This seems to work pretty well. > 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. Hmmm ... this feature is very, very, useful now that I know how to use it. I'd love to see it hang around for future versions of PgSQL. Tom? -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco