From: Peter Schmidt <peterjs@home.com>
To: pgsql-sql@postgresql.org
Subject: plpgsql function case statement
Date: Wed, 12 Sep 2001 08:46:34 -0700
Message-ID: <9nnvu8$8hk$1@news.tht.net> (raw)
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';
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: peterjs@home.com
Subject: Re: plpgsql function case statement
In-Reply-To: <9nnvu8$8hk$1@news.tht.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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