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.
Thanks,
Raymond
