Message-ID: <BAY13-F29B358ACA804B6359BB32D89840@phx.gbl>
Reply-To: mrblonde@locked.myftp.org
In-Reply-To: <20050122193539.GA88448@winnie.fuhr.org>
From: "adam etienne" <a_eti@hotmail.com>
To: mike@fuhr.org
Cc: pgsql-sql@postgresql.org
Subject: Re: update from multiple rows
Date: Sun, 23 Jan 2005 11:36:11 +0000
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-2022-jp; format=flowed

Thanks for your answer
In fact the computation is somewhat more complex than an average and the 
data set is quite large... I did some test with view & triggers but it's 
too slow..
Moreover, sometime i need to do big insertion or update and then other time 
i need juste little update of this table...
I would like to apply a trigger only for little update but i don't know how 
to proceed.. Maybe with a condition into the trigger.. But it's adding 
computation time...

Thanks again,
Etienne Adam


&gt; &gt;  I have some trouble updating a table like this one :
&gt; &gt;  date     |     data_raw     |     data_sys
&gt; &gt;  12-01   |   5                   |       4.5
&gt; &gt;  13-01   |   6                   |       6
&gt; &gt;  14-01   |   7                   |       8
&gt; &gt;
&gt; &gt; I would like to update the 'data_sys' row by computing values of 
multiple
&gt; &gt; 'data_raw' values. I mean for example :
&gt; &gt; data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
&gt; &gt; data_raw['14-01'] )/3;
&gt;
&gt;Is there a reason to maintain data_sys in the table?  Could you use
&gt;a view instead?  A view could do self joins (join the table against
&gt;itself) and perform the necessary calculations on the fly when you
&gt;make a query.  That could be expensive if you select large data
&gt;sets, but for small slices of data it might suffice.
&gt;
&gt;Another possibility might be to use a trigger to recalculate data_sys
&gt;when records are inserted, updated, or deleted.  You'd still have
&gt;to do a potentially expensive one-time update of the entire table,
&gt;but future updates would then touch only the rows that depend on
&gt;the data being inserted, updated, or deleted, and the calculated
&gt;values would always be current.  Using a trigger would require some
&gt;care, however, to avoid cascading updates that are unnecessary or
&gt;that could result in infinite recursion.

_________________________________________________________________
無料メールならやっぱり 「MSN Hotmail」 http://www.hotmail.com/