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
Message-ID: <BAY13-F29B358ACA804B6359BB32D89840@phx.gbl> (raw)
In-Reply-To: <20050122193539.GA88448@winnie.fuhr.org>
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
> > I have some trouble updating a table like this one :
> > date | data_raw | data_sys
> > 12-01 | 5 | 4.5
> > 13-01 | 6 | 6
> > 14-01 | 7 | 8
> >
> > I would like to update the 'data_sys' row by computing values of
multiple
> > 'data_raw' values. I mean for example :
> > data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
> > data_raw['14-01'] )/3;
>
>Is there a reason to maintain data_sys in the table? Could you use
>a view instead? A view could do self joins (join the table against
>itself) and perform the necessary calculations on the fly when you
>make a query. That could be expensive if you select large data
>sets, but for small slices of data it might suffice.
>
>Another possibility might be to use a trigger to recalculate data_sys
>when records are inserted, updated, or deleted. You'd still have
>to do a potentially expensive one-time update of the entire table,
>but future updates would then touch only the rows that depend on
>the data being inserted, updated, or deleted, and the calculated
>values would always be current. Using a trigger would require some
>care, however, to avoid cascading updates that are unnecessary or
>that could result in infinite recursion.
_________________________________________________________________
$BL5NA%a!<%k$J$i$d$C$Q$j(B $B!V(BMSN Hotmail$B!W(B http://www.hotmail.com/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: a_eti@hotmail.com, mike@fuhr.org
Subject: Re: update from multiple rows
In-Reply-To: <BAY13-F29B358ACA804B6359BB32D89840@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox