1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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;
|
