Thomas Kellerer <spam_ea...@gmx.net> hat am 29. September 2012 um 16:13 geschrieben: > 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
Yeah, with a WITH - CTE, cool ;-) Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
