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
46
47
48
49
50
51
52
53
54
55
56
57
|
-- createlang pgpgsql ebus
CREATE TABLE value_cache ( LIKE value );
DELETE FROM value_cache;
-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$$ LANGUAGE SQL STABLE;
-- And then wrap an aggreagate around it
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement
);
DROP FUNCTION value_insert_trigger_func() CASCADE;
--CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
-- BEGIN
-- DELETE FROM value_cache
-- WHERE date_trunc('hour', timestamp) =
-- (SELECT date_trunc('hour', max(timestamp)) FROM value)
-- AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value));
--
-- INSERT INTO value_cache
-- SELECT
-- COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq
-- ,date_trunc('hour', timestamp) AS "timestamp"
-- ,sensor_id AS "sensor_id"
-- ,type AS "type"
-- ,AVG(value_float) AS "value_float"
-- ,AVG(value_int) AS "value_int"
-- ,first(value_string) AS "value_string"
-- FROM value
-- WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value)
-- AND sensor_id = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value))
-- GROUP BY type, sensor_id, date_trunc('hour', timestamp);
-- RETURN NULL;
-- END;
--$$ LANGUAGE plpgsql;
CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
BEGIN
NOTIFY evt_ebus_value_insert;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
-- DROP TRIGGER value_insert_trigger ON value;
CREATE TRIGGER value_insert_trigger
AFTER INSERT ON value FOR EACH STATEMENT EXECUTE PROCEDURE value_insert_trigger_func();
|
