插入類型的觸發器訪問欄位之前的Postgres

在數據庫中，我有一個類型和一個表，其中一個行就是該類型，例如：

CREATE TYPE state (last_active timestamp, ...)
CREATE TABLE devices ( state state, ... )

對於來自設備的任何互動，我想更新last_active狀態中的欄位。我添加了以下BEFORE UPDATE觸發器：

create or replace function update_last_active_on_position_update_hook()
   returns trigger
   language plpgsql
as $$
declare
   new_state state;
begin
   if new.state IS NOT NULL then
       new.state.last_active := now();
   end if;
   RETURN new;
end
$$;

當我這樣做時，我收到以下錯誤： [42601] ERROR: "new.state.last_active" is not a known variable

但是如果我用 type 聲明一個變數state，複製new.state到該變數，更新欄位並複制回來；有用。工作版本：

create or replace function update_last_active_on_position_update_hook()
   returns trigger
   language plpgsql
as $$
declare
   new_state state;
begin
   if new.state IS NOT NULL then
       new_state := new.state;
       new_state.last_active := now();
       new.state := new_state;
   end if;
   RETURN new;
end
$$;

但我不喜歡複製-更新-複製。還有其他解決方案嗎？

我試圖進入new.stateparens，(new.state).last_active但它給了我一個syntax error near "("

我可以用 Postgres 12 中的片段 1 重現問題：

db<>在這裡擺弄

這顯然是 PL/pgSQL 的一個缺點。但無需報告，因為它已在 Postgres 14 中修復。看：

db<>在這裡擺弄

發行說明：

• 改進 PL/pgSQL 的表達式和賦值解析 (Tom Lane)

此更改允許分配給數組切片和嵌套記錄欄位。

特別是，Tom Lane 評論道：

0005 添加文件和測試案例。它還修復了 plpgsql 解析器在分配給記錄欄位的子欄位時存在的幾個預先存在的問題，這是我在進行測試時發現的。

解決方法

但我不喜歡複製-更新-複製。還有其他解決方案嗎？

這是 Postgres 13 或更早版本的解決方法：

CREATE OR REPLACE FUNCTION update_last_active_on_position_update_hook()
 RETURNS trigger
 LANGUAGE plpgsql AS
$func$
DECLARE
  _state state;
BEGIN
  NEW.state := json_populate_record (NEW.state, json_build_object('last_active', now()));  -- !!
  RETURN NEW;
END
$func$;

db<>在這裡擺弄

看：

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

更好的？快點？沒有把握。無論如何都要短一點。

在此過程中，我將條件移動到WHEN觸發器定義中的一個子句中。這樣，觸發函式甚至只在需要時才被呼叫。那更便宜：

CREATE TRIGGER foo
BEFORE UPDATE ON devices
FOR EACH ROW
WHEN (NEW.state IS NOT NULL)
EXECUTE FUNCTION update_last_active_on_position_update_hook();

看：

• 是否可以動態循環遍歷表的列？

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