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 20:11:26
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Alvaro Herrera wrote:
> Raymond C. Rodgers escribió:
>> Drat, thanks. Other than array_accum() I've never used arrays in  
>> PostgreSQL, so I wasn't aware of that behavior.
> Why do you want to use array_accum() in the first place?  Maybe there
> are better ways to do what you are using it for, that do not subject you
> to the awkward ways of arrays.
I'm not a database professional, so I'll explain this as best I can. 
There are two tables that are linked via entries in a third: company, 
publisher, and company-publisher association. A publisher can be 
referenced by multiple companies, so the company-publisher association 
table is a simple two column table that consists of foreign keyed 
references to the company table's primary key and the publisher table's 
primary key. 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 response to


pgsql-general by date

Next:From: Francisco ReyesDate: 2008-07-28 21:50:23
Subject: Re: Clone a database to other machine
Previous:From: smiley2211Date: 2008-07-28 20:06:51
Subject: Re: Must be table owner to truncate?

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