Search Postgresql Archives

Clipping values

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux