Re: Reuse temporary calculation results in an SQL update query

On Sep 29, 2012, at 6:49, 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
>
>

You are allowed to use a FROM clause with UPDATE so if you can figure out how to write a SELECT query, including a CTE if needed, you can use that as your cache.

An immutable function should also be optimized in theory though I've never tried it.

David J.