From c0a88abd9b7efd429ec88f59f3bacc630493603d Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Thu, 3 Aug 2017 12:51:30 +0200 Subject: [PATCH] map some json fileds to columns with trigger --- create.sql | 20 +++++++++++++++++--- 1 file changed, 17 insertions(+), 3 deletions(-) diff --git a/create.sql b/create.sql index 3f34253..f69b912 100644 --- 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(); + + -- 2.20.1