转一个有意思的利用存储过程备份恢复PostgreSQL

【转自 housonglin1213 的博客】http://blog.csdn.net/housonglin1213/article/details/51005540

1、自定义函数脚本备份

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、已备份自定义函数脚本的恢复

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;

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