Use trigger to synchronize columns with fields in json column on insert or update 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-&gt;&gt;'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-&gt;&gt;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();
Dynamic fields are notoriously difficult in plpgsql. In particular there's no way we can write new.variable := something where variable stands for a column name.

See How to set value of composite variable field using dynamic SQL for ways that involve querying the catalog at runtime.

Personally, I'd suggest a simpler solution with the plv8 language.

CREATE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for (var key in obj) {
    NEW[key]=obj[key];
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();




Otherwise, you may consider if your implementation is not simplified by moving the json field out of this table and into a separate table, assuming it's still compatible with your general intention.