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 Reyes||Date: 2008-07-28 21:50:23|
|Subject: Re: Clone a database to other machine|
|Previous:||From: smiley2211||Date: 2008-07-28 20:06:51|
|Subject: Re: Must be table owner to truncate?|