From 862282ce99760832d3e9e5b4b1171b861105e004 Mon Sep 17 00:00:00 2001 From: Ebus-at-dockstar Date: Mon, 25 Mar 2013 10:24:28 +0100 Subject: move old stuff away --- heap/sql/populate_cache.sql | 45 +++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 45 insertions(+) create mode 100644 heap/sql/populate_cache.sql (limited to 'heap/sql/populate_cache.sql') diff --git a/heap/sql/populate_cache.sql b/heap/sql/populate_cache.sql new file mode 100644 index 0000000..2733cbb --- /dev/null +++ b/heap/sql/populate_cache.sql @@ -0,0 +1,45 @@ +drop table value_cache cascade; + +CREATE TABLE value_cache ( + value_real integer, + timestamp timestamp, + sensor_id integer +); + +create or replace view vi_value_cache as + SELECT timestamp, sensor_id, value_real, 'CACHE' + FROM value_cache +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), + 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 = CURRENT_TIMESTAMP; +BEGIN + select max(date_trunc('hour'::text, "timestamp")) + into last_update + from value_cache; + + RAISE NOTICE 'last update=%', last_update; + if last_update is NULL then + last_update = CURRENT_TIMESTAMP - interval '20 days'; + RAISE NOTICE 'last update set to %', last_update; + end if; + + delete from value_cache where timestamp >= last_update; + + insert into value_cache + select COALESCE(avg(value_float),avg(value_int)), date_trunc('hour', timestamp), sensor_id + from value + where date_trunc('hour', timestamp) > last_update + group by date_trunc('hour', timestamp), sensor_id; + + RETURN last_update; +END; +$value_cache_aktualisieren$ LANGUAGE plpgsql; -- cgit v1.2.1