Re: how to create aggregate xml document in 8.3?

"Matt Magoffin" <postgresql(dot)org(at)msqr(dot)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"/><range string4="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