Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THxK2-0001yD-Uy for pgsql-sql@postgresql.org; Sat, 29 Sep 2012 13:43:11 +0000 Received: from nm6-vm0.bullet.mail.ac4.yahoo.com ([98.139.52.70]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1THxJz-00066r-ER for pgsql-sql@postgresql.org; Sat, 29 Sep 2012 13:43:09 +0000 Received: from [98.139.52.197] by nm6.bullet.mail.ac4.yahoo.com with NNFMP; 29 Sep 2012 13:43:04 -0000 Received: from [98.139.52.144] by tm10.bullet.mail.ac4.yahoo.com with NNFMP; 29 Sep 2012 13:43:04 -0000 Received: from [127.0.0.1] by omp1027.mail.ac4.yahoo.com with NNFMP; 29 Sep 2012 13:43:04 -0000 X-Yahoo-Newman-Id: 810002.8357.bm@omp1027.mail.ac4.yahoo.com Received: (qmail 81378 invoked from network); 29 Sep 2012 13:43:04 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=MLXL6lYhoPkS1qm3OfsQkvFDS6WnVGKoVwKHkTmRwyQjxDF5cBuSMLjYHsqECEp65t8nM1NIg96wLflysuK26KSER0UJPWnR0U1u3EdvBHWwfw/mufFTV3XLH9OKYupiO3ueJcvQAD0rYkJL9ck9TdnpK7Gs9rvlkubimWaIB2g= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1348926184; bh=kSsi8/+FH/1Aeq25/A9GTjEGeaik6B0jNZU5Pwxmg9U=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=KG1okUSs4Jvg5VjxHS8sdN+UY/DgkVPnO2B/SDGYcwScNRT30j4aLD6idbLxlCmTXxDLv0CDKZdSG9cT0O9A+QZPxfiMk3HXEy4fdkN4nmNDnHZRnlwKfo59kdeq7Wp29Y2QSaqVYuyU8BCMD1ROfMqK/+1Jeffsea8gcd1WkbU= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: aqB3G4oVM1m79_6Sqg7qin8kYi_bcewyZgMLefegflM4K6U HjptuU0O5k2VV9gs0C0xbfG49J3NPR7HOva1TpkXmhBP83wL_bxQXlPsB2iX _Tw36vBxFOUPzwCvF.7xkRx7rDHwFLI5dLZEsBbxyqKfBXru3dHF7.R2pM67 KcPC_MAb9XTcHyEy2geDn4X.Awt8QK8Q053zZyJEOkVV9Uvn57PAFWoZlrMv 9ZeBYmcR30.cu.IUiM1cAQWCEkLDk7jkXOtXteApE1NiPE8IWhryJ6k6SPMX NsJhWQ3AGVAWCV7PLuhLEQQT33qvvyUx9jMk.JEtAkW1.fHQ85uQBKyJWcTB HpNKHn3sOgvTqwXdLEOcxnCkWCOO4CYpv05jhOAbLVffNhoZE9vaW0PgyXbN AFWgnv0.7l2xm2gb4ReQ4aZ4C4KAlDv3FTZQPJdYUvNEa6Y9tT2EdggwsTV6 Nijpa X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from [192.12.17.101] (polobo@24.93.23.188 with xymcookie) by smtp111-mob.biz.mail.ac4.yahoo.com with SMTP; 29 Sep 2012 06:43:04 -0700 PDT References: <43516431.HfO3TYfNBy@hek506> In-Reply-To: <43516431.HfO3TYfNBy@hek506> Mime-Version: 1.0 (1.0) Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii Message-Id: Cc: "pgsql-sql@postgresql.org" X-Mailer: iPad Mail (9B206) From: David Johnston Subject: Re: Reuse temporary calculation results in an SQL update query Date: Sat, 29 Sep 2012 09:43:06 -0400 To: Matthias Nagel X-Pg-Spam-Score: -2.8 (--) X-Archive-Number: 201209/60 X-Sequence-Number: 36862 On Sep 29, 2012, at 6:49, Matthias Nagel wrote:= > Hello, >=20 > is there any way how one can store the result of a time-consuming calculat= ion if this result is needed more than once in an SQL update query? This sol= ution might be PostgreSQL specific and not standard SQL compliant. Here is a= n example of what I want: >=20 > UPDATE table1 SET > StartTime =3D 'time consuming calculation 1', > StopTime =3D 'time consuming calculation 2', > Duration =3D 'time consuming calculation 2' - 'time consuming calculatio= n 1' > WHERE foo; >=20 > It would be nice, if I could use the "new" start and stop time to calculat= e the duration time. First of all it would make the SQL statement faster and= secondly much more cleaner and easily to understand. >=20 > Best regards, Matthias >=20 >=20 You are allowed to use a FROM clause with UPDATE so if you can figure out ho= w to write a SELECT query, including a CTE if needed, you can use that as yo= ur cache. An immutable function should also be optimized in theory though I've never t= ried it. David J.