Skip site navigation (1) Skip section navigation (2)

Re: Reuse temporary calculation results in an SQL update query

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Reuse temporary calculation results in an SQL update query
Date: 2012-09-29 14:13:59
Message-ID: k46vmq$p11$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-sql
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




In response to

Responses

pgsql-sql by date

Next:From: Andreas KretschmerDate: 2012-09-29 15:13:18
Subject: Re: Reuse temporary calculation results in an SQL update query
Previous:From: David JohnstonDate: 2012-09-29 13:43:06
Subject: Re: Reuse temporary calculation results in an SQL update query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group