From: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Reuse temporary calculation results in an SQL update query |
Date: | 2012-09-29 11:20:04 |
Message-ID: | k46lh4$b08$1@reversiblemaps.ath.cx |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 2012-09-29, Matthias Nagel <matthias(dot)h(dot)nagel(at)gmail(dot)com> wrote:
> 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.
>
> Best regards, Matthias
use a CTE.
http://www.postgresql.org/docs/9.1/static/queries-with.html
with a as (
select 'time consuming calculation 1' as tcc1
, 'time consuming calculation 2' as tcc2
)
update table1
SET StartTime = a.tcc1
StopTime = a.tcc2
Duration = a.tcc2 - a.tcc1
WHERE foo;
you man need to move foo into the CTE too.
--
⚂⚃ 100% natural
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-09-29 13:43:06 | Re: Reuse temporary calculation results in an SQL update query |
Previous Message | Matthias Nagel | 2012-09-29 11:04:23 | Re: Reuse temporary calculation results in an SQL update query |