On 2012-09-29, Matthias Nagel <matthias.h.na...@gmail.com> wrote: > Hello, > > is there any way how one can store the result of a time-consuming calculation > if this result is needed more than once in an SQL update query? This solution > might be PostgreSQL specific and not standard SQL compliant. Here is an > example of what I want: > > UPDATE table1 SET > StartTime = 'time consuming calculation 1', > StopTime = 'time consuming calculation 2', > Duration = 'time consuming calculation 2' - 'time consuming calculation 1' > WHERE foo; > > It would be nice, if I could use the "new" start and stop time to calculate > the duration time. First of all it would make the SQL statement faster and > secondly much more cleaner and easily to understand. > > Best regards, Matthias
use a CTE. http://www.postgresql.org/docs/9.1/static/queries-with.html with a as ( select 'time consuming calculation 1' as tcc1 , 'time consuming calculation 2' as tcc2 ) update table1 SET StartTime = a.tcc1 StopTime = a.tcc2 Duration = a.tcc2 - a.tcc1 WHERE foo; you man need to move foo into the CTE too. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
