个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
31
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章PostgreSQL批量修改函数拥有者的操作由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
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批量修改函数拥有者的操作的文章就讲到这里了,如果你想了解更多关于PostgreSQL批量修改函数拥有者的操作的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
31
4
0
前言： 有时候，一个数据库有多个帐号，包括数据库管理员，开发人员，运维支撑人员等，可能有很多帐号都有比较大的权限，例如DDL操作权限(创建，修改，删除存储过程，创建，修改，删除表等），账户多了，管理
这个问题已经有答案了: Condition variable deadlock (2 个回答) 已关闭 5 年前。 在研究多线程时，我编写了以下代码，但在屏幕上没有观察到输出。我在这里做错了什么？我期
复制代码 代码如下: <IfModule mod_rewrite.c> RewriteEngineOn RewriteBase/ #将www.zzvips.com跳转到www.zzv
复制代码 代码如下: <IfModule mod_rewrite.c> RewriteEngine On RewriteBase / # 把 www.zzvips.com
复制代码 代码如下: Const T_GATEWAY = "1.1.1.1" '网关 Const T_NEWDNS1 = "2.2.2.2" 'DNS1
0. 修改索引 大文本字段支持排序 PUT http://localhost:9200/lrc_blog/_mapping //请求体 { "properties": { "title": { "t
仅 react 当状态发生变化时重新渲染 . 那么为什么我会直接看到我对真实 DOM 所做的更改呢？ 我知道我正在修改真实的 DOM，但是当我根本没有改变状态时触发重新渲染的是什么。 import R
Xcode beta 5 推出 @FetchRequest对于 SwiftUI。 我有一个 View ，它有一个 @FetchRequest . NSFetchRequest是在管理器中创建的，该管理
关闭。这个问题需要更多 focused .它目前不接受答案。 想改进这个问题？更新问题，使其仅关注一个问题 editing this post . 7年前关闭。 Improve this questi
我有一个表达式[text][id]应替换为链接 text 解决方案是( id 是整数) $s = preg_replace("/\[([^\]]+)(\]*)\]\[([0-9]+)\]/","$1$
我在 repo 中有一个文件，我不想让任何人更新。 我能做什么？ 最佳答案 你想要svn锁:http://www.linxit.de/svnbook/en/1.2/svn.ref.svn.c.lock
说我有项目 list 。我想导出到csv，但在此之前我想做一些计算/修改。 基本上，设置如下所示: PS C:\Files> gci Directory: C:\Files Mode
我有一个非常简单的问题 - 是否可以修改 Java API 的源代码，例如Junit，JABX ？ 我知道这似乎是一个非常愚蠢的问题，但它一直困扰着我一段时间。 最佳答案 如果您可以掌握源代码，那么请
我有一个带有变量/列的小标题，其中包括不同形状的小标题列表。我想为其中一个变量中的每个(子)标题添加一个变量/列。 例如此类数据 library("tibble") aaa aaa # A tibb
我有几个菜单，可以在单击时向当前链接添加变量。这是一个例子: 1 2 3 x y z 我的问题是，如果我选择“y”2次，它会添加“&cord=y”2次。相反，我希望它替
我有两个项目:一个服务项目和一个服务安装程序项目。服务项目具有适合我的产品的装配信息。它包括公司信息和正确的服务名称。一旦服务实际安装，所有这些似乎都会被忽略。安装服务时，它使用在服务安装程序的ini
以下代码何时可能产生副作用？ @some = map { s/xxx/y/; $_ } @some; perlcritic 将其解释为危险的，因为例如: @other = map { s/xxx/y/
我想知道以下哪种解决方案更好:我想修改一些 .class 文件，我意识到有两种方法可以做到这一点: 反编译.class文件，修改它，最后再次编译。 - 直接用十六进制编辑器修改。 谢谢 最佳答案 在这
这是我的按钮代码 onclick 我希望我的程序等待用户单击一个 JPanel，并且当用户单击 JPanel 时，它应该在控制台上打印其名称。 此按钮代码未显示输出 JPopupMenu popu
我正在使用一个具有“getName()”方法的特定 API。 getName() 返回一个字符串。是否可以修改该字符串？ API 中不包含修饰符方法，并且 String getName() 返回的是私
我是一名优秀的程序员,十分优秀！