From: | "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)us> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: how to create aggregate xml document in 8.3? |
Date: | 2007-12-12 04:49:24 |
Message-ID: | 51059.192.168.1.108.1197434964.squirrel@msqr.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> "Matt Magoffin" <postgresql(dot)org(at)msqr(dot)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@
From | Date | Subject | |
---|---|---|---|
Next Message | Guy Rouillier | 2007-12-12 04:57:30 | Re: Hijack! |
Previous Message | Scott Marlowe | 2007-12-12 04:24:21 | Re: Would it be OK if I put db file on a ext2 filesystem? |