Re: array_accum() and quoted content

On Mon, Jul 28, 2008 at 04:11:26PM -0400, Raymond C. Rodgers wrote:
> 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

You could do something like

array_to_string(
    array_accum(p.publisher_name),
    '|' -- or any other string guaranteed not to appear in the publisher_name
)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate