--- /dev/null
+[Unit]
+Description=mqtt-tele-pgsql
+
+[Service]
+User=dpavlin
+ExecStart=/klin/rtl433-sensors/mqtt-tele-pgsql.sh
+Restart=on-failure
+
+[Install]
+WantedBy=multi-user.target
--- /dev/null
+#!/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
--- /dev/null
+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();
+
+