Обсуждение: 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@