Skip site navigation (1) Skip section navigation (2)

Re: array_accum() and quoted content

From: valgog <valgog(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: array_accum() and quoted content
Date: 2008-07-29 10:08:01
Message-ID: 530d5fa2-dbf9-4c37-bc60-4b17e4a7e082@z72g2000hsb.googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-general
On Jul 29, 12:08 am, alvhe(dot)(dot)(dot)(at)commandprompt(dot)com (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 (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> 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?

In response to

pgsql-general by date

Next:From: Dmitry TeslenkoDate: 2008-07-29 10:25:21
Subject: Right way to reject INSERTs and UPDATEs
Previous:From: Asko OjaDate: 2008-07-29 08:35:09
Subject: Re: Clone a database to other machine

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group