Matthias Nagel wrote on 29.09.2012 12:49:
> 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.
with my_calc as (
time_consuming_calculation_1 as calc1,
time_consuming_calculation_2 as calc2
set startTime = my_calc.calc1,
stopTime = my_calc.calc2,
duration = my_calc.calc2 - calc1
where foo.pk = my_calc.pk;
In response to
pgsql-sql by date
|Next:||From: Andreas Kretschmer||Date: 2012-09-29 10:56:35|
|Subject: Re: Reuse temporary calculation results in an SQL update
|Previous:||From: Matthias Nagel||Date: 2012-09-29 10:49:18|
|Subject: Reuse temporary calculation results in an SQL update query|