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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to