projects
/
rtl433-sensors
/ commitdiff
commit
grep
author
committer
pickaxe
?
search:
re
summary
|
shortlog
|
log
|
commit
| commitdiff |
tree
raw
|
patch
|
inline
| side by side (parent:
4c2030a
)
map some json fileds to columns with trigger
author
Dobrica Pavlinusic
<dpavlin@rot13.org>
Thu, 3 Aug 2017 10:51:30 +0000
(12:51 +0200)
committer
Dobrica Pavlinusic
<dpavlin@rot13.org>
Thu, 3 Aug 2017 10:51:30 +0000
(12:51 +0200)
create.sql
patch
|
blob
|
history
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,
create table rtl433 (
time timestamp without time zone,
model text,
- id integer
not null
,
+ id integer,
json jsonb,
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();
+
+