在插入或更新時使用觸發器將列與 json 列中的欄位同步

我是一個數據庫/postgres 初學者，所以請耐心等待。

如果我有一張桌子，就像這樣。

CREATE TABLE testy (
   id INTEGER REFERENCES other_table,
   name varchar(128) PRIMARY KEY,
   json JSONB NOT NULL
);

我希望在插入或更新之前創建一個觸發器，它將設置列id和.name``json

因此，例如，如果testy包含以下內容UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1並被呼叫。

id | name | json
---+------+-----
1 | "jim"| {"id":1,"name":"jim"}

期望的結果是

id | name | json
---+------+-----
2 | "jim"| {"id":2,"name":"jim"}

我希望使它相當通用，因此不需要對列名進行硬編碼。如果相應的 json 欄位不存在，則將該列設置為 NULL 即可。到目前為止我有

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();

這不起作用，因為您不能靈活地使用 roow.column_name 。我試過玩 EXECUTE 沒有成功，儘管我可能只是做得不對。

任何幫助將不勝感激！！

編輯：這樣做的動機是可以將外鍵約束放置在表現為 json 欄位的東西上。

編輯：plv8 很棒。使用了@Daniel Vérité 答案的修改版本，以便在 json 中未表示為欄位的列將被清空

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();

實際上，這就是您所需要的：

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

根據文件：

jsonb_populate_record(base anyelement, from_json jsonb)

將對象展開from_json到其列與基定義的記錄類型匹配的行中（請參閱下面的註釋）。

未記錄的內容：作為第一個參數提供的行保留了所有未被覆蓋的值（json 值中沒有匹配的鍵）。我認為沒有理由改變這種情況，但除非它被記錄在案，否則你不能完全依賴它。

需要注意的一件事 - 你寫道：

如果相應的 json 欄位不存在，則將該列設置為 NULL 即可。

這會保留JSON 值中沒有匹配鍵的所有值，這應該會更好。

如果“未記錄”對您來說太不確定，請使用完全相同的hstore操作符。#=

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

無論如何，該hstore模組應該安裝在大多數係統中。指示：

• 在 Postgres 觸發器中按鍵分配給 NEW

兩種解決方案都可以從Daniel 已經引用的我的回答中得出：

• 如何使用動態 SQL 設置複合變數欄位的值

功能碼

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;

您設置中的其他所有內容看起來都正確，只需將 PK 添加到testy：

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

在 pg 9.4 中進行了測試，它對我來說就像宣傳的那樣有效。我懷疑 PLv8 功能能否與性能和簡單性相媲美。

將其他列設置為 NULL

根據評論：

CREATE OR REPLACE FUNCTION json_fn()
 RETURNS TRIGGER 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$ LANGUAGE plpgsql;

顯然，您需要確保列名或您的jsonb列不會作為鍵名出現在 JSON 值中。而且我不會json用作列名，因為它是一個數據類型名稱，可能會讓人感到困惑。

引用自：https://dba.stackexchange.com/questions/105721