获取所有程序性的,用户定义的功能 [英] Get all procedural , user defined functions
问题描述
如何通过 SQL
查询获取所有用户定义函数的列表?
How to get list of all user defined functions via SQL
query ?
SELECT p.proname, p.pronargs, t.typname
FROM pg_proc p, pg_language l, pg_type t
WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname = 'c'
ORDER BY proname;
但这会获得C函数
如何获取用户定义的过程语言函数,例如以 plpgsql
语言编写?
How to get user defined, procedural language functions, writen for example in plpgsql
language?
推荐答案
考虑:
select
pp.proname,
pl.lanname,
pn.nspname,
pg_get_functiondef(pp.oid)
from pg_proc pp
inner join pg_namespace pn on (pp.pronamespace = pn.oid)
inner join pg_language pl on (pp.prolang = pl.oid)
where pl.lanname NOT IN ('c','internal')
and pn.nspname NOT LIKE 'pg_%'
and pn.nspname <> 'information_schema';
另请参阅:在Postgresql中查找现有函数脚本的命令是什么?
使用 pg_get_functiondef
或直接从 pg_proc
中的 prosrc
列。关键思想是加入 pg_namespace
并过滤出PostgreSQL目录函数,这对于大多数用途可能已经足够了:
Use pg_get_functiondef
or the prosrc
column from pg_proc
directly. The key idea is to join on pg_namespace
and filter out the PostgreSQL catalog functions, which will probably be adequate for most purposes:
FROM pg_proc pp INNER JOIN pg_namespace ON (pp.pronamespace = pn.oid)
WHERE pn.nspname <> 'pg_catalog'
获取用户定义函数源代码的麻烦正在确定 user 的含义。可以创建许多类型的函数:
The trouble with obtaining the source code for user defined functions is deciding what user means. Many types of functions can be created:
- 使用
CREATE EXTENSION
的函数。
具有足够授权的超级用户可以在 pg_proc
中定义函数,但通常不能。
Superusers with sufficent grants can define functions in pg_proc
, but usually don't.
由于只有超级用户才能创建C语言函数,因此请排除它们。此类功能可以由管理员(而不是普通用户)自定义安装在特定数据库上。
Since only superusers can create C language functions, exclude them. Such functions can be custom-installed on a particular database by the admin, but not a normal user.
这篇关于获取所有程序性的,用户定义的功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!