how to create aggregate xml document in 8.3? - Mailing list pgsql-general

From Matt Magoffin
Subject how to create aggregate xml document in 8.3?
Date
Msg-id 53966.192.168.1.108.1197359047.squirrel@msqr.us
Whole thread Raw
Responses Re: how to create aggregate xml document in 8.3?
List 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@





pgsql-general by date:

Previous
From: "Trevor Talbot"
Date:
Subject: Re: SQL design pattern for a delta trigger?
Next
From: Simon Riggs
Date:
Subject: Re: question about warm standby databases in 8.2.5