diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/populate_cache.sql | 12 | ||||
| -rw-r--r-- | sql/trigger.sql | 2 |
2 files changed, 8 insertions, 6 deletions
diff --git a/sql/populate_cache.sql b/sql/populate_cache.sql index f302607..2733cbb 100644 --- a/sql/populate_cache.sql +++ b/sql/populate_cache.sql @@ -1,9 +1,9 @@ drop table value_cache cascade; CREATE TABLE value_cache ( -value_real integer, -timestamp timestamp, -sensor_id integer + value_real integer, + timestamp timestamp, + sensor_id integer ); create or replace view vi_value_cache as @@ -13,14 +13,14 @@ UNION SELECT date_trunc('hour', timestamp), sensor_id, COALESCE(avg(value_int),avg(value_float)), 'LIVE' FROM value WHERE date_trunc('hour', timestamp) > coalesce((select max(timestamp) from value_cache), - now() - interval '2 days') + CURRENT_TIMESTAMP - interval '2 days') GROUP BY date_trunc('hour', timestamp), sensor_id; ; CREATE OR REPLACE FUNCTION value_cache_aktualisieren() RETURNS timestamp AS $value_cache_aktualisieren$ DECLARE - last_update timestamp = now(); + last_update timestamp = CURRENT_TIMESTAMP; BEGIN select max(date_trunc('hour'::text, "timestamp")) into last_update @@ -28,7 +28,7 @@ BEGIN RAISE NOTICE 'last update=%', last_update; if last_update is NULL then - last_update = now() - interval '20 days'; + last_update = CURRENT_TIMESTAMP - interval '20 days'; RAISE NOTICE 'last update set to %', last_update; end if; diff --git a/sql/trigger.sql b/sql/trigger.sql index fb60617..ce91f61 100644 --- a/sql/trigger.sql +++ b/sql/trigger.sql @@ -39,6 +39,8 @@ CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$ 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; END; $$ LANGUAGE plpgsql; |