Re: grouping of query data in xml

2012/1/21 c k <shreeseva(dot)learning(at)gmail(dot)com>:
> I have written a query as follows
>
> select xmlelement(name accgroup, xmlforest(accgroupid, accgroupname,
> xmlforest(refaccdocid,d, c) as doc))::xml AS xmldata
>  from (select 'acc group' as accgroup, accgroups.accgroupid,
> accgroups.accgroupname,  'docs' as doc, act.refaccdocid, sum(act.debit) as
> d, sum(act.credit) as c from accgroups inner join accountingtransactions as
> act on accgroups.accgroupid=act.accgroupid where accgroups.accgroupid
> between 6050 and 6055 group by accgroups.accgroupid, accgroups.accgroupname,
> act.refaccdocid limit 10) as b
>
> which gives output I need in really fast way. (processing 1000 rows in
> around 200 ms)
> but when using query_to_xml with the same output is taking much higher time
> (processing 1000 rows in around 4000 ms) .
>
> Can you please give me the answer for this? Why query_to_xml is taking too
> much time?

I don't know - I see only useles casts in your queries, but in both queries

SELECT xmlforest(....)::xml is nonsens

Regards

Pavel

>
> Regards,
>
> CPKulkarni
>
>
> On Sat, Jan 21, 2012 at 9:40 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> Hello
>>
>> there are others SQL/XML functions - with this function, you can
>> create xml like you need
>>
>> http://www.postgresql.org/docs/9.1/static/functions-xml.html
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>> 2012/1/21 c k <shreeseva(dot)learning(at)gmail(dot)com>:
>> > Hi friends,
>> >
>> > I am working with some xml exporting functionality of our application. I
>> > am
>> > testing query_to_xml function supported in PostgreSQL 9.1. XML output
>> > should
>> > be
>> >  <accgroup>
>> >   <accgroupid>1</accgroupid>
>> >   <accgroupname>COMPANY ACCOUNT</accgroupname>
>> >  <docs>
>> >   <refaccdocid>638168</refaccdocid>
>> >   <debit>10000.0000</debit>
>> >   <credit>0.0000</credit>
>> >  </docs>
>> >  <docs>
>> >  <refaccdocid>638168</refaccdocid>
>> >   <debit>0.0000</debit>
>> >   <credit>10000.0000</credit>
>> >   </docs>
>> > </accgroup>
>> >
>> > but the output is as follows:
>> > <row>
>> >   <accgroup>acc group</accgroup>
>> >   <accgroupid>1</accgroupid>
>> >   <accgroupname>COMPANY ACCOUNT</accgroupname>
>> >   <docs>docs</docs>
>> >   <refaccdocid>638168</refaccdocid>
>> >   <debit>10000.0000</debit>
>> >   <credit>0.0000</credit>
>> > </row>
>> >
>> > <row>
>> >   <accgroup>acc group</accgroup>
>> >   <accgroupid>1</accgroupid>
>> >   <accgroupname>COMPANY ACCOUNT</accgroupname>
>> >   <docs>docs</docs>
>> >   <refaccdocid>638168</refaccdocid>
>> >   <debit>0.0000</debit>
>> >   <credit>10000.0000</credit>
>> > </row>
>> >
>> > How can grouping on specific columns can be done in result xml through
>> > using
>> > query_to_xml?
>> > I am expecting help from postgresql community as always.
>> >
>> > Thanks
>> >
>> > CPKulkarni
>> >
>
>