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
goingto be called from a cron job every 5 minutes to update "aa, bb, cc and dd" percentages every 5 minutes. The amount
ofthe 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
lookingat ugly IF/ELSE checks to clip those values back to 1.0. This is expected to be a large table and I would
reallylike have this code be simpler. Below is the start of my function. What could I insert into the LOOP to make
thissimple 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
variablesover 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