"Matt Magoffin" <postgresql.org@xxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
