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

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

本文演示的Postgresql版本如下:

PostgreSQL 9.6.8

相关视图

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

视图一: information_schema.routines

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

名称数据类型备注
specific_schemasql_identifier包含该函数的模式名
routine_namesql_identifier该函数的名字(在重载的情况下可能重复)
specific_namesql_identifier该函数的"专用名"。这是一个在模式中唯一标识该函数的名称,即使该函数真正的名称已经被重载。专用名的格式尚未被定义,它应当仅被用来与指定例程名称的其他实例进行比较。

视图二: information_schema.parameters

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

名称数据类型备注
parameter_namesql_identifier参数名,如果参数没有名称则为空
udt_namesql_identifier该参数的数据类型的名字
ordinal_positioncardinal_number该参数在函数参数列表中的顺序位置(从 1 开始计数)
specific_namecardinal_number该函数的"专用名"。详见第 35.40 节。

注意:可以通过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;

效果:

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

以上是 PostgreSQL批量修改函数拥有者的操作 的全部内容, 来源链接: utcz.com/z/351705.html

回到顶部