From 4f6f18fc3ad0f0a62f0db20e3e6e8d5a2bb190bf Mon Sep 17 00:00:00 2001 From: Random Hacker Date: Mon, 5 Dec 2011 22:47:24 +0100 Subject: use current_timestamp; add notify --- sql/populate_cache.sql | 12 ++++++------ sql/trigger.sql | 2 ++ 2 files changed, 8 insertions(+), 6 deletions(-) (limited to 'sql') 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; -- cgit v1.2.1