Re: how to create aggregate xml document in 8.3?

"Matt Magoffin" <postgresql.org@msqr.us> writes:
> Hello, I'm trying to write a query to return an XML document like
> <root foo="bar">
>   <range range="x" count="123">
>   <range range="y" count="345">
>   ...
> </root>

Something like this:

regression=# select xmlelement(name root, xmlagg(x)) from
regression-#   (select xmlelement(name range, xmlattributes(string4, count(*) as count)) as x from tenk1 group by
string4)ss; 
                                                                              xmlelement
                                               

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 <root><range string4="OOOOxx" count="2500"/><range string4="AAAAxx" count="2500"/><range string4="HHHHxx"
count="2500"/><rangestring4="VVVVxx" count="2500"/></root> 
(1 row)

You need a subquery because your setup requires two levels of
aggregation: one to make the grouped counts, and then another one
for the xmlagg() (which is basically just text concatenation).

            regards, tom lane