sql中事件触发器跟踪记录ALTER/DROP/CREATE表的操作，适用于各类sql语句，包括批量执行sql语句

最新推荐文章于 2022-04-17 21:17:13 发布

济海河神

最新推荐文章于 2022-04-17 21:17:13 发布

阅读量411

点赞数

分类专栏： postgresql 文章标签： sql 数据库 postgresql

本文链接：https://blog.csdn.net/liyangxueit/article/details/97016056

版权

本文介绍了如何在SQL中设置事件触发器，以便详细记录ALTER、DROP和CREATE表的活动。内容涉及如何捕获表名和模式名，以及处理包括批量执行在内的各种SQL语句。

摘要由CSDN通过智能技术生成

先获取alter/drop/create的表名和模式名

create or replace function public.get_tablen_scheman
(in _Qry_1 text,in op_type text)
returns VOID
LANGUAGE plpgsql
as $$
declare 
table_n text;
schema_n text;
--_Qry_2 text =replace(lower(_Qry_1),CHR(10),' ');
_Qry_2 text =trim(lower(_Qry_1));
begin
	if position(CHR(10) in _Qry_2)<>0 or position(chr(13) in _Qry_2)<>0then
	_Qry_2=replace(_Qry_2,chr(10),'');
	_Qry_2=replace(_Qry_2,chr(13),'');
	end if;
	if op_type='CREATE TABLE' and position('create' in _Qry_2)=1 then
		if position('as' in _Qry_2)=0 then
			if position('exists'in _Qry_2)=0 then
				if position('.'in _Qry_2)=0 then
					table_n=trim(substring(_Qry_2,min(position('table' in _Qry_2))+6,min(position('(' in _Qry_2))-min(position('table'in _Qry_2))-6));
					schema_n='public';
				elseif position('.'in _Qry_2)<>0 then 
					table_n=trim(substring(_Qry_2,min(position('.' in _Qry_2))+1,min(position('(' in _Qry_2))-min(position('.'in _Qry_2))-1));
					schema_n=trim(substring(_Qry_2,min(position('table' in _Qry_2))+6,min(position('.' in _Qry_2))-min(position('table'in _Qry_2))-6));
				end if;
			elseif position('exists'in _Qry_2)<>0 then
				if position('.'in _Qry_2)=0 then
					table_n=trim(substring(_Qry_2,position('exists' in _Qry_2)+7,min(position('(' in _Qry_2))-position('exists'in _Qry_2)-7));
					schema_n='public';
				elseif position('.'in _Qry_2)<>0 then 
					table_n=trim(substring(_Qry_2,min(position('.' in _Qry_2))+1,min(position('(' in _Qry_2))-min(position('.'in _Qry_2))-1));
					schema_n=trim(substring(_Qry_2,position('exists' in _Qry_2)+7,min(position('.' in _Qry_2))-position('exists'in _Qry_2)-7));	
				end if;
			end if;
		elseif position('as'in _Qry_2)<>0 then
			if position('exists'in _Qry_2)=0 then
				if position('.'in _Qry_