关于postgresql自定义函数和触发器的一些问题
来源:互联网 发布:js invoke 编辑:程序博客网 时间:2024/05/17 17:17
一、前言
最近接触公司的一个项目,需要做一个数据同步模块,所以要用到触发器。但坑爹的是用的是postgresql数据库而非MySQL,不得不说遇到了很多的坑。本文中的内容基本上都是自己网上查找资料,参考别人的博客,再加自己摸索,才实现的,且已经用到了项目中。可能不是最优的,仅供参考,如果有更好的方法,请留言告知,谢谢!
需求说明:dms_sync是存放数据同步表,如果其他需要同步的表中记录有增删改操作,都会通过触发器往dms_sync表中增加一条数据,如dms_sync表中有以下一条记录:
这说明,用户在2017-09-02 15:39:31往dms_dept表中新插入了一条id为b22ea7c304494dfe8c5cb339930da806的记录。
postgresql的触发器需要用函数来执行,所以要实现以上的效果,还得先自定义函数。
二、自定义函数
1.创建函数模板
CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; [...] BEGIN < function_body > [...] RETURN { variable_name | value } END; LANGUAGE plpgsql;
看模板不如直接看例子。。。
2.创建普通函数
(1)创建自定义uuid()函数
postgresql默认是没有像mysql那样有uuid()函数的,虽然可以拓展,但考虑到项目是给用户用的。如果让用户去装拓展、或者每次初始化数据库时都要安装拓展,有点麻烦。如果自己能创建一个函数用来产生uuid,这样把函数放入初始化数据库脚本即可。函数如下:
---------------------创建UUID函数------------------CREATE OR REPLACE FUNCTION uuid () RETURNS TEXT AS $BODY$DECLARE passed BOOLEAN ;BEGINRETURN (SELECTREPLACE (uuid_in (md5(random() :: TEXT || now() :: TEXT) :: cstring) :: TEXT,'-' :: TEXT,'' :: TEXT)) ; RETURN passed ;END ; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;说明一下:
加上 OR REPLACE的好处在于, 如果函数存在则替代,即允许修改一个存在的函数.
运行结果:
(2)创建带参的insert_sync(recid TEXT,tbname TEXT,typ TEXT)函数
这个函数的作用是触发器触发时,往dms_sync表中插入数据。如下:
--------------------------创建insert_sync(recid TEXT,tbname TEXT,typ TEXT)函数------------------------DROP FUNCTION IF EXISTS insert_sync(recid TEXT,tbname TEXT,typ TEXT);CREATE OR REPLACE FUNCTION insert_sync (recid TEXT,tbname TEXT,typ TEXT) RETURNS BOOLEAN AS $$DECLARE passed BOOLEAN ;BEGININSERT INTO dms_sync (ID,recid,tbname,typ,createtime)VALUES(uuid(), $1, $2, $3, to_char(now(), 'yyyy-mm-dd HH24:MI:ss')) ; RETURN passed ;END ; $$ LANGUAGE plpgsql VOLATILE COST 100;说明一下:
其中的uuid()是自定义的函数,$1表示第一个参数,$2表示第二个函数,一次类推...postgresql函数的参数使用不支持通过参数名,只能通过$1、$2这样来调用。
3.创建触发器函数
触发器函数是触发器触发时调用,函数返回的类型必须是TRIGGER ,且不能有任何参数。在这里建一个trigger_insert_sync()触发器函数,如下:
--------------------------创建trigger_insert_sync函数--------------------------触发器函数可直接覆盖插入,因此无须判断CREATE OR REPLACE FUNCTION trigger_insert_sync () RETURNS TRIGGER AS $$DECLARE passed BOOLEAN ;BEGINIF (TG_OP = 'DELETE') THENIF OLD.data_src IS NULL THENperform insert_sync (OLD . ID, TG_TABLE_NAME, 'del') ;END IF;ELSIF (TG_OP = 'UPDATE') THEN -- data_src字段必须存在IF NEW.data_src IS NULL THEN-- sortnum字段不存在,直接插进去,否则sortnum有变化则不插入(因为删除时会更改sortnum)IF (SELECT COUNT (*) FROM information_schema. COLUMNS WHERE table_schema = TG_TABLE_SCHEMA AND TABLE_NAME = TG_TABLE_NAME AND COLUMN_NAME = 'sortnum' )=0 THEN perform insert_sync (NEW . ID, TG_TABLE_NAME, 'upd') ;ELSEIF(NEW.sortnum =OLD.sortnum ) THENperform insert_sync (NEW . ID, TG_TABLE_NAME, 'upd') ;END IF;END IF; ELSIF (TG_OP = 'INSERT') THENIF NEW.data_src IS NULL THENperform insert_sync (NEW . ID, TG_TABLE_NAME, 'ins') ;END IF;END IF ; RETURN NULL ;END ; $$ LANGUAGE plpgsql VOLATILE COST 100;说明几点:
- postgresql触发器函数中自带一些特殊变量,这里只解释上面函数中所用到的变量:NEW:数据类型是record。在insert、update操作触发时存储新的数据行。
OLD:数据类型是record。在update、delete操作触发时存储旧的数据行。
TG_OP:内容为“INSERT”、“UPDATE”、“DELETE”、“TRUNCATE”,用于指定DML语句类型。
TG_TABLE_NAME:触发器所在表的表名称。
TG_SCHEMA_NAME:触发器所在表的模式
- SELECT COUNT (*) FROM information_schema. COLUMNS WHERE table_schema = TG_TABLE_SCHEMA AND TABLE_NAME = TG_TABLE_NAME AND COLUMN_NAME = 'sortnum' )=0 这句的意思是判断触发器所在的表中是否含有sortnum字段,如果等于0,说明不包含这个字段。
- 触发器触发时调用该函数,通过判断操作类型来往dms_sync表中插入不同的数据,以update操作为例:如果data_src这个字段的值为空,再判断表中是否包含"sortnum"这个字段,如果不包含,直接调用自定义的insert_sync函数往dms_sync表中插入一条记录,如果sortnum不为空,需要判断sortnum有无变化,因为sortnum是序号,删除一条记录时,会更新其余记录的序号,就会触发触发器。
三.创建触发器
所有的准备工作都做好了,最后为需要同步的表创建触发器,以dms_dept表为例,需要创建三个触发器,分别在增删改时触发:
----------------------------给dms_dept创建新增触发器-------------------------DROP TRIGGER IF EXISTS "trigger_dms_dept_ins" on dms_dept;CREATE TRIGGER trigger_dms_dept_ins AFTER INSERTON dms_dept FOR EACH ROW EXECUTE PROCEDURE trigger_insert_sync ();----------------------------给dms_dept创建删除触发器-------------------------DROP TRIGGER IF EXISTS "trigger_dms_dept_del" on dms_dept;CREATE TRIGGER trigger_dms_dept_del AFTER DELETEON dms_dept FOR EACH ROW EXECUTE PROCEDURE trigger_insert_sync ();----------------------------给dms_dept创建修改触发器-------------------------DROP TRIGGER IF EXISTS "trigger_dms_dept_upd" on dms_dept;CREATE TRIGGER trigger_dms_dept_upd AFTER UPDATEON dms_dept FOR EACH ROW EXECUTE PROCEDURE trigger_insert_sync ();
四、总结
过程是漫长而又辛酸的,结果却是短暂而又快乐的,以上的触发器与函数之间的关系可以用下图来表示:
- 关于postgresql自定义函数和触发器的一些问题
- postgresql触发器函数中的一些特殊变量
- MySQL触发器、存储过程、自定义函数、视图的一些笔记
- postgresql 中关于触发器的笔记
- 在做IVMM算法时关于PostgreSQL的一些问题
- 关于触发器的问题?
- 关于PostgreSQL的一些限制
- 存储过程和触发器以及自定义函数的区别
- 关于自定义Camera的一些问题
- 关于自定义view的一些问题
- postgresql 触发器的操作
- 关于PreferenceActivity的使用和一些问题的解决(自定义Title和取值)
- 存储过程、触发器和用户自定义函数实验2、触发器
- 关于EL自定义函数和如果其中参数是一个自定义的类的问题
- windows下postgresql创建函数和数据类型的问题
- 关于Oracle函数wm_concat的一些问题
- 关于构造函数继承的一些问题
- postgreSQL用C写触发器的例子中的问题
- GitHub 的 Pull Request 是指什么意思?
- asp.net core MVC 过滤器之ExceptionFilter过滤器(一)
- 双目标定---非常重要的基础
- 关于 异常 的一些总结(自定义异常确实有用啊)
- Responsive Design with Bootstrap
- 关于postgresql自定义函数和触发器的一些问题
- ACM暑期训练总结
- 闭包的用途
- redis解决购物车的问题
- oracle-11g-安装
- 关于 集合框架 Collection的整理
- == 和equals的区别
- CodeForces-787B(题目不好读懂,懂了题目就发现是水题,感觉自己棒棒哒)
- 批处理关闭PC共享文件夹(Windows下)