批量设置表owner的函数

CREATE OR REPLACE FUNCTION public.alter_owner(a_schema_name varchar,a_role_name varchar) RETURNS TEXT AS
$$
DECLARE            
    -- a_schema_name:指定某个模式下，不对定是对数据库的所有表
    -- a_role_name：表所有者
    v_rec RECORD;    
    v_sql TEXT;   
BEGIN        
    IF  a_schema_name != '' THEN  --如果指模式，检查模式是否存
        PERFORM 1 FROM pg_namespace WHERE nspname = a_schema_name;
        IF NOT FOUND THEN
            RETURN '指定的模式 ' || a_schema_name || ' 不存在!';
        END IF;
    END IF;
    PERFORM 1 FROM pg_roles WHERE rolname = a_role_name ;
    IF NOT FOUND THEN  --检查用户是否存在
        RETURN '指定的用户 ' || a_role_name || ' 不存在!';
    END IF;
    IF  a_schema_name != '' THEN    --指定了模式  
             v_sql:='SELECT schemaname,tablename FROM pg_tables WHERE schemaname=''' || a_sche || '''';
    ELSE
             v_sql:='SELECT schemaname,tablename FROM pg_tables WHERE schemaname!=''pg_catalog'' AND schemaname!=''information_schema'' ';        
    END IF;
    FOR v_rec IN EXECUTE v_sql LOOP        
        EXECUTE 'ALTER TABLE "'||v_rec.schemaname||'"."'||v_rec.tablename||'" OWNER TO '||a_role_name;   
    END LOOP;
    RETURN 'ok';
END;
$$
LANGUAGE PLPGSQL;
COMMENT ON FUNCTION  public.alter_owner(a_schema_name varchar,a_role_name varchar)  IS '批量设置表所有者';
CREATE OR REPLACE FUNCTION public.alter_owner(a_role_name varchar) RETURNS TEXT AS
$$      
BEGIN        
    RETURN public.alter_owner('',a_role_name); 
END;
$$
LANGUAGE PLPGSQL;
COMMENT ON FUNCTION  public.alter_owner(a_role_name varchar)  IS '批量设置表所有者重载';