From a700fe3774b5a9e37a7214bcfcc1ea1d155f6769 Mon Sep 17 00:00:00 2001
From: Random Hacker <random_hacker@xapek.org>
Date: Sun, 29 Aug 2010 00:12:47 +0200
Subject: add trigger

---
 trigger.sql | 49 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 49 insertions(+)
 create mode 100644 trigger.sql

(limited to 'trigger.sql')

diff --git a/trigger.sql b/trigger.sql
new file mode 100644
index 0000000..0c06a99
--- /dev/null
+++ b/trigger.sql
@@ -0,0 +1,49 @@
+-- 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;
+
+-- 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();
-- 
cgit v1.2.1