From: Matthias Nagel <matthias.h.nagel@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
Date: Sat, 29 Sep 2012 17:46:32 +0200
Message-ID: <2031491.bojg3UQVmk@hek506> (raw)
In-Reply-To: <301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de>
References: <43516431.HfO3TYfNBy@hek506>
<k46vmq$p11$1@ger.gmane.org>
<301621609.148693.1348931598720.JavaMail.open-xchange@ox.ims-firmen.de>
Hello,
thank you. The "WITH" clause did the trick. I did not even know that such a thing exists. But as it turns out it makes the statement more readable and elegant but not faster.
The reason for the latter is that both the CTE and the UPDATE statement have the same "FROM ... WHERE ..." part, because the tempory calculation needs some input values from the same table. Hence the table is looked up twice instead once.
Matthias
Am Samstag 29 September 2012, 17:13:18 schrieb Andreas Kretschmer:
>
> 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
>
>
>
----------------------------------------------------------------------
Matthias Nagel
Willy-Andreas-Allee 1, Zimmer 506
76131 Karlsruhe
Telefon: +49-721-8695-1506
Mobil: +49-151-15998774
e-Mail: matthias.h.nagel@gmail.com
ICQ: 499797758
Skype: nagmat84
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: matthias.h.nagel@gmail.com
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
In-Reply-To: <2031491.bojg3UQVmk@hek506>
* 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