2012/1/21 c k <shreeseva.learning@xxxxxxxxx>: > 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.stehule@xxxxxxxxx> > 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.learning@xxxxxxxxx>: >> > 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 >> > > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
