PostgreSQL 10.0 preview 功能增强 - 触发器函数内置中间表

标签

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'\n';  
+  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

上一篇:传统金融IT男转型互联网大数据码农,图啥?


下一篇:mac 终端添加颜色