On Sep 29, 2012, at 6:49, Matthias Nagel <matthias.h.na...@gmail.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.



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Reply via email to