Обсуждение: how to create aggregate xml document in 8.3?
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> I started with select xmlelement(name range, xmlattributes(m.range, count(s.id) as "count")) from mb_sale s inner join mb_lead m on m.sale_id = s.id where s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30') and s.sale_type = 'd' -- direct sale group by m.range order by m.range; xmlelement ------------------------------------ <range range="0-30" count="215"/> <range range="31-60" count="202"/> <range range="61-90" count="64"/> <range range="91-120" count="22"/> (4 rows) which returns 4 individual rows as shown, but I can't figure out how to correctly produce this with a root element and the <range> elements nested under that. I tried a variety of ways, including select xmlelement(name "matchback-months", xmlattributes('bar' as "foo"), xmlagg( xmlelement(name "range", xmlattributes(m.range, count(s.id) as "count") ) ) ) from mb_sale s inner join mb_lead m on m.sale_id = s.id where s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30') and s.sale_type = 'd' group by m.range order by m.range; which returns an error "aggregate function calls cannot be nested". Is this type of output possible in 8.3? -- m@
"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
> "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"/><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). Thanks very much, that helps. Now I'm wondering if it's also possible to then fill in another nested element level in the XML output, from the rows that are aggregated into the <range> count. So from your example, something like <root> <range string4="0000xx" count="2500"> <string4 foo="bar1"/> <string4 foo="bar2"/> </range> <range string4="AAAAxx" count="2500"> <string4 foo="bar3"/> .... </range> .... </root> Any thoughts on this? -- m@
"Matt Magoffin" <postgresql.org@msqr.us> writes: > Thanks very much, that helps. Now I'm wondering if it's also possible to > then fill in another nested element level in the XML output, from the rows > that are aggregated into the <range> count. Something involving xmlagg in the sub-query, perhaps? No time to experiment with it now. regards, tom lane
> "Matt Magoffin" <postgresql.org@msqr.us> writes: >> Thanks very much, that helps. Now I'm wondering if it's also possible to >> then fill in another nested element level in the XML output, from the >> rows >> that are aggregated into the <range> count. > > Something involving xmlagg in the sub-query, perhaps? No time to > experiment with it now. Thanks for the first tip, anyway. I got stuck with trying this out myself... I had ended up with select xmlelement( name "matchback-months", xmlattributes(1 as "count", 'true' as "multi"), xmlagg(ranges)) from ( select xmlelement(name "range", xmlattributes(m.range, count(s.id) as "sales-conv-from-lead"), xmlagg(sales)) from ( select xmlelement(name "sale", xmlattributes(ss.vin, ms.lead_id as "lead-id") ) as sales from mb_sale ss inner join mb_lead ms on ms.sale_id = ss.id where ss.sale_date >= date('2007-08-01') and ss.sale_date <= date('2007-08-30') and ss.sale_type = 'd' and ms.range = m.range order by ss.sale_date ) ssub ) as ranges from mb_sale s inner join mb_lead m on m.sale_id = s.id where s.sale_date >= date('2007-08-01') and s.sale_date <= date('2007-08-30') and s.sale_type = 'd' group by m.range order by m.range ) sub; but this does not compile: ERROR: syntax error at or near "from" LINE 20: from mb_sale s ^ If anyone has any suggestions, much appreciated. -- m@