PostgreSQL10.0preview功能增強-觸發器函式內建中間表

德哥發表於2017-04-02
SQLView觸發器函式

標籤

PostgreSQL , 10.0 , 觸發器 , 中間表 , OLD , NEW

背景

在觸發器中，如果要提取觸發該事件的記錄，使用OLD和NEW關鍵字。

OLD.* ， NEW.* 提取

對於for statement after觸發器，觸發的記錄數可能是很多的，PostgreSQL 10.0增加了一個功能，中間表。

在觸發器函式中，可以使用這個中間表，中間表的資料就是觸發器涉及的資料，中級鏢的功能支援after觸發器（因為after後才有全部的記錄呀）。

語法

    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]  
    [ FOR [ EACH ] { ROW | STATEMENT } ]

例子

1. 建立一個測試表

+CREATE TABLE transition_table_base (id int PRIMARY KEY, val text);

2. 建立一個觸發器函式，其中newtable, oldtable分別是中間表，中間表不需要定義，就是觸發器對應的表結構。

+CREATE OR REPLACE FUNCTION transition_table_base_upd_func()  
+  RETURNS trigger  
+  LANGUAGE plpgsql  
+AS $$  
+DECLARE  
+  t text;  
+  l text;  
+BEGIN  
+  t = ``;  
+  FOR l IN EXECUTE  
+           $q$  
+             EXPLAIN (TIMING off, COSTS off, VERBOSE on)  
+             SELECT * FROM oldtable ot FULL JOIN newtable nt USING (id)  
+           $q$ LOOP  
+    t = t || l || E`
`;  
+  END LOOP;  
+  
+  RAISE INFO `%`, t;  
+  RETURN new;  
+END;  
+$$;

3. 建立for statement after觸發器，指定old table名字叫做oldtable, new table名字叫做newtable。

注意update支援old,new table, insert支援new table, delete支援old table

+CREATE TRIGGER transition_table_base_upd_trig  
+  AFTER UPDATE ON transition_table_base  
+  REFERENCING OLD TABLE AS oldtable NEW TABLE AS newtable  
+  FOR EACH STATEMENT  
+  EXECUTE PROCEDURE transition_table_base_upd_func();

4. 測試，可以看到觸發器輸出的內容，oldtable, newtable實際上就是transition_table_base表的表結構。

對應的就是原來常用的OLD, NEW關鍵字，只是以中間表的形式體現。

+UPDATE transition_table_base  
+  SET val = `*` || val || `*`  
+  WHERE id BETWEEN 2 AND 3;  
  
+INFO:  Hash Full Join  
+  Output: COALESCE(ot.id, nt.id), ot.val, nt.val  
+  Hash Cond: (ot.id = nt.id)  
+  ->  Named Tuplestore Scan  
+        Output: ot.id, ot.val  
+  ->  Hash  
+        Output: nt.id, nt.val  
+        ->  Named Tuplestore Scan  
+              Output: nt.id, nt.val

中間表有什麼用呢？

某些場景中，可以使用”for each statement+中間表” 替代for each row，因為for each statement是末尾觸發，效能更好。

這個patch的討論，詳見郵件組，本文末尾URL。

PostgreSQL社群的作風非常嚴謹，一個patch可能在郵件組中討論幾個月甚至幾年，根據大家的意見反覆的修正，patch合併到master已經非常成熟，所以PostgreSQL的穩定性也是遠近聞名的。

參考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=59702716324ab9c07b02fb005dcf14c7f48c4632

https://www.postgresql.org/docs/devel/static/sql-createtrigger.html


相關文章