From d440c0c717df484adf3cacccfc1bca96a8396c6e Mon Sep 17 00:00:00 2001 From: Random Hacker Date: Wed, 7 Dec 2011 23:30:21 +0100 Subject: NOTIFY event on insert trigger --- sql/trigger.sql | 44 +++++++++++++++++++++++++------------------- 1 file changed, 25 insertions(+), 19 deletions(-) (limited to 'sql/trigger.sql') 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; -- cgit v1.2.1