map some json fileds to columns with trigger

diff --git a/create.sql b/create.sql
index 3f34253..f69b912 100644 (file)
--- a/create.sql
+++ b/create.sql
@@ -1,9 +1,23 @@
+drop table rtl433;
 create table rtl433 (
        time timestamp without time zone,
        model text,
-       id integer not null,
+       id integer,
        json jsonb,
-       primary key(time,id)
+       _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 rtl433 FOR EACH ROW
+EXECUTE PROCEDURE json_fn();
+
+