Postgresql如何批量 修改 函数 拥有者，默认创建的函数对象的拥有者为当前创建的用户，如果使用postgres超级管理员创建一个test()的函数，拥有者就是postgres用户。下面讲解下如何批量修改拥有者。
本文演示的Postgresql版本如下：
PostgreSQL 9.6.8
相关视图
要查询Postgresql的函数和函数参数需要使用函数视图和参数视图，分别记录了函数信息和参数列表信息。
视图一： information_schema.routines
视图routines包含当前数据库中所有的函数。只有那些当前用户能够访问（作为拥有者或具有某些特权）的函数才会被显示。需要用到的列如下，完整视图讲解请参考官方文档。
| specific_schema | sql_identifier | 包含该函数的模式名 |
| routine_name | sql_identifier | 该函数的名字（在重载的情况下可能重复） |
| specific_name | sql_identifier | 该函数的"专用名"。这是一个在模式中唯一标识该函数的名称，即使该函数真正的名称已经被重载。专用名的格式尚未被定义，它应当仅被用来与指定例程名称的其他实例进行比较。 |
视图二： information_schema.parameters
视图parameters包含当前数据库中所有函数的参数的有关信息。只有那些当前用户能够访问（作为拥有者或具有某些特权）的函数才会被显示。需要用到的列如下，完整视图讲解请参考官方文档。
| parameter_name | sql_identifier | 参数名，如果参数没有名称则为空 |
| udt_name | sql_identifier | 该参数的数据类型的名字 |
| ordinal_position | cardinal_number | 该参数在函数参数列表中的顺序位置（从 1 开始计数） |
| specific_name | cardinal_number | 该函数的"专用名"。详见第 35.40 节。 |
注意：可以通过routines. specific_name 和 parameters.specific_name字段关联查询。
单个修改
如果需要修改的函数只有一个，请执行如下SQL语句即可：
如果需要修改的函数只有一个，请执行如下SQL语句即可：
|
1 2 3 4 |
// 无参数函数 ALTER FUNCTION "abc" . "test" () OWNER TO "dbadmin" ; //带参数函数 ALTER FUNCTION "abc" . "test3" (p1 varchar , p2 varchar ) OWNER TO "dbadmin" ; |
批量修改
首先可以查询当前模式下函数的所有者分别是哪个用户，使用下面SQL来查询：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.prolang WHERE 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语句），参考如下：
|
1 2 3 4 5 6 7 8 9 10 11 |
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所有行连接成字符串，并用逗号分隔符分隔。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DO $$ DECLARE r record; BEGIN FOR r IN 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 fname FROM tmp GROUP BY specific_schema, routine_name LOOP EXECUTE 'ALTER FUNCTION ' || r.fname|| ' OWNER TO "dbadmin" ' ; END LOOP; END $$; |
可以看到模式[abc]的Owner已经全部改为dbadmin这个账号了。
上次批量修改函数可能存在部分特殊场景会报错， 会把[参数类型] + [返回类型] 拼接在一起
改进方法：我们通过pg_catalog目录来实现批量修改，参考代码如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DO $$ DECLARE r record; BEGIN FOR 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 tmp LOOP EXECUTE 'ALTER FUNCTION ' || r.fname|| ' OWNER TO "postgres" ' ; END LOOP; END $$; |
补充：PostgreSQL更改Owner所有者
网上一个大神写的
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT ‘ alter table ' || nsp.nspname || ‘.' || cls.relname || ' owner to usr_zhudong;' || chr ( 13 ) FROM pg_catalog.pg_class cls, pg_catalog.pg_namespace nsp WHERE nsp.nspname IN ( ‘ public ' ) AND cls.relnamespace = nsp.oid AND cls.relkind = ‘r' ORDER BY nsp.nspname, cls.relname; |
我来做一个改版
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT 'alter table ' || nsp.nspname || '.' || cls.relname || ' owner to test2;' || chr ( 13 ) FROM pg_catalog.pg_class cls, pg_catalog.pg_namespace nsp WHERE nsp.nspname IN ( 'public' ) AND cls.relnamespace = nsp.oid AND cls.relkind = 'r' ORDER BY nsp.nspname, cls.relname;
SELECT 'alter table "' || nsp.nspname || '"."' || cls.relname || '" owner to user01;' || chr ( 13 ) FROM pg_catalog.pg_class cls, pg_catalog.pg_namespace nsp WHERE nsp.nspname IN ( 'public' ) AND cls.relnamespace = nsp.oid AND cls.relkind = 'r' ORDER BY nsp.nspname, cls.relname; |
效果：
以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。
原文链接：https://modestchen.blog.csdn.net/article/details/86609797
查看更多关于PostgreSQL批量修改函数拥有者的操作的详细内容...