From: | c k <shreeseva(dot)learning(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: grouping of query data in xml |
Date: | 2012-01-21 18:07:40 |
Message-ID: | CAN2Y=uPuOyRt8TKw716OXgGMypCqCQ4NPvK=si1iVzccPcD2Dw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
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(dot)stehule(at)gmail(dot)com>wrote:
> 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
> >> >
> >
> >
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2012-01-22 17:51:31 | Re: Force explicit transactions on insert / update |
Previous Message | Pavel Stehule | 2012-01-21 17:33:48 | Re: grouping of query data in xml |
From | Date | Subject | |
---|---|---|---|
Next Message | Jose Carlos Martinez Llario | 2012-01-21 21:11:32 | log_statement variable does not admit all value |
Previous Message | Pavel Stehule | 2012-01-21 17:33:48 | Re: grouping of query data in xml |