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();
SELECT INTO
in theEXECUTE
statement (although not sure about that).NEW.thing = other_thing
. I want to do this so that I can effectively use json fields as keys.UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1
can easily be changed toUPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE (json->>'id') = 1
and it would use the index you have shown as created ..CREATE UNIQUE INDEX testy_id ON testy((json->>'id'));
I'm not sure why you need to look through the data dictionary for the column names, though - isn't that a defined fixed set? In your example you have two columnsid
andname
.. you could have two lines setting each column appropriately (instead of loop) - how many columns does your REAL table have?