僅給定欄位名稱，如何訪問 NEW 或 OLD 欄位？

我正在寫一個驗證觸發器。觸發器必須驗證數組的總和是否等於另一個欄位。由於我有很多這種驗證的實例，我想編寫一個過程並創建多個觸發器，每個觸發器都有一組不同的欄位要檢查。

例如，我有以下架構：

CREATE TABLE daily_reports(
    start_on date
  , show_id uuid
  , primary key(start_on, show_id)

    -- _graph are hourly values, while _count is total for the report
  , impressions_count bigint not null
  , impressions_graph bigint[] not null

  -- interactions_count, interactions_graph
  -- twitter_interactions_count, twitter_interactions_graph
);

驗證必須確認impressions_count = sum(impressions_graph).

我被卡住了，因為我不知道如何NEW從 plpgsql 中動態訪問欄位：

CREATE FUNCTION validate_sum_of_array_equals_other() RETURNS TRIGGER AS $$
DECLARE
 total bigint;
 array_sum bigint;
BEGIN
 -- TG_NARGS = 2
 -- TG_ARGV[0] = 'impressions_count'
 -- TG_ARGV[1] = 'impressions_graph'

 -- How to access impressions_count and impressions_graph from NEW?
 RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_daily_reports_impressions
ON daily_reports BEFORE INSERT OR UPDATE
FOR EACH ROW EXECUTE
 validate_sum_of_array_equals_other('impressions_count', 'impressions_graph');

我嘗試通過做來執行動態命令EXECUTE 'SELECT $1 FROM NEW' INTO total USING TG_ARGV[0]，但是 PL/PGsql 抱怨 NEW 是一個未知的關係。

我專門針對 PostgreSQL 9.1。

實際上，由於NEW是一個定義良好的複合類型，您可以使用簡單明了的屬性表示法訪問任何列。SQL 本身不允許使用動態標識符（表或列名等）。但是您可以在 PL/pgSQL 函式中使用動態 SQL 。EXECUTE

展示

CREATE OR REPLACE FUNCTION trg_demo1()
 RETURNS TRIGGER AS
$func$
DECLARE
  _col_value text;
  _col_name  text := quote_ident(TG_ARGV[0]);  -- escape identifier
BEGIN
  EXECUTE format('SELECT ($1).%s::text', _col_name)
  USING NEW
  INTO  _col_value;

  -- do something with _col_value ...

  RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value;

  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

演員表text是可選的。使用它，因為它普遍適用。如果您知道類型，則無需強制轉換即可工作…

使用format()with %s，因為此時標識符已被轉義。

否則，使用format()with%I來防止 SQL 注入。

或者，在 Postgres 9.3 或更高版本中，您可以將列轉換NEW為 JSON，to_json()並將列作為鍵訪問：

CREATE OR REPLACE FUNCTION trg_demo2()
 RETURNS TRIGGER AS
$func$
DECLARE
  _col_value text := to_json(NEW) ->> TG_ARGV[0];  -- no need to escape identifier
BEGIN
  RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value;
  RETURN NEW;
END
$func$ LANGUAGE plpgsql;

由於列名沒有串聯成 SQL 字元串，因此無法進行 SQL 注入，並且不需要對名稱進行轉義。

db<>fiddle here（使用EXCEPTION而不是NOTICE使效果可見）。

有關的：

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

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