how to create aggregate xml document in 8.3?

Поиск
Список
Период
Сортировка
От Matt Magoffin
Тема how to create aggregate xml document in 8.3?
Дата
Msg-id 53966.192.168.1.108.1197359047.squirrel@msqr.us
обсуждение исходный текст
Ответы Re: how to create aggregate xml document in 8.3?
Список 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 по дате отправления:

Предыдущее
От: "Trevor Talbot"
Дата:
Сообщение: Re: SQL design pattern for a delta trigger?
Следующее
От: Simon Riggs
Дата:
Сообщение: Re: question about warm standby databases in 8.2.5