diff options
| -rw-r--r-- | trigger.sql | 30 |
1 files changed, 15 insertions, 15 deletions
diff --git a/trigger.sql b/trigger.sql index 0c06a99..fb60617 100644 --- a/trigger.sql +++ b/trigger.sql @@ -20,26 +20,26 @@ 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) + 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 + 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" + 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); + 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; + RETURN NULL; END; $$ LANGUAGE plpgsql; |