傳遞參數到觸發函式以執行動態 SQL

我有這個由 Erwin 解決的程式碼：

Execute dynamic INSERT ON CONFLICT DO UPDATE in plpgsql function

現在我希望觸發器insert_data_func使用參數 ( model_cd, processdate) 呼叫觸發器函式。

有不同的模型，這就是為什麼我試圖通過使用動態 SQL 來簡化事情。

功能：

CREATE OR REPLACE FUNCTION insert_data_func() 
RETURNS TRIGGER AS 
$func$ 

DECLARE 
model_cd text;
processdate text;

BEGIN  
model_cd := TG_ARGV[0];
processdate := TG_ARGV[1];

EXECUTE
 $x$INSERT INTO tb_moldsummary AS t
    SELECT $1.machine                               
         , $1.model
         , split_part(lot, '_', 1)
         , right(lot, position('_' IN lot) * -1)    
         , COUNT(lot)
         , $1.datetimestamp
    FROM   model$x$ || to_char(now(), 'YYYYMM') || '
    WHERE  lot = $1.lot
    AND    machine = $1.machine
    GROUP  BY machine, model, lot
    ON     CONFLICT ON CONSTRAINT tb_summary_unique
    DO     UPDATE
    SET    machine = $1.machine                     
         , totalshots = t.totalshots + 1
         , datetimestamp = $1.datetimestamp'
USING NEW;                                            
RETURN NEW; 
END
$func$  
LANGUAGE plpgsql;

扳機：

DO$$
BEGIN

EXECUTE $x$CREATE TRIGGER insert_data_trigger 
          AFTER INSERT ON modelsample$x$ || to_char(now(), 'YYYYMM') ||    
          ' FOR EACH ROW EXECUTE PROCEDURE 
            insert_data_func' || 
           (''modelsample''' || ',' || to_char(now(), 'YYYYMM') ||  ')';
END
$$;

觸發器將模型和過程日期參數傳遞給觸發器函式insert_data_func()。但我得到這個錯誤消息：

錯誤：關係“model_cd201707”不存在

第 9 行：來自 model_cd201707

如何正確使用“ $ x $ " 在insert_data_func()函式上，因為我認為這就是模型名稱沒有從model_cd變數中獲取值的原因。

錯誤消息說relation "model_cd201707" does not exist。我想當時沒有那個名字的表search_path嗎？您的問題中有表名混淆。modelsample..., model_cd..., model... tb_moldsummary. 這有些不對勁 …

除此之外，在將傳遞的參數值分配給變數之後model_cd，processdate您根本沒有使用它們（還）。

假設您的 2 個傳遞的參數應該用於建構表名，它可能會像這樣工作：

CREATE OR REPLACE FUNCTION trg_insert_data_func() 
 RETURNS TRIGGER AS 
$func$ 
BEGIN  
  EXECUTE format(
  $x$INSERT INTO tb_moldsummary AS t
     SELECT $1.machine                               
          , $1.model
          , split_part($1.lot, '_', 1)
          , right($1.lot, position('_' IN lot) * -1)    
          , COUNT(*)  -- never null in this query
          , $1.datetimestamp
     FROM   %I
     WHERE  lot = $1.lot
     AND    machine = $1.machine
     GROUP  BY machine, model, lot
     ON     CONFLICT ON CONSTRAINT tb_summary_unique  -- constraint has same name?
     DO     UPDATE
     SET    machine = $1.machine                     
          , totalshots = t.totalshots + 1
          , datetimestamp = $1.datetimestamp
  $x$, TG_ARGV[0]
  )
  USING NEW;                                            
  RETURN NEW; 
END
$func$  
LANGUAGE plpgsql;

觸發器（將單個連接參數作為表名傳遞）：

DO
$$
BEGIN
  EXECUTE format(
    'CREATE TRIGGER insert_data_trigger 
     AFTER INSERT ON %1$s
     FOR EACH ROW EXECUTE PROCEDURE trg_insert_data_func(%1$L)'
   , 'modelsample' || to_char(now(), 'YYYYMM')
  );
END
$$;

請注意我如何使用%1$sSQL 語句中的（不帶引號的）表名和%1$L參數（引用為字元串文字）。%I在這種情況下，對於已知的合法標識符不需要。有關詳細資訊，請參閱手冊format()。

您可能希望模式限定函式和表名稱是明確的。喜歡public.modelsample。更多的：

• CREATE FUNCTION語句中的特殊語法sth.name()，是什麼意思？

請注意，觸發器定義中內置的日期組件在創建後是不可變的。只有創建語句本身（和触發函式）是“動態的”。

有關的：

• 以列名作為參數修改行的觸發函式

但這似乎是不必要的複雜化。無需傳遞呼叫觸發器的表的名稱。一些特殊變數在 plpgsql 觸發器函式中自動可用。其中：TG_TABLE_SCHEMA和TG_TABLE_NAME。所以在觸發器函式中使用動態SQL，但是觸發器本身可以是簡單的靜態的：

CREATE OR REPLACE FUNCTION trg_insert_data_func() 
 RETURNS TRIGGER AS 
$func$ 
BEGIN  
  EXECUTE format(
  $x$INSERT INTO tb_moldsummary AS t
     SELECT $1.machine                               
          , $1.model
          , split_part($1.lot, '_', 1)
          , right($1.lot, position('_' IN lot) * -1)    
          , COUNT(*)  -- never null in this query
          , $1.datetimestamp
     FROM   %I.%I     -- !!
     WHERE  lot = $1.lot
     AND    machine = $1.machine
     GROUP  BY machine, model, lot
     ON     CONFLICT ON CONSTRAINT tb_summary_unique  -- constraint has same name?
     DO     UPDATE
     SET    machine = $1.machine                     
          , totalshots = t.totalshots + 1
          , datetimestamp = $1.datetimestamp
  $x$, TG_TABLE_SCHEMA, TG_TABLE_NAME   -- !!
  )
  USING NEW;                                            
  RETURN NEW; 
END
$func$  
LANGUAGE plpgsql;

觸發器本身沒有參數。

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