Matthias Nagel wrote on 29.09.2012 12:49:
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.
Something like:
with my_calc as (
select pk,
time_consuming_calculation_1 as calc1,
time_consuming_calculation_2 as calc2
from foo
)
update foo
set startTime = my_calc.calc1,
stopTime = my_calc.calc2,
duration = my_calc.calc2 - calc1
where foo.pk = my_calc.pk;
http://www.postgresql.org/docs/current/static/queries-with.html#QUERIES-WITH-MODIFYING
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
