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;