pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
plpgsql function case statement
2+ messages / 2 participants
[nested] [flat]

* plpgsql function case statement
@ 2001-09-12 15:46 Peter Schmidt <peterjs@home.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Peter Schmidt @ 2001-09-12 15:46 UTC (permalink / raw)
  To: pgsql-sql

I'm having trouble finding the best solution for the following plpgsql
function.
The function does the following:

- gets an integer from table1 (using function getHighMark)
- Updates info.lastused with the latest timestamp from usage_log where
info.id = usage_log.id, but only if the sequence number in usage_log is
greater than or equal to what was returned by getHighMark.
-  if the timestamp value returned by the subselect is null, I want to set
info.lastused to an exisiting value from another timestamp column in
info(firstused).

Without the case statement the update inserts null into each row where
criteria was not met.

rows_updated is a placemarker for later postgres version...we're on 7.0.3

One alternative we had thought of was to create a view inside the function
(and destroy it) that is based on seq_number, but CREATE VIEW inside the
function seems to fail when it includes the variable "wm".

Here's what I came up with...but it's clearly not very efficient...
Can anyone help?

TIA
Peter

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 =
                         case when
                                 (SELECT MAX(p.requesttime)
                                  FROM usage_log p
                                  WHERE p.id = info.id
                                  AND   p.seq_no >= wm)
                          = null
                          then firstused
                          else
                                 (SELECT MAX(p.requesttime)
                                  FROM usage_log p
                                  WHERE p.id = info.id
                                  AND   p.seq_no >= wm)
                          end;
                return rows_updated;
        end;
        '
language 'plpgsql';






^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: plpgsql function case statement
@ 2001-09-26 17:27 Masaru Sugawara <rk73@echna.ne.jp>
  0 siblings, 0 replies; 2+ messages in thread

From: Masaru Sugawara @ 2001-09-26 17:27 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-09-26 17:27 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-12 15:46 plpgsql function case statement Peter Schmidt <peterjs@home.com>
2001-09-26 17:27 Re: plpgsql function case statement Masaru Sugawara <rk73@echna.ne.jp>

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