cache

1 files changed, 44 insertions, 0 deletions

diff --git a/populate_cache.sql b/populate_cache.sql
new file mode 100644
index 0000000..0af4b36
--- /dev/null
+++ b/populate_cache.sql
@@ -0,0 +1,44 @@
+drop table value_cache cascade;
+
+CREATE TABLE value_cache (
+value_int integer,
+timestamp timestamp,
+sensor_id integer
+);
+
+create or replace view vi_value_cache as 
+  SELECT timestamp, sensor_id, value_int, 'CACHE'
+    FROM value_cache
+UNION
+  SELECT date_trunc('hour', timestamp), sensor_id, avg(value_int), 'LIVE'
+    FROM value
+   WHERE timestamp > coalesce( (select max(timestamp) from value_cache), now() - 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();
+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 = now() - interval '3 days';
+      RAISE NOTICE 'last update set to %', last_update;
+    end if;
+
+    delete from value_cache where timestamp >= last_update;
+
+    insert into value_cache 
+        select 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;