PostgreSQL 觸發器用另一個表中的數據更新插入的行

我在 PostgreSQL-12 中有 2 個表，SENSORS（從感測器接收時間序列數據）和 LABELS（包含感測器標籤和元數據）。我正在嘗試創建一個 PostgreSQL 觸發器來更新感測器數據，因為它們被插入到 SENSORS 中。每行感測器數據都將使用 LABELS 中的相應標籤名稱進行更新。無法使行更新正常工作。範例如下所示：

樣本數據集

CREATE TABLE sensor (
  datavalue integer, 
  created timestamp,
  label_id integer,
  label_name text);

CREATE TABLE labels (
  id integer,
  label_id integer,
  label_name text);

Insert into labels values (1, 215, 'Home'), (2, 216, 'Shop'), (3, 217, 'Flat'), (4, 218, 'Street');
Insert into sensor values (67, '2020-09-02 08:40:07.354', 215),(67, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

觸發功能

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS 
$func$
BEGIN
UPDATE sensor 
   SET label_name = b.label_name 
   from labels b
   where new.label_id = b.label_id;
    RETURN NEW; 
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
AFTER INSERT OR UPDATE ON sensor
FOR EACH ROW EXECUTE PROCEDURE update_name();

測試行插入

Insert into sensor values (78, '2020-09-02 08:40:07.354', 215),(77, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

我有大量的行流入，並希望在插入每一行時對其進行更新。

非常感謝任何幫助。謝謝！

你很可能走錯了方向，但我對此只有直覺。

但是，您可以使用觸發器 - 也許它會起作用。但是，您似乎將在每個觸發器呼叫中進行大量更新，因為您正在sensor使用新標籤更新所有行，這似乎是錯誤的。

由於我不確定您的具體需求，我將通過這些更改以最適當的方式（我希望）推動您：

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$$
BEGIN
   IF TG_OP = 'INSERT' THEN
       INSERT INTO sensor(datavalue, created, label_id, label_name)
       SELECT NEW.datavalue, NEW.created, NEW.label_id, b.label_name
       from labels b
       where new.label_id = b.label_id;

   ELSIF TG_OP = 'UPDATE' THEN
       UPDATE sensor
       SET label_name = b.label_name
       from labels b
       where new.label_id = b.label_id;

   END IF;
   RETURN NULL;

END
$$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
BEFORE INSERT OR UPDATE ON sensor
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE update_name();

在這裡，您執行這些更改而不是（之前）插入或更新。插入將是正確的，需要調整或刪除更新，因為從您的描述中看起來您將只在此處進行插入。

請注意，這裡有建議的限制器https://dba.stackexchange.com/a/103661/213360。

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