On Jul 29, 12:08 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: > Raymond C. Rodgers escribió: > > > The query in which I'm using array_accum() is building a > > list of companies and the associated publishers for each. For example: > > > SELECT c.company_id, c.company_name, array_accum(p.publisher_name) AS > > publishers FROM company_table c LEFT JOIN company_publisher_assoc cpa ON > > c.company_id = cpa.company_id LEFT JOIN publisher_table p ON > > cpa.publisher_id = p.publisher_id GROUP BY c.company_id, c.company_name > > ORDER BY company_name > > > (This query isn't direct out of my code, and thus may have errors, but > > it should convey the idea of what I'm trying to accomplish.) > > > The result is that I should have a single row containing the company_id, > > company_name, and publishers' names if any. > > In order to do this you can use a custom aggregate function to > concatenate the texts. I have described this previously here: > > http://archives.postgresql.org/message-id/20080327234052.GZ8764%40alv... > > the text is in spanish but the SQL commands should be trivial to follow. > > I think this is a FAQ. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > -- > Sent via pgsql-general mailing list ([EMAIL PROTECTED]) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general
Appropos, have you had a chance to compare the performance of this approach and when you use array_to_string( array_accum( $1 ), ' ' ) instead of the text_concat( $1 ) PL/pgSQL based aggregate function? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
