批量设置表的加密规则函数

create or replace function MLS_TRANSPARENT_CRYPT_ALGORITHM_BIND_ALL_TABLE(a_schema text,a_algo_id int) returns text as
$$
declare
    v_rec record;    
    v_algorithm_name text;
    v_raise_notice text;
    v_sqlstate text;
    v_context text; 
    v_message_text text; 
begin
    perform 1 from pg_namespace where nspname=a_schema ;
    if not found then
        return '模式['||a_schema||']不存在';        
    end if;
    --显示使用的加密算法
    select algorithm_name INTO v_algorithm_name from pg_transparent_crypt_policy_algorithm where algorithm_id=a_algo_id;
    if not found then
        return '加密算法id['||a_algo_id::text||']不存在';   
    else
        raise notice '你使用的密码算法为--%',v_algorithm_name;
    end if;   
    for v_rec in select pg_tables.schemaname,pg_tables.tablename,pg_transparent_crypt_policy_map.tblname from pg_tables left outer join pg_transparent_crypt_policy_map on pg_tables.schemaname=pg_transparent_crypt_policy_map.nspname and pg_tables.tablename=pg_transparent_crypt_policy_map.tblname where pg_tables.schemaname=a_schema and pg_transparent_crypt_policy_map.tblname is null loop        
        begin
           PERFORM MLS_TRANSPARENT_CRYPT_ALGORITHM_BIND_TABLE(v_rec.schemaname,v_rec.tablename, a_algo_id);  
           EXCEPTION WHEN OTHERS THEN
           GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE,
                                   v_message_text = MESSAGE_TEXT,
                                   v_context = PG_EXCEPTION_CONTEXT; 
           RAISE NOTICE '出错信息 : %',v_message_text;           
        end;
    end loop;    
    return '配置表加密完成'; 
end;
$$
language plpgsql;