From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | "Raymond C(dot) Rodgers" <sinful622(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: array_accum() and quoted content |
Date: | 2008-07-28 22:08:10 |
Message-ID: | 20080728220809.GD14617@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond C. Rodgers | 2008-07-28 23:01:28 | Re: array_accum() and quoted content |
Previous Message | Francisco Reyes | 2008-07-28 21:50:23 | Re: Clone a database to other machine |