diff options
| -rw-r--r-- | sql/trigger.sql | 44 |
1 files changed, 25 insertions, 19 deletions
diff --git a/sql/trigger.sql b/sql/trigger.sql index ce91f61..2191ca0 100644 --- a/sql/trigger.sql +++ b/sql/trigger.sql @@ -18,27 +18,33 @@ CREATE AGGREGATE public.first ( DROP FUNCTION value_insert_trigger_func() CASCADE; +--CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$ +-- BEGIN +-- DELETE FROM value_cache +-- WHERE date_trunc('hour', timestamp) = +-- (SELECT date_trunc('hour', max(timestamp)) FROM value) +-- AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value)); +-- +-- INSERT INTO value_cache +-- SELECT +-- COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq +-- ,date_trunc('hour', timestamp) AS "timestamp" +-- ,sensor_id AS "sensor_id" +-- ,type AS "type" +-- ,AVG(value_float) AS "value_float" +-- ,AVG(value_int) AS "value_int" +-- ,first(value_string) AS "value_string" +-- FROM value +-- WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value) +-- AND sensor_id = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value)) +-- GROUP BY type, sensor_id, date_trunc('hour', timestamp); +-- RETURN NULL; +-- END; +--$$ LANGUAGE plpgsql; + + CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$ BEGIN - DELETE FROM value_cache - WHERE date_trunc('hour', timestamp) = - (SELECT date_trunc('hour', max(timestamp)) FROM value) - AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value)); - - INSERT INTO value_cache - SELECT - COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq - ,date_trunc('hour', timestamp) AS "timestamp" - ,sensor_id AS "sensor_id" - ,type AS "type" - ,AVG(value_float) AS "value_float" - ,AVG(value_int) AS "value_int" - ,first(value_string) AS "value_string" - FROM value - WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value) - AND sensor_id = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value)) - GROUP BY type, sensor_id, date_trunc('hour', timestamp); - NOTIFY evt_ebus_value_insert; RETURN NULL; |