Unnamed

CREATE OR REPLACE FUNCTION check_com_meter() RETURNS TRIGGER AS '
  DECLARE

    times TIMESTAMP WITHOUT TIME ZONE;
    temp_time TIMESTAMP;
    time_interval INTEGER;
    rows INTEGER;
    temp INTEGER;
    month1 INTEGER;
    month2 INTEGER;
    year1 INTEGER;
    year2 INTEGER;
    reading double precision;
    average double precision;
    makeup_reading double precision;

  BEGIN

    temp_time := now();
    temp_time := date_trunc(''second'', temp_time);
    month1 := EXTRACT(MONTH FROM temp_time);
    month2 := EXTRACT(MONTH FROM NEW.timestamp);
    year1 := EXTRACT(YEAR FROM temp_time);
    year2 := EXTRACT(YEAR FROM NEW.timestamp);

    IF month1 != month2 THEN
        temp := EXTRACT(EPOCH FROM temp_time);
    ELSE
        IF year1 != year2 THEN
                temp := EXTRACT(EPOCH FROM temp_time);
        ELSE
                temp := EXTRACT(EPOCH FROM NEW.timestamp);
        END IF;
    END IF;

--temp := EXTRACT(EPOCH FROM NEW.timestamp);
    IF temp % 900 != 0 THEN
        temp := temp / 900;
        temp := temp * 900;
        NEW.timestamp := ''epoch''::timestamptz + temp * interval ''1 second'';
    END IF;


    SELECT INTO times, reading timestamp, readings FROM com_meter WHERE amrid = NEW.amrid
                AND channel = NEW.channel ORDER BY timestamp DESC;

    IF NOT FOUND THEN
        RETURN NEW;
    ELSE

        temp := EXTRACT(EPOCH FROM times);
        IF temp % 900 != 0 THEN
                temp := temp / 900;
                temp := temp * 900;
                times := ''epoch''::timestamptz + temp * interval ''1 second'';
        END IF;

        time_interval := EXTRACT(EPOCH FROM NEW.timestamp) - EXTRACT(EPOCH FROM times);
        rows := time_interval / 900;
        IF time_interval <= 0 THEN
                return NULL;
        ELSE
                IF rows > 1 THEN
                        average := (NEW.readings - reading)/rows;
                        FOR i IN 1 .. rows-1 LOOP
                                makeup_reading := reading + average*i;
                                times := times + interval ''900 second'';
                                INSERT INTO com_meter(amrid,timestamp,demand_int,curr_demand,
                                        max_demand,max_demand_time,units,readings,channel,tri_add)
                                        VALUES(NEW.amrid, times, NEW.demand_int, NEW.curr_demand,NEW.max_demand,
                                        NEW.max_demand_time, NEW.units,makeup_reading,NEW.channel,1);
                        END LOOP;
                END IF;
        END IF;
        RETURN NEW;
    END IF;
  END;
' LANGUAGE 'plpgsql';




CREATE TRIGGER check_com_meter
             BEFORE INSERT
             ON com_meter FOR EACH ROW
             EXECUTE PROCEDURE check_com_meter();