當數據填充另一個時更新列上的時間戳

一般來說，我是 Postgresql 和 SQL 的新手。我有一張看起來像這樣的桌子

id (primary key)|temperature|temperature_timestamp|humidity|humidity_timestamp|...|parameter|parameter_timestamp 

我希望在對特定參數執行“更新”時自動更新特定“參數”_timestamp 的時間戳。

例如：當我更新特定行中的溫度時，該行上相應的 temperature_timestamp 應該更新

我目前正在關注此連結https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/並且能夠更新一列如何將其擴展到我的整個表？

我的觸發器功能如下所示：

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
   NEW.temp_lastupdated = now();
RETURN NEW;
END;
$$ language 'plpgsql'; 

我的觸發器看起來像這樣

CREATE TRIGGER update_timestamp
BEFORE UPDATE
ON sensor_data
FOR EACH ROW EXECUTE PROCEDURE  update_timestamp();

就我的偏好而言，觸發器在這裡的工作是錯誤的工具。試圖猜測應用程序是否真的意味著更新某個值是一場失敗的遊戲。讓業務邏輯做它被告知的事情，如果它更新值“ a”，那麼您作為管理員要求它也更新“ a_timestamp”同一事務的一部分。

例如：如果您修改應用程式碼的能力有限，但您可能需要 json 有效負載，您可以使用如下模型（此處為完整的 dbfiddle）。

給定一個表格…

create table foo (
   id serial primary key,
   a int,
   b int,
   c int,
   a_tstz timestamptz not null default now(),
   b_tstz timestamptz not null default now(),
   c_tstz timestamptz not null default now()
);
insert into foo (a,b,c)
values
(1,2,3),
(4,5,6);

…和形式的過程…

create procedure bar (payload jsonb)
language sql
as $$
   with j as (
      select payload as p
   ), cte as (
       select 
           (p->>'id')::int as id,
           case when p ? 'a' then (p->>'a')::int else f.a end as a,
           case when p ? 'b' then (p->>'b')::int else f.b end as b,
           case when p ? 'c' then (p->>'c')::int else f.c end as c,
           case when p ? 'a' then now()::timestamptz else f.a_tstz end as a_tstz,
           case when p ? 'b' then now()::timestamptz else f.b_tstz end as b_tstz,
           case when p ? 'c' then now()::timestamptz else f.c_tstz end as c_tstz
       from foo f
       join j on (j.p->>'id')::int = f.id 
   )
   update foo as f set
       a = c.a,
       b = c.b,
       c = c.c,
       a_tstz = c.a_tstz,
       b_tstz = c.b_tstz,
       c_tstz = c.c_tstz
   from cte as c
   where c.id = f.id;
$$

…你可以call bar('{"id":1,"a":1,"c":null}'::jsonb);。這將向您展示 json 有效負載中指定的屬性與它們的配對時間戳一起更新，而未指定的屬性不會更新。即使指定屬性也是如此

1. 更新為與之前相同的值
2. 移動到未知NULL狀態

---

在使用有效 jsonb負載的過程之外發生的更新仍然需要您“手動”更新配對的時間戳。proc 僅展示您可以推斷出哪些時間戳需要更新。值得注意的是，這裡寫的這種方法可能有一堆你想要 QA 的邊緣案例錯誤。從我的頭頂上…

1. 格式錯誤的有效負載 - 未id提供、a多次提供、提供無效數據等…
2. 多個有效載荷 - 我只測試了一個有效的有效載荷。如果您同時提供多個有效負載，您希望發生什麼。
3. upsert 邏輯 - 給定的 proc 只更新值。您將如何檢測到沒有發生更新並根據需要插入？

可能還值得注意的是，要求您自己通過程式碼更新數據/時間戳對允許您在不禁用觸發器的情況下對錶中的數據進行帶外更新，這些數據不會對時間戳進行處理。雖然您現在可能會說“我總是希望更新時間戳。”，但根據我的經驗，將自己描繪成這樣的角落是一個值得商榷的選擇。換句話說：“這不是一個錯誤，它是一個特性。 ”

您可以在單個觸發器中檢查哪些參數發生了變化：

create or replace function trg_sensor_data()
returns trigger as 
$$
begin

 if NEW.p1 <> OLD.p1 and NEW.p1 IS NOT NULL then
     NEW.p1_tm = now();
 end if;

 if NEW.p2 <> OLD.p2 and NEW.p2 IS NOT NULL  then
     NEW.p2_tm = now();
 end if;

 return NEW;

end;
$$
language 'plpgsql';

update sensor_data set p1 = 100;

select * from sensor_data;

1 行受影響

編號 | p1 | p1_tm | p2 | p2_tm 
-: | --: | :------------------------- | -: | :------------------
 1 | 100 | 2020-03-17 15:18:36.220044 | 23 | 2020-01-01 11:00:00

update sensor_data set p2 = 200;

select * from sensor_data;

1 行受影響

編號 | p1 | p1_tm | p2 | p2_tm 
-: | --: | :------------------------- | --: | :-------------------------
 1 | 100 | 2020-03-17 15:18:36.220044 | 200 | 2020-03-17 15:18:36.223565

db<>在這裡擺弄

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