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

末蓝、 2022-07-28 01:48 11阅读 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类型自带换行（效果如下图，需要纵向拉伸才能看到全部信息）

![Image 1][]

![Center][]

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为函数名称，即：指定要恢复的函数。

[Image 1]: 
[Center]: /images/20220727/4f072147cefc4a82a5e13131c75e6a5e.png