I'm a bit of a database/postgres beginner,so bear with me.
If I have a table, something like this.
CREATE TABLE testy (
id INTEGER REFERENCES other_table,
name varchar(128) PRIMARY KEY,
json JSONB NOT NULL
);
I'm looking to create a trigger before insert or update that will set the columns id
and name
to the values of fields with the same names in json
.
So for example if testy
contained the below and UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1
was called.
id | name | json
---+------+-----
1 | "jim"| {"id":1,"name":"jim"}
The desired result would be
id | name | json
---+------+-----
2 | "jim"| {"id":2,"name":"jim"}
I wish to make this fairly generic so the column names do not need to be hard coded. Setting the column to NULL if the corresponding json field does not exist is fine.
So far I have
CREATE TABLE testy_index (
id INTEGER PRIMARY KEY
);
INSERT INTO testy_index VALUES (1);
INSERT INTO testy_index VALUES (2);
INSERT INTO testy_index VALUES (3);
CREATE TABLE testy (
id INTEGER REFERENCES testy_index,
json JSONB NOT NULL
);
CREATE UNIQUE INDEX testy_id ON testy((json->>'id'));
CREATE OR REPLACE FUNCTION json_fn() RETURNS TRIGGER AS $testy$
DECLARE
roow RECORD;
BEGIN
FOR roow IN
SELECT column_name FROM information_schema.columns WHERE table_name = 'testy'
LOOP
NEW.roow.column_name = (NEW.json->>roow.column_name);
END LOOP;
END;
$testy$ LANGUAGE plpgsql;
CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();
Which doesn't work as you can't use roow.column_name that flexibly. I've tried playing around with EXECUTE with no success, although it's possible I'm just not doing it right.
Any help would be greatly appreciated!!
EDIT: The motivation for this is so that foreign key constraints can be placed on something that behaves as a json field.
EDIT: plv8 is great. Used a modified version of @Daniel Vérité 's answer so that columns not represented as fields in json will be nulled
CREATE OR REPLACE FUNCTION json_fn() RETURNS trigger AS
$$
var obj = JSON.parse(NEW.json);
for(var col in NEW){
if(col == 'json'){
continue;
}
if(col in obj){
NEW[col]=obj[col];
}else{
NEW[col]=null;
}
}
return NEW;
$$
LANGUAGE plv8;
CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();
Best Answer
Actually, this is all you need:
Per documentation:
What's not documented: The row provided as first argument retains all values that are not overwritten (no matching key in the json value). I see no reason why this should change, but you cannot fully rely on it unless it's documented.
One thing to note - you wrote:
This retains all values with no matching key in the JSON value, which should be even better.
If "undocumented" is too uncertain for you, use the
hstore
operator#=
doing exactly the same.The
hstore
module should be installed in most systems anyway. Instructions:Both solution can be derived from my answer that Daniel already referenced:
Function code
Everything else in your setup looks right, just add a PK to
testy
:Tested in pg 9.4 and it works for me as advertised. I doubt that the PLv8 function can rival performance and simplicity.
Set other columns to NULL
As per comment:
Obviously, you need to make sure that the column name or your
jsonb
column does not appear as key name in the JSON value. And i wouldn't usejson
as column name, since it's a data type name and that can get confusing.