Re: how to create aggregate xml document in 8.3?

Поиск
Список
Период
Сортировка
Искать

Re: how to create aggregate xml document in 8.3?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Matt Magoffin"  writes:
> Hello, I'm trying to write a query to return an XML document like
> 
>   
>   
>   ...
> 

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                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
(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

Re: how to create aggregate xml document in 8.3?

От:
"Matt Magoffin" <postgresql.org@msqr.us>
Дата:
> "Matt Magoffin"  writes:
>> Hello, I'm trying to write a query to return an XML document like
>> 
>>   
>>   
>>   ...
>> 
>
> 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
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
>   count="2500"/> string4="VVVVxx" count="2500"/>
> (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  count. So from your example,
something like


  
    
    
  
  
    
    ....
  
  ....


Any thoughts on this?

-- m@

Re: how to create aggregate xml document in 8.3?

От:
"Matt Magoffin" <postgresql.org@msqr.us>
Дата:
> "Matt Magoffin"  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  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@

how to create aggregate xml document in 8.3?

От:
"Matt Magoffin" <postgresql.org@msqr.us>
Дата:
Hello, I'm trying to write a query to return an XML document like


  
  
  ...


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
------------------------------------
 
 
 
 
(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  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@

Re: how to create aggregate xml document in 8.3?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Matt Magoffin"  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  count.

Something involving xmlagg in the sub-query, perhaps?  No time to
experiment with it now.

			regards, tom lane
FAQ