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;