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

相关资源：python入门教程(PDF版)