From 02f48f7a905973428fc7766cf92bb04614e1fdca Mon Sep 17 00:00:00 2001 From: yvesf Date: Sun, 24 Jul 2011 13:12:14 +0200 Subject: cleanup, tag version 0.3 --- populate_cache.sql | 45 --------------------------------------------- 1 file changed, 45 deletions(-) delete mode 100644 populate_cache.sql (limited to 'populate_cache.sql') diff --git a/populate_cache.sql b/populate_cache.sql deleted file mode 100644 index f302607..0000000 --- a/populate_cache.sql +++ /dev/null @@ -1,45 +0,0 @@ -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), - 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 '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