postgresql 实现用户自定义方法生成脚本的备份及恢复

最新推荐文章于 2024-09-23 09:15:00 发布

songlin121314

最新推荐文章于 2024-09-23 09:15:00 发布

阅读量2.1k

点赞数 3

分类专栏： PostgreSQL 文章标签： postgresql 函数触发器函数备份还原

本文链接：https://blog.csdn.net/housonglin1213/article/details/51005540

版权

PostgreSQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

1、备份函数、触发器函数生成脚本的函数实现

<pre name="code" class="html">CREATE OR REPLACE FUNCTION function_sql_dump()
  RETURNS void AS
$BODY$
DECLARE
	my_cur refcursor;
	isexisted integer:=0;
	oid_var oid;
	role_oid oid;
BEGIN
	--判断临时表是否已经存在
	select count(*) into isexisted from pg_class where relname = 'function_sql_table';
	--如果已经存在则删除
	IF(isexisted !=0)THEN
	DROP TABLE function_sql_table; 
	END IF;
	--创建临时表-用于存储函数sql语句
	CREATE TABLE function_sql_table(t TEXT); 
	select oid into role_oid from pg_roles where rolname='smartsys';
	IF role_oid is null THEN
		return;
	END IF;
	OPEN my_cur FOR select oid from pg_proc where proowner=role_oid order by oid;
	LOOP
	FETCH my_cur INTO oid_var;
		IF NOT FOUND THEN EXIT; END IF;
		INSERT INTO function_sql_table(t) VALUES (pg_get_functiondef(oid_var)); 
	END LOOP;
	CLOSE my_cur;
	COPY function_sql_table TO 'D:/PostgreSQL/9.5/function_sql_bak.sql';
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION function_sql_dump()
  OWNER TO smartsys;

补充说明：备份出的sql文件是存储于postgresql数据库服务所在服务器的目录中的。smartsys为数据库用户名

注：pg_get_functiondef(oid_var)方法返回text类型自带换行（效果如下图，需要纵向拉伸才能看到全部信息）

2、已备份函数创建脚本修改后批量还原

<span style="font-size:18px;">-- Function: function_sql_restore(character varying)

-- DROP FUNCTION function_sql_restore(character varying);

CREATE OR REPLACE FUNCTION function_sql_restore(func_name character varying)
  RETURNS void AS
$BODY$
DECLARE
	my_cur refcursor;
	isexisted integer:=0;
	oid_var oid;
	sql_str text;
	sql_var varchar(1024);
BEGIN
	--判断临时表是否已经存在
	select count(*) into isexisted from pg_class where relname = 'function_sql_table';
	--如果已经存在则删除
	IF(isexisted !=0)THEN
	DROP TABLE function_sql_table; 
	END IF;
	--创建临时表-用于存储函数sql语句
	CREATE TABLE function_sql_table(t TEXT); 

	COPY function_sql_table from 'D:/PostgreSQL/9.5/function_sql_bak.sql';

	IF(func_name is not null)THEN
		OPEN my_cur FOR select t from function_sql_table where t like '%'||func_name||'%';
	ELSE
		OPEN my_cur FOR select t from function_sql_table;
	END IF;
	LOOP
		FETCH my_cur INTO sql_str;
		IF NOT FOUND THEN EXIT; END IF;
		execute sql_str;
	END LOOP;
	CLOSE my_cur;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
</span>

其中该方法中的参数：func_name为函数名称，即：指定要恢复的函数。