Thread: Who knows this SELECT in PostgreSQL
Dear gurus, I'm sure someone knows immediately how to translate this mysql to postgresql: SELECT GROUP_CONCAT(dbmail_messageblks.messageblk ORDER BY dbmail_messageblks.is_header DESC SEPARATOR '') FROM dbmail_messages JOIN dbmail_messageblks ON (dbmail_messages.physmessage_id=dbmail_messageblks.physmessage_id) WHERE dbmail_messages.message_idnr='${MSG_UID}' GROUP BY dbmail_messages.message_idnr" It should simply return the whole message which can be truncated to pieces into dbmail_messageblks and return it as a single entity. I found this for mysql and would like to rewrite the shell script to postgresql, but am stuck at this select. mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4
Michael Monnerie написа: > Dear gurus, I'm sure someone knows immediately how to translate this > mysql to postgresql: > > SELECT GROUP_CONCAT(dbmail_messageblks.messageblk > ORDER BY dbmail_messageblks.is_header DESC SEPARATOR '') > FROM dbmail_messages > JOIN dbmail_messageblks ON > (dbmail_messages.physmessage_id=dbmail_messageblks.physmessage_id) > WHERE dbmail_messages.message_idnr='${MSG_UID}' > GROUP BY dbmail_messages.message_idnr" > > It should simply return the whole message which can be truncated to > pieces into dbmail_messageblks and return it as a single entity. I found > this for mysql and would like to rewrite the shell script to postgresql, > but am stuck at this select. > > mfg zmi CREATE AGGREGATE public.array_agg(ANYELEMENT) ( SFUNC = ARRAY_APPEND, STYPE = ANYARRAY, INITCOND = '{}' ); SELECT ARRAY_TO_STRING(public.ARRAY_AGG(b.messageblk),'') AS the_message FROM dbmail_messages AS m JOIN dbmail_messageblks AS b ON (m.physmessage_id = b.physmessage_id) WHERE m.message_idnr = '${MSG_UID}' GROUP BY m.message_idnr; If you can wait for or want to test version 8.4 (which is in Beta right now and would be released relatively soon) you can skip the creation of the aggregate. -- Milen A. Radev
On Samstag 06 Juni 2009 Milen A. Radev wrote: > CREATE AGGREGATE public.array_agg(ANYELEMENT) ( > SFUNC = ARRAY_APPEND, > STYPE = ANYARRAY, > INITCOND = '{}' > ); > SELECT > ARRAY_TO_STRING(public.ARRAY_AGG(b.messageblk),'') AS the_message Wow, very cryptic. Would never have found that, thanks a lot! > If you can wait for or want to test version 8.4 (which is in Beta > right now and would be released relatively soon) you can skip the > creation of the aggregate. Need it now, but good to know 8.4 helps here :-) mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0660 / 415 65 31 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4