From 833113950c84a096286ddb2eb663190e3a317a32 Mon Sep 17 00:00:00 2001
From: Dobrica Pavlinusic <dpavlin@rot13.org>
Date: Sun, 28 Oct 2018 12:45:59 +0100
Subject: [PATCH] record tele topic and content

---
 mqtt-tele-pgsql.service | 10 ++++++++++
 mqtt-tele-pgsql.sh      |  7 +++++++
 tele.sql                | 23 +++++++++++++++++++++++
 3 files changed, 40 insertions(+)
 create mode 100644 mqtt-tele-pgsql.service
 create mode 100755 mqtt-tele-pgsql.sh
 create mode 100644 tele.sql

diff --git a/mqtt-tele-pgsql.service b/mqtt-tele-pgsql.service
new file mode 100644
index 0000000..ce3c70d
--- /dev/null
+++ b/mqtt-tele-pgsql.service
@@ -0,0 +1,10 @@
+[Unit]
+Description=mqtt-tele-pgsql
+
+[Service]
+User=dpavlin
+ExecStart=/klin/rtl433-sensors/mqtt-tele-pgsql.sh
+Restart=on-failure
+
+[Install]
+WantedBy=multi-user.target
diff --git a/mqtt-tele-pgsql.sh b/mqtt-tele-pgsql.sh
new file mode 100755
index 0000000..e276ef6
--- /dev/null
+++ b/mqtt-tele-pgsql.sh
@@ -0,0 +1,7 @@
+#!/bin/sh -x
+
+mosquitto_sub -h 10.13.37.5 -t 'tele/#' -v | while read topic json ; do
+	echo $json > /dev/shm/tele.json
+#	psql -c "copy tele (json) from '/dev/shm/tele.json'" rot13
+	psql -c "insert into tele (topic,json) values ('$topic','$json');" rot13
+done
diff --git a/tele.sql b/tele.sql
new file mode 100644
index 0000000..30a7a1d
--- /dev/null
+++ b/tele.sql
@@ -0,0 +1,23 @@
+drop table tele;
+create table tele (
+	_id serial,
+	time timestamp without time zone default now(),
+	topic text not null,
+	json jsonb
+);
+
+CREATE OR REPLACE FUNCTION json_tele_fn()
+  RETURNS TRIGGER AS
+$func$
+BEGIN
+   NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
+   NEW.time = NEW.json->'Time';
+   RETURN NEW;
+END
+$func$ LANGUAGE plpgsql;
+
+CREATE TRIGGER json_tele_trigger
+BEFORE INSERT OR UPDATE ON tele FOR EACH ROW
+EXECUTE PROCEDURE json_tele_fn();
+
+
-- 
2.20.1