Skip site navigation (1) Skip section navigation (2)

Re: aggregate function ?

From: Raimon Fernandez <coder(at)montx(dot)com>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: aggregate function ?
Date: 2007-05-21 18:40:29
Message-ID: 157693F0-A5A8-49BA-B2F7-A7861AF4FB13@montx.com (view raw or flat)
Thread:
Lists: pgsql-novice
ok, thanks for the clarification ...

I'll try to do it myself and see ... but also I would like to do it  
in a function/stored procedure, as will be more clear for me ...

and in this way I'll compare the speed difference ...

my initial approach is:

create temporary table test as SELECT  
oid,numero,data,concepte,deure,haver,(deure-haver) as saldo FROM  
assentaments WHERE clau_compte='0257000000002';

create a cursor,

loop for each row and update the saldo field

and return the select * from test

still working ...


regards and thanks again !


raimon




On 21/05/2007, at 14:46, Richard Broersma Jr wrote:

>
> --- Raimon Fernandez <coder(at)montx(dot)com> wrote:
>> This returns 3217 rows, and the value_sum is ok, but it takes too
>> long (89.45 sec)
>>
>> Can anyone confirm that it's doing for every row the
>> 'starting_sum' (first select), and if so, how to do it just once ?
>
> Yes, any sub-select in the Select expression list will be executed  
> many times. To fix this, you
> will need to reform your query by pushing this sub-select down to  
> the from clause.  Also you will
> need to add clau_compte to the SELECT expression list of both sub- 
> Selects
>
>     SELECT <your expression list>, Summed_assentaments.delta_sum +  
> Initialvalue.starting_sum
>       FROM ( <your first initial sub-select> ) AS  
> Summed_Assentaments( oid, concepte,deure, haver,
> delta_sum, clau_compte )
> INNER JOIN ( <your initial value sub-select> ) AS Initialvalue 
> ( starting_sum, clau_compte )
>         ON Summed_Assentaments.clau_compte = Initialvalue.clau_compte;
>
> I hope this can help to improve query time.
>
> Regards,
> Richard Broersma Jr.
>



In response to

pgsql-novice by date

Next:From: Derrick BettsDate: 2007-05-21 18:53:36
Subject: Multiple row update with trigger
Previous:From: Richard Broersma JrDate: 2007-05-21 12:46:27
Subject: Re: aggregate function ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group