how to create aggregate xml document in 8.3?

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@

Responses

Browse pgsql-general by date

  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?