From: | "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | how to create aggregate xml document in 8.3? |
Date: | 2007-12-11 07:44:07 |
Message-ID: | 53966.192.168.1.108.1197359047.squirrel@msqr.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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@
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2007-12-11 08:20:48 | Re: question about warm standby databases in 8.2.5 |
Previous Message | Trevor Talbot | 2007-12-11 07:24:52 | Re: SQL design pattern for a delta trigger? |