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.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语句），参考如下：
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;
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目录来实现批量修改，参考代码如下：
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所有者
网上一个大神写的
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;
我来做一个改版
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;
效果：
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。