Обсуждение: 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@gmail.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
Drat, thanks. Other than array_accum() I've never used arrays in PostgreSQL, so I wasn't aware of that behavior."Raymond C. Rodgers" <sinful622@gmail.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#AEN5876Is 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
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:
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
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: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.
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:
Thanks again,
Raymond
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) :-)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 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@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
On Jul 29, 12:08 am, alvhe...@commandprompt.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...@postgresql.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?