move old stuff away

1 files changed, 45 insertions, 0 deletions

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;