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

Re: Reuse temporary calculation results in an SQL update query

From: Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
To: Thomas Kellerer <spam_eater(at)gmx(dot)net>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Reuse temporary calculation results in an SQL update query
Date: 2012-09-29 15:13:18
Message-ID: 301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de (view raw or flat)
Thread:
Lists: pgsql-sql

Thomas Kellerer <spam_eater(at)gmx(dot)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


In response to

Responses

pgsql-sql by date

Next:From: Matthias NagelDate: 2012-09-29 15:46:32
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
Previous:From: Thomas KellererDate: 2012-09-29 14:13:59
Subject: Re: Reuse temporary calculation results in an SQL update query

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