schema.postgresql

CREATE OR REPLACE VIEW usage_minutely AS
SELECT stamp, 3600000.0::DOUBLE PRECISION * events / sum_ms AS wh, min_ms, max_ms, events
  FROM readings_minutely;

CREATE OR REPLACE VIEW usage_hourly AS
SELECT stamp, 3600000.0::DOUBLE PRECISION * events / sum_ms AS wh, min_ms, max_ms, events
  FROM readings_hourly;

CREATE OR REPLACE FUNCTION do_monthly_aggregate() RETURNS trigger AS
$BODY$
DECLARE
  as_date TIMESTAMP WITH TIME ZONE;
  hour BIGINT;
  minute BIGINT;
BEGIN
  as_date := TIMESTAMP WITH TIME ZONE 'epoch' + trunc(NEW.stamp/1000) * INTERVAL '1 second';
  hour := 1000::BIGINT * extract(epoch from date_trunc('hour', as_date));
  minute := 1000::BIGINT * extract(epoch from date_trunc('minute', as_date));
  INSERT INTO readings_hourly (stamp, events, sum_ms, min_ms, max_ms)
  SELECT hour, 0, 0, NEW.ms, NEW.ms
   WHERE NOT EXISTS (SELECT 1 FROM readings_hourly R WHERE R.stamp=hour);
  UPDATE readings_hourly
     SET events = events + 1,
         sum_ms = sum_ms + NEW.ms,
         min_ms = LEAST(min_ms, NEW.ms),
         max_ms = GREATEST(max_ms, NEW.ms)
   WHERE stamp = hour;
  INSERT INTO readings_minutely (stamp, events, sum_ms, min_ms, max_ms)
  SELECT minute, 0, 0, NEW.ms, NEW.ms
   WHERE NOT EXISTS (SELECT 1 FROM readings_minutely R WHERE R.stamp=minute);
  UPDATE readings_minutely
     SET events = events + 1,
         sum_ms = sum_ms + NEW.ms,
         min_ms = LEAST(min_ms, NEW.ms),
         max_ms = GREATEST(max_ms, NEW.ms)
   WHERE stamp = minute;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER monthly_aggregate AFTER INSERT
    ON readings
   FOR EACH ROW
EXECUTE PROCEDURE do_monthly_aggregate();

CREATE OR REPLACE FUNCTION labibus_monthly_aggregate() RETURNS trigger AS
$BODY$
DECLARE
  as_date TIMESTAMP WITH TIME ZONE;
  hour BIGINT;
  minute BIGINT;
BEGIN
  as_date := TIMESTAMP WITH TIME ZONE 'epoch' + trunc(NEW.stamp/1000) * INTERVAL '1 second';
  hour := 1000::BIGINT * extract(epoch from date_trunc('hour', as_date));
  minute := 1000::BIGINT * extract(epoch from date_trunc('minute', as_date));
  INSERT INTO device_log_hourly (id, stamp, events, sum_value, sum_sqvalue, min_value, max_value)
  SELECT NEW.id, hour, 0, 0, 0, NEW.value, NEW.value
   WHERE NOT EXISTS (SELECT 1 FROM device_log_hourly R WHERE R.id = NEW.id AND R.stamp=hour);
  UPDATE device_log_hourly
     SET events = events + 1,
         sum_value = sum_value + NEW.value,
         sum_sqvalue = sum_sqvalue + NEW.value*NEW.value,
         min_value = LEAST(min_value, NEW.value),
         max_value = GREATEST(max_value, NEW.value)
   WHERE id = NEW.id AND stamp = hour;
  INSERT INTO device_log_minutely (id, stamp, events, sum_value, sum_sqvalue, min_value, max_value)
  SELECT NEW.id, minute, 0, 0, 0, NEW.value, NEW.value
   WHERE NOT EXISTS (SELECT 1 FROM device_log_minutely R WHERE R.id = NEW.id AND R.stamp=minute);
  UPDATE device_log_minutely
     SET events = events + 1,
         sum_value = sum_value + NEW.value,
         sum_sqvalue = sum_sqvalue + NEW.value*NEW.value,
         min_value = LEAST(min_value, NEW.value),
         max_value = GREATEST(max_value, NEW.value)
   WHERE id = NEW.id AND stamp = minute;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER monthly_aggregate AFTER INSERT
    ON device_log
   FOR EACH ROW
EXECUTE PROCEDURE labibus_monthly_aggregate();