NOTIFY event on insert trigger

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;