PostgreSQL批量修改函数拥有者的操作

PostgreSQL如何批量修改函数拥有者，默认创建的函数对象的拥有者为当前创建的用户，如果使用postgres超级管理员创建一个test()的函数，拥有者就是postgres用户。下面讲解下如何批量修改拥有者。

本文演示的Postgresql版本如下：

PostgreSQL 9.6.8

相关视图

要查询Postgresql的函数和函数参数需要使用函数视图和参数视图，分别记录了函数信息和参数列表信息。

视图一： information_schema.routines

视图routines包含当前数据库中所有的函数。只有那些当前用户能够访问（作为拥有者或具有某些特权）的函数才会被显示。需要用到的列如下，完整视图讲解请参考官方文档。

视图二： information_schema.parameters

视图parameters包含当前数据库中所有函数的参数的有关信息。只有那些当前用户能够访问（作为拥有者或具有某些特权）的函数才会被显示。需要用到的列如下，完整视图讲解请参考官方文档。

注意：可以通过routines. specific_name 和 parameters.specific_name字段关联查询。

单个修改

如果需要修改的函数只有一个，请执行如下SQL语句即可：

如果需要修改的函数只有一个，请执行如下SQL语句即可：

// 无参数函数ALTER FUNCTION "abc"."test"() OWNER TO "dbadmin";
    //带参数函数ALTER FUNCTION "abc"."test3"(p1 vArchar, p2 VARchar) OWNER TO "dbadmin";
    

批量修改

首先可以查询当前模式下函数的所有者分别是哪个用户，使用下面SQL来查询：

SELECT n.nspname as "Schema",p.PRoname as "Name",pg_cataLOG.pg_get_function_result(p.oid) as "Result data type",pg_catalog.pg_get_function_arguments(p.oid) as "argument data types",pg_catalog.pg_get_userbyid(p.proowner) as "Owner"From pg_catalog.pg_proc p  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace  LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolangWHERE pg_catalog.pg_function_is_visible(p.oid)  AND n.nspname >
     'pg_catalog'  AND n.nspname >
     'information_schema'ORDER BY 1, 2;
    

当前显示模式“abc”有2个无参函数和1个带参函数，拥有者都是postgres超级用户。

然后根据上面讲的两个视图： routines 和 parameters关联查询出模式下的所有函数和参数（目的是为了拼接SQL语句），参考如下：

SELECT "routines".specific_schema,"routines".routine_name, COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回参数中的第一个非null的值COALESCE("parameters".udt_name, '') AS udt_name,COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,"parameters".ordinal_position From "information_schema"."routines"LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name WHERE "routines".specific_schema='abc' ORDER BY 1,2,6;
    

这里我们再使用聚合函数: string_agg 把字段 params所有行连接成字符串，并用逗号分隔符分隔。

WITH tmp AS (SELECT "routines".specific_schema,"routines".routine_name, COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回参数中的第一个非null的值COALESCE("parameters".udt_name, '') AS udt_name,COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,"parameters".ordinal_position FROM "information_schema"."routines"LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name WHERE "routines".specific_schema='abc' ORDER BY 1,2,6) SELECT specific_schema, routine_name, string_agg(params, ',') AS params, '"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' AS fname FROM tmp group by specific_schema, routine_name;
     

最后使用一个Postgres执行代码片段完成批量修改，完整SQL如下：

DO $$DECLARE r record;
    BEginFOR r INWITH tmp AS (SELECT "routines".specific_schema,"routines".routine_name, COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回参数中的第一个非null的值COALESCE("parameters".udt_name, '') AS udt_name,COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,"parameters".ordinal_position FROM "information_schema"."routines"LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name WHERE "routines".specific_schema='abc' ORDER BY 1,2,6) SELECT '"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' AS fname FROM tmp GROUP BY specific_schema, routine_nameLOOPEXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "dbadmin" ';
    END LOOp;
    END $$;
    

可以看到模式“abc”的Owner已经全部改为dbadmin这个账号了。

上次批量修改函数可能存在部分特殊场景会报错， 会把“参数类型” + “返回类型” 拼接在一起

改进方法：我们通过pg_catalog目录来实现批量修改，参考代码如下：

DO $$DECLARE r record;
    BEGINFOR r IN WITH tmp AS ( SELECT n.nspname as "Schema", p.proname as "Name", pg_catalog.pg_get_function_result(p.oid) as "Result data type", pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types", CASE WHEN p.pROIsagg THEN 'agg' WHEN p.proiswindow THEN 'window' WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger' ELSE 'normal' END as "Type" FROM pg_catalog.pg_proc p  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'etl' ORDER BY 1, 2, 4 ) SELECT '"' || "Schema" || '"' || '.' || '"' || "Name" || '"' || '(' || "Argument data types" ||')' AS fname FROM tmpLOOPEXECUTE 'ALTER FUNCTION '|| r.fname||' OWNER TO "postgres" ';
    END LOOP;
    END $$;
    

补充：PostgreSQL更改Owner所有者

网上一个大神写的

SELECT‘ALTER TABLE ' || nsp.nspname || ‘.' || cls.relname || ' owner to usr_zhudong;
    ' || chr ( 13 )FROMpg_catalog.pg_class cls,pg_catalog.pg_namespace nspWHEREnsp.nspname IN ( ‘public' )AND cls.relnamespace = nsp.oidAND cls.relkind = ‘r'ORDER BYnsp.nspname,cls.relname;
    

我来做一个改版

SELECT'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to test2;
    ' || chr ( 13 )FROMpg_catalog.pg_class cls,pg_catalog.pg_namespace nspWHEREnsp.nspname IN ( 'public' )AND cls.relnamespace = nsp.oidAND cls.relkind = 'r'ORDER BYnsp.nspname,cls.relname;
     SELECT'alter table "' || nsp.nspname || '"."' || cls.relname || '" owner to user01;
    ' || chr ( 13 )FROMpg_catalog.pg_class cls,pg_catalog.pg_namespace nspWHEREnsp.nspname IN ( 'public' )AND cls.relnamespace = nsp.oidAND cls.relkind = 'r'ORDER BYnsp.nspname,cls.relname;
    

效果：

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• Postgresql去重函数distinct的用法说明
• PostgreSQL 定义返回表函数的操作
• PostgreSQL的generate_series()函数的用法说明
• postgresql合并string_agg函数的实例
• PostgreSQL数据类型格式化函数操作
• 在postgresql数据库中判断是否是数字和日期时间格式函数操作
• Postgresql自定义函数详解
• postgresql 循环函数的简单实现操作

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！