Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QHOYh35093 for ; Wed, 26 Sep 2001 13:24:34 -0400 (EDT) (envelope-from rk73@echna.ne.jp) Received: from VIP333 [210.175.55.145] by echna.ne.jp with ESMTP (SMTPD32-6.06) id AF4A410020E; Thu, 27 Sep 2001 02:24:26 +0900 Date: Thu, 27 Sep 2001 02:27:25 +0900 From: Masaru Sugawara To: pgsql-sql@postgresql.org Subject: Re: plpgsql function case statement Message-Id: <20010927022655.642E.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.06 X-Archive-Number: 200109/227 X-Sequence-Number: 4909 Peter Schmidt wrote: >I'm having trouble finding the best solution for the following plpgsql >function. >The function does the following: Hi, I post in this mailing list for the first time. :-) I think your pl/pgsql function runs properly, but there is likely to exist another SQL pattern. In this case, estimation of whether the TIMESTAMP value or NULL is finished at one time. Please try it, if you have time for checking. (on v7.1.3) create function updateLastUsed(text, text) returns integer as ' declare wm integer; rows_updated integer; begin rows_updated := 0; wm := getHighmark($1,$2); -------------------------------------------------------------- UPDATE info SET lastused = (SELECT case when MAX(p.requesttime) is null then info.firstused else MAX(p.requesttime) end FROM usage_log p WHERE p.id = info.id AND p.seq_no >= wm ) -------------------------------------------------------------- return rows_updated; end; ' language 'plpgsql'; -------------------- M.Sugawara rk73@echna.ne.jp