在 plpgsql 函式中執行動態 INSERT ON CONFLICT DO UPDATE

我有這個每個月創建的表。前任。model201707（‘model’ = 固定；‘201707’ = 基於目前日期的年月）：

| COLUMN_NAME   | TYPE        |
|---------------|-------------|
| serial        | varchar     |
| lot           | varchar     |
| model         | varchar     |
| line          | varchar     |
| machine       | varchar     |
| datetimestamp | timestamptz |

範例值：

| serial | lot        | model  | line | machine | datetimestamp       |
|--------|------------|--------|------|---------|---------------------|
| 1      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:39 |
| 2      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:42 |
| 3      | 16_F22_F23 | sample | A    | 1       | 2017-02-28 07:22:45 |

我創建了一個函式來從表中獲取數據model201707並將其插入表中tb_moldsummary：

| COLUMN_NAME   | TYPE      |    |    |     |
|---------------|-----------|----|----|-----|
| machine_name  | varchar   | NN | PK |     |
| model         | varchar   | NN | PK | UNQ |
| mold          | varchar   | NN | PK | UNQ |
| cavity        | varchar   | NN | PK | UNQ |
| totalshots    | int4      |    |    |     |
| datetimestamp | timestamp |    |    |     |

範例值：

| machine | model  | m_id | cav_id  | totalshots | datetimestamp       |
|---------|--------|------|---------|------------|---------------------|
| 1       | sample | 16   | F22_F23 | 3          | 2017-02-28 07:22:45 |

功能：

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

EXECUTE 'INSERT INTO tb_moldsummary '
   ||  'SELECT NEW.machine, NEW.model, split_part(NEW.lot, ''_'', 1), '
   ||  'SUBSTRING(NEW.lot FROM (POSITION(''_'' in NEW.lot)+1) FOR LENGTH(NEW.lot)), COUNT(lot), NEW.datetimestamp '
   ||  'FROM model' || to_char(CURRENT_TIMESTAMP, 'YYYYMM') 
   ||  ' WHERE lot = NEW.lot AND machine = NEW.machine GROUP BY machine, model, lot '
   ||  'ON CONFLICT ON CONSTRAINT tb_summary_unique '
   ||  'DO UPDATE SET '
   ||  'machine = NEW.machine, '
   ||  'totalshots = tb_moldsummary.totalshots + 1, '
   ||  'datetimestamp = NEW.datetimestamp ';


RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER insert_data_trigger 
AFTER INSERT ON model201707
FOR EACH ROW 
EXECUTE PROCEDURE insert_data_func();

錯誤：

[Err] ERROR:  missing FROM-clause entry for table "new"
LINE 1: INSERT INTO tb_moldsummary SELECT NEW.machine, NEW.model, sp...

我已經檢查了以下連結，但我很困惑：

PLPGSQL 語句執行動態 SQL

在觸發器函式中插入記錄到表中

這個動態 UPSERT 的正確語法是什麼？

多個問題。這應該有效：

CREATE OR REPLACE FUNCTION insert_data_func() 
 RETURNS TRIGGER AS 
$func$ 
BEGIN  -- !
  EXECUTE
     $x$INSERT INTO tb_moldsummary AS t
        SELECT $1.machine                               -- !
             , $1.model
             , split_part(lot, '_', 1)
             , right(lot, position('_' IN lot) * -1)    -- ! simpler
             , 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; -- can be RETURN NULL for AFTER trigger
END
$func$  LANGUAGE plpgsql;

我用重要的修復標記了行。

要點

• NEW與子句EXECUTE一起傳遞USING（如您引用的問題的答案中所示）：

  • 將 RECORD 插入到觸發器函式中的表中？

• 美元引號有助於簡化包含單引號的字元串文字的語法。

• 代替

SUBSTRING(NEW.lot FROM (POSITION('_' in NEW.lot)+1) FOR LENGTH(NEW.lot))

使用這個更簡單、更快速的等效表達式：

right(NEW.lot, position('_' IN NEW.lot) * -1)

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