use json from mqtt and store it in postgresql

diff --git a/create.sql b/create.sql
new file mode 100644 (file)
index 0000000..094a297
--- /dev/null
+++ b/create.sql
@@ -0,0 +1,24 @@
+drop table eg5120;
+create table eg5120 (
+       time timestamp without time zone default now(),
+       mac text,
+       addr text,
+       "nodeId" int,
+       json jsonb,
+       _id serial
+);
+
+CREATE OR REPLACE FUNCTION json_fn()
+  RETURNS TRIGGER AS
+$func$
+BEGIN
+   NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
+   RETURN NEW;
+END
+$func$ LANGUAGE plpgsql;
+
+CREATE TRIGGER json_trigger
+BEFORE INSERT OR UPDATE ON eg5120 FOR EACH ROW
+EXECUTE PROCEDURE json_fn();
+
+

diff --git a/mqtt-pgsql.sh b/mqtt-pgsql.sh
new file mode 100755 (executable)
index 0000000..e25d39f
--- /dev/null
+++ b/mqtt-pgsql.sh
@@ -0,0 +1,8 @@
+#!/bin/sh -e
+
+mosquitto_sub -h localhost -t eg5120 | while read json ; do
+       date=$( date +%Y-%m-%dT%H:%M:%S )
+       ecno -n $date ' '
+       echo $json | tee data/$date.json | tee /dev/shm/last-eg5120.json
+       psql --quiet -c "copy eg5120 (json) from '/dev/shm/last-eg5120.json'" eg5120
+done