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->>'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:

Actually, this is all you need:

NEW := jsonb_populate_record(NEW, NEW.json);

The manual (since pg 13):

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

Expands the top-level JSON object to a row having the composite type
of the base argument. The JSON object is scanned for fields whose
names match column names of the output row type, and their values are
inserted into those columns of the output. (Fields that do not
correspond to any output column name are ignored.) In typical use, the
value of base is just NULL, which means that any output columns that
do not match any object field will be filled with nulls. However, if
base isn't NULL then the values it contains will be used for
unmatched columns.

That the row provided as first argument retains all values not overwritten (no matching key in the json value) was undocumented before Postgres 13.

If "undocumented" is too uncertain for you, use the hstore operator #= doing exactly the same.

NEW := (NEW #= hstore(jsonb_populate_record(NEW, NEW.json)));

The hstore module should be installed in most systems anyway. Instructions:

• Assign to NEW by key in a Postgres trigger

Either solution can be derived from:

• How to set value of composite variable field using dynamic SQL

One thing to note - you wrote:

Setting the column to NULL if the corresponding json field does not
exist is fine.

This retains all values with no matching key in the JSON value, which should be even better.
Function code

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW := jsonb_populate_record(NEW, NEW.json); -- or hstore alternative
   RETURN NEW;
END
$func$;

Everything else in your setup looks right, just add a PK to testy:

CREATE TABLE testy (
    id   int   PRIMARY KEY REFERENCES testy_index
  , data jsonb NOT NULL
);

Tested in pg 9.4 and it works for me as advertised.
Set other columns to NULL

As per comment:

CREATE OR REPLACE FUNCTION json_fn()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
DECLARE
   _j jsonb := NEW.json;  -- remember the json value
BEGIN
   NEW := jsonb_populate_record(NULL::testy, _j);
   NEW.json := _j;   -- reassign
   RETURN NEW;
END
$func$;

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 use json as column name, since it's a basic data type name and that can get confusing.

StackExchange Database Administrators Q#105721, answer score: 4