Re: plpgsql function case statement

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