From: Andreas Kretschmer <andreas@a-kretschmer.de>
To: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Reuse temporary calculation results in an SQL update query
Date: Sat, 29 Sep 2012 17:13:18 +0200 (CEST)
Message-ID: <301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de> (raw)
In-Reply-To: <k46vmq$p11$1@ger.gmane.org>
References: <43516431.HfO3TYfNBy@hek506>
<k46vmq$p11$1@ger.gmane.org>
Thomas Kellerer <spam_eater@gmx.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
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: andreas@a-kretschmer.de, spam_eater@gmx.net
Subject: Re: Reuse temporary calculation results in an SQL update query
In-Reply-To: <301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de>
* 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