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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介:

标签

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
6月前
|
关系型数据库 Serverless 分布式数据库
【公测】PolarDB PostgreSQL版Serverless功能免费使用​!
【公测】PolarDB PostgreSQL版Serverless功能免费使用​,公测于2024年3月28日开始,持续三个月,公测期间可以免费使用!
|
2月前
|
关系型数据库 Serverless 定位技术
PostgreSQL GIS函数判断两条线有交点的函数是什么?
PostgreSQL GIS函数判断两条线有交点的函数是什么?
186 60
|
3月前
|
SQL 自然语言处理 关系型数据库
在 PostgreSQL 中使用 `REPLACE` 函数
【8月更文挑战第8天】
788 9
在 PostgreSQL 中使用 `REPLACE` 函数
|
2月前
|
SQL 关系型数据库 C语言
PostgreSQL SQL扩展 ---- C语言函数(三)
可以用C(或者与C兼容,比如C++)语言编写用户自定义函数(User-defined functions)。这些函数被编译到动态可加载目标文件(也称为共享库)中并被守护进程加载到服务中。“C语言函数”与“内部函数”的区别就在于动态加载这个特性,二者的实际编码约定本质上是相同的(因此,标准的内部函数库为用户自定义C语言函数提供了丰富的示例代码)
|
3月前
|
关系型数据库 PostgreSQL
PostgreSQL的null值函数
【8月更文挑战第20天】PostgreSQL的null值函数
74 3
|
3月前
|
监控 物联网 关系型数据库
使用PostgreSQL触发器解决物联网设备状态同步问题
在物联网监控系统中,确保设备状态(如在线与离线)的实时性和准确性至关重要。当设备状态因外部因素改变时,需迅速反映到系统内部。因设备状态数据分布在不同表中,直接通过应用同步可能引入复杂性和错误。采用PostgreSQL触发器自动同步状态变化是一种高效方法。首先定义触发函数,在设备状态改变时更新管理模块表;然后创建触发器,在状态字段更新后执行此函数。此外,还需进行充分测试、监控性能并实施优化,以及在触发函数中加入错误处理和日志记录功能。这种方法不仅提高自动化程度,增强数据一致性与实时性,还需注意其对性能的影响并采取优化措施。
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
302 0
|
4月前
|
SQL Oracle 关系型数据库
|
4月前
|
关系型数据库 BI 数据处理
|
5月前
|
JSON 关系型数据库 数据库
PostgreSQL中json_to_record函数的神秘面纱
`json_to_record`是PostgreSQL中的函数,用于将JSON数据转换为RECORD类型,便于查询和分析。基本用法是传入JSON数据,如`SELECT json_to_record('{"name": "张三", "age": 30}'::json);`。还可结合FUNCTION创建自定义函数,实现复杂功能。在实际应用中,它简化了对JSON格式数据的处理,例如筛选年龄大于30的用户。了解并善用此函数能提升数据库操作效率。本文由木头左分享,期待你的点赞和收藏,下次见!
PostgreSQL中json_to_record函数的神秘面纱

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版