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
45
|
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),
CURRENT_TIMESTAMP - 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 = CURRENT_TIMESTAMP;
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 = CURRENT_TIMESTAMP - 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;
|
