Re: array_accum() and quoted content

Some time ago, I found the aggregate function array_accum() listed on
the PostgreSQL web site on a page similar to
http://www.postgresql.org/docs/8.2/static/xaggr.html , and implemented
it in a database that hasn't seen much use. More recently, for a client,
I again used the function but I'm running into some inconsistencies
within a select query in which I'm using the aggregate. The problem is
that sometimes the data contained in the array is quoted, and other
times it isn't, all within the same query results. My returned data may
appear like this:

accumed_column
============
{"test 1","test 2","test 3"}
{test4,test5,test6}

The only difference I can see is that the quotes don't appear when the
values returned don't contain white space, and do when white space is
present. Is there any way to force consistency? My PHP code currently is
expecting quoted strings to be returned.

Thank you,
Raymond

"Raymond C. Rodgers" <sinful622(at)gmail(dot)com> writes:
> The only difference I can see is that the quotes don't appear when the
> values returned don't contain white space, and do when white space is
> present.

That is per the definition of array output format:
http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876

> Is there any way to force consistency? My PHP code currently is
> expecting quoted strings to be returned.

Better fix your PHP code.

regards, tom lane

Tom Lane wrote:
> "Raymond C. Rodgers" <sinful622(at)gmail(dot)com> writes:
>
>> The only difference I can see is that the quotes don't appear when the
>> values returned don't contain white space, and do when white space is
>> present.
>>
>
> That is per the definition of array output format:
> http://www.postgresql.org/docs/8.2/static/arrays.html#AEN5876
>
>
>> Is there any way to force consistency? My PHP code currently is
>> expecting quoted strings to be returned.
>>
>
> Better fix your PHP code.
>
> regards, tom lane
>
Drat, thanks. Other than array_accum() I've never used arrays in
PostgreSQL, so I wasn't aware of that behavior.

Raymond

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.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

Alvaro Herrera wrote:
> 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.
>
>
Thanks for the link, and the SQL is simple enough to follow. I'll give
it a whirl. It would certainly be useful to have that SQL posted as a
comment on the PostgreSQL documentation page I referenced earlier; maybe
it could stop being a FAQ, and end up a FFA (Frequently Found Answer) :-)

Thanks again,
Raymond

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

On Jul 29, 12:08 am, alvhe(dot)(dot)(dot)(at)commandprompt(dot)com (Alvaro Herrera)
wrote:
> 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%40alv...
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-gene(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

Appropos, have you had a chance to compare the performance of this
approach and when you use array_to_string( array_accum( $1 ), ' ' )
instead of the text_concat( $1 ) PL/pgSQL based aggregate function?