I have the following domain defined:
CREATE DOMAIN percentage AS real
CONSTRAINT percentage_check CHECK (((VALUE >= 0.0) AND (VALUE <=
1.0)));
The various values (aa,bb,cc and dd) defined as "percentage" can increase over
time, to a maximum value of 1.0. In fact, I have one table with four different
"percentage" values which can increase. The plpsql function I'm writing is
going to be called from a cron job every 5 minutes to update "aa, bb, cc and
dd" percentages every 5 minutes. The amount of the update is a set amount
(we'll say .1 for the sake of argument) times the number of five minute
intervals (num_intervals * .1) which have elapsed since the last time it was
called.
However, "aa + (num_intervals * .1)" might exceed the constraint value of 1.0.
Thus, for four different values, I'm looking at ugly IF/ELSE checks to clip
those values back to 1.0. This is expected to be a large table and I would
really like have this code be simpler. Below is the start of my function.
What could I insert into the LOOP to make this simple and correct?
CREATE FUNCTION event_update_percentages() RETURNS void AS $$
DECLARE
last_update TIMESTAMP := (SELECT update_percentages FROM event_manager);
-- 5 minute intervals (60 seconds * 5)
num_intervals INTEGER := (SELECT EXTRACT (EPOCH FROM now() -
last_update )::int/300);
item some_table%ROWTYPE;
BEGIN
IF num_intervals > 0 THEN
FOR item IN SELECT * FROM some_table WHERE (
aa < 1 OR bb < 1 OR cc < 1 OR dd < 1
)
LOOP
-- ???
END LOOP;
UPDATE event_manager SET update_percentages = now();
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
Better yet, am I approaching this entirely the wrong way? If there is a
simpler solution to gradually increase those variables over time, I'd welcome
it.
If this is not clear, please let me know and I can try to explain more.
Cheers,
Ovid
--
Buy the book - http://www.oreilly.com/catalog/perlhks/
Tech blog - http://blogs.perl.org/users/ovid/
Twitter - http://twitter.com/OvidPerl
Official Perl 6 Wiki - http://www.perlfoundation.org/perl6
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
