From: David Johnston <polobo@yahoo.com>
To: Matthias Nagel <matthias.h.nagel@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Reuse temporary calculation results in an SQL update query
Date: Sat, 29 Sep 2012 09:43:06 -0400
Message-ID: <B638A88E-32D8-455E-9224-C5708D5F2585@yahoo.com> (raw)
In-Reply-To: <43516431.HfO3TYfNBy@hek506>
References: <43516431.HfO3TYfNBy@hek506>
On Sep 29, 2012, at 6:49, Matthias Nagel <matthias.h.nagel@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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: polobo@yahoo.com, matthias.h.nagel@gmail.com
Subject: Re: Reuse temporary calculation results in an SQL update query
In-Reply-To: <B638A88E-32D8-455E-9224-C5708D5F2585@yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox