It was not intentionally made, but was remaining due to copy-paste from other queries. It is only one occurrence of unnecessary casting. I got the idea about such a query from an oracle pl/sql book.
C P Kulkarni On Sat, Jan 21, 2012 at 11:03 PM, Pavel Stehule <pavel.steh...@gmail.com>wrote: > 2012/1/21 c k <shreeseva.learn...@gmail.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.steh...@gmail.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.learn...@gmail.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 > >> > > > > > >
