pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReuse temporary calculation results in an SQL update query
10+ messages / 5 participants
[nested] [flat]
* Reuse temporary calculation results in an SQL update query
@ 2012-09-29 10:49 Matthias Nagel <matthias.h.nagel@gmail.com>
0 siblings, 5 replies; 10+ messages in thread
From: Matthias Nagel @ 2012-09-29 10:49 UTC (permalink / raw)
To: pgsql-sql
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
----------------------------------------------------------------------
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 10:55 Thomas Kellerer <spam_eater@gmx.net>
parent: Matthias Nagel <matthias.h.nagel@gmail.com>
4 siblings, 0 replies; 10+ messages in thread
From: Thomas Kellerer @ 2012-09-29 10:55 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 10:56 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Matthias Nagel <matthias.h.nagel@gmail.com>
4 siblings, 1 reply; 10+ messages in thread
From: Andreas Kretschmer @ 2012-09-29 10:56 UTC (permalink / raw)
To: Matthias Nagel <matthias.h.nagel@gmail.com>; pgsql-sql
Matthias Nagel <matthias.h.nagel@gmail.com> hat am 29. September 2012 um 12:49
geschrieben:
> 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;
The Duration - field is superfluous ...
As far as i know there is no way to re-use the result.
Regards, Andreas
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 11:04 Matthias Nagel <matthias.h.nagel@gmail.com>
parent: Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 0 replies; 10+ messages in thread
From: Matthias Nagel @ 2012-09-29 11:04 UTC (permalink / raw)
To: pgsql-sql
Hello,
> Matthias Nagel <matthias.h.nagel@gmail.com> hat am 29. September 2012 um 12:49
> geschrieben:
> > 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;
>
> The Duration - field is superfluous ...
>
I expected the answer ;-), but no, it is not superfluous. In this small example it might appear as if it is, but there are cases in that the start time and the duration time have values and the stop time equals null to indicate a running session. And for reasons that are beyond the orginal question, there are also cases where duration does not equal the difference between start and stop time.
> As far as i know there is no way to re-use the result.
Too bad.
> Regards, Andreas
Thanks, Matthias
----------------------------------------------------------------------
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 11:20 Jasen Betts <jasen@xnet.co.nz>
parent: Matthias Nagel <matthias.h.nagel@gmail.com>
4 siblings, 0 replies; 10+ messages in thread
From: Jasen Betts @ 2012-09-29 11:20 UTC (permalink / raw)
To: pgsql-sql
On 2012-09-29, 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
use a CTE.
http://www.postgresql.org/docs/9.1/static/queries-with.html
with a as (
select 'time consuming calculation 1' as tcc1
, 'time consuming calculation 2' as tcc2
)
update table1
SET StartTime = a.tcc1
StopTime = a.tcc2
Duration = a.tcc2 - a.tcc1
WHERE foo;
you man need to move foo into the CTE too.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 13:43 David Johnston <polobo@yahoo.com>
parent: Matthias Nagel <matthias.h.nagel@gmail.com>
4 siblings, 0 replies; 10+ messages in thread
From: David Johnston @ 2012-09-29 13:43 UTC (permalink / raw)
To: Matthias Nagel <matthias.h.nagel@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 14:13 Thomas Kellerer <spam_eater@gmx.net>
parent: Matthias Nagel <matthias.h.nagel@gmail.com>
4 siblings, 1 reply; 10+ messages in thread
From: Thomas Kellerer @ 2012-09-29 14:13 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query
@ 2012-09-29 15:13 Andreas Kretschmer <andreas@a-kretschmer.de>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 10+ messages in thread
From: Andreas Kretschmer @ 2012-09-29 15:13 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
@ 2012-09-29 15:46 Matthias Nagel <matthias.h.nagel@gmail.com>
parent: Andreas Kretschmer <andreas@a-kretschmer.de>
0 siblings, 1 reply; 10+ messages in thread
From: Matthias Nagel @ 2012-09-29 15:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
@ 2012-09-30 16:26 David Johnston <polobo@yahoo.com>
parent: Matthias Nagel <matthias.h.nagel@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: David Johnston @ 2012-09-30 16:26 UTC (permalink / raw)
To: 'Matthias Nagel' <matthias.h.nagel@gmail.com>; pgsql-sql
>
> 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.
This is unusual; the only WHERE clause you should require is some kind of key matching...
Like:
UPDATE tbl
SET ....
FROM (
WITH final_result AS (
SELECT pkid, ....
FROM tbl
WHERE ...
) -- /WITH
SELECT pkid, .... FROM final_result
) src -- /FROM
WHERE src.pkid = tbl.pkid
;
If you provide an actual query better help may be provided.
David J.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2012-09-30 16:26 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-29 10:49 Reuse temporary calculation results in an SQL update query Matthias Nagel <matthias.h.nagel@gmail.com>
2012-09-29 10:55 ` Thomas Kellerer <spam_eater@gmx.net>
2012-09-29 10:56 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2012-09-29 11:04 ` Matthias Nagel <matthias.h.nagel@gmail.com>
2012-09-29 11:20 ` Jasen Betts <jasen@xnet.co.nz>
2012-09-29 13:43 ` David Johnston <polobo@yahoo.com>
2012-09-29 14:13 ` Thomas Kellerer <spam_eater@gmx.net>
2012-09-29 15:13 ` Andreas Kretschmer <andreas@a-kretschmer.de>
2012-09-29 15:46 ` Re: Reuse temporary calculation results in an SQL update query [SOLVDED] Matthias Nagel <matthias.h.nagel@gmail.com>
2012-09-30 16:26 ` Re: Reuse temporary calculation results in an SQL update query [SOLVDED] David Johnston <polobo@yahoo.com>
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