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

Re: array_accum() and quoted content

From: "Raymond C(dot) Rodgers" <sinful622(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: array_accum() and quoted content
Date: 2008-07-28 23:01:28
Message-ID: 488E4FC8.1010205@gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
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%40alvh.no-ip.org
>
> the text is in spanish but the SQL commands should be trivial to follow.
>
>
> I think this is a FAQ.
>
>   
Thanks for the link, and the SQL is simple enough to follow. I'll give 
it a whirl. It would certainly be useful to have that SQL posted as a 
comment on the PostgreSQL documentation page I referenced earlier; maybe 
it could stop being a FAQ, and end up a FFA (Frequently Found Answer) :-)

Thanks again,
Raymond

In response to

pgsql-general by date

Next:From: David FetterDate: 2008-07-29 00:52:19
Subject: Re: array_accum() and quoted content
Previous:From: Alvaro HerreraDate: 2008-07-28 22:08:10
Subject: Re: array_accum() and quoted content

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