如何找到PostgreSQL中是否存在函数？ [英] How to find if a function exists in PostgreSQL?
问题描述
与表格或序列不同，用户定义的函数不能通过的pg_class 。 如何查找要删除的所有功能的列表或 grant 他们，但是如何找到一个单独的函数（具有已知的名称和参数类型）并不是不言而喻的。那么如何找到一个函数是否存在？
编辑：我想以自动方式在函数中使用它。哪种解决方案性能最佳？陷阱错误是非常昂贵的，所以我想对我来说最好的解决方案是没有额外的步骤来将错误转化为错误，但是我可能在这个假设中是错误的。
解决方案是的，您不能在 pg_class 中查找函数，因为函数存储在系统表 pg_proc $ b $ pre $ $ $ $ $ $ $
$ |名称|结果数据类型|参数数据类型|键入
-------- + -------------------- + --------------- --- + ---------------------- + --------
public | foo |整数|一个整数，b整数|正常
public | function_arguments |文字| oid |正常
（2行）
根据<$ c $查询自定义函数列表c> pg_proc 就是简单的
postgres =＃select p.oid :: regprocedure
来自pg_proc p
在p.pronamespace = n.oid中加入pg_namespace n
其中n.nspname不在（'pg_catalog'，'information_schema'）;
oid
-------------------------
foo（整数，整数）
function_arguments（ oid）
（2行）
没有参数）regproc或regprocedure（带参数）：
postgres =＃select'foo':: regproc;
regproc
---------
foo
（1 row）
postgres =＃选择'foox':: regproc;
错误：函数foox不存在
LINE 1：选择'foox':: regproc;
^
postgres =＃select'foo（int，int）':: regprocedure;
regprocedure
----------------------
foo（整数，整数）
（1 row）
postgres =＃select'foo（int，text）':: regprocedure;
错误：函数foo（int，text）不存在
LINE 1：选择'foo（int，text）':: regprocedure;
^
或者您可以使用 pg_proc
postgres =＃select exists（select * from pg_proc where proname ='foo'）;
存在
--------
t
（1行）
postgres =＃select exists（select *
from pg_proc
其中proname ='foo'
和function_arguments（oid）='integer，integer'）;
存在
--------
t
（1 row）
其中：
创建或替换函数public.function_arguments（oid）
RETURNS text语法sql AS $ function $
select（选择format_type（unnest（proargtypes），null）参数
from pg_proc其中oid = $ 1）x
$ function $
或者您可以使用buildin函数： pg_get_function_arguments
ps在系统目录中简单定向的技巧。使用 psql 选项 -E ：
[pavel @ localhost〜] $ psql -E postgres
psql（9.2.8，server 9.5devel）
键入help寻求帮助。
postgres =＃\df
********* QUERY **********
将n.nspname选择为Schema ，
p.proname为Name，
pg_catalog.pg_get_function_result（p.oid）为Result data type，
pg_catalog.pg_get_function_arguments（p.oid）asArgument data types ，
CASE
当p.proisagg THEN'agg'
当p.proiswindow THEN'window'
当p.prorettype ='pg_catalog.trigger':: pg_catalog.regtype THEN 'trigger'
ELSE'normal'
END作为Type
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible（p.oid）
AND n.nspname<> 'pg_catalog'
AND n.nspname<> 'information_schema'
ORDER BY 1，2，4;
**************************
函数列表
Schema |名称|结果数据类型|参数数据类型|键入
-------- + -------------------- + --------------- --- + ---------------------- + --------
public | foo |整数|一个整数，b整数|正常
public | function_arguments |文字| oid |正常
（2行）
Unlike tables or sequences, user-defined functions cannot be found through pg_class. There are questions on how find a list of all functions to delete or grant them, but how to find an individual function (with known name and argument types) is not self-evident from them. So how to find whether a function exists or not?
EDIT: I want to use it in a function, in automated manner. Which solution is the best performance-wise? Trapping errors is quite expensive, so I guess the best solution for me would be something without the extra step of translating error to false, but I might be wrong in this assumption.
Yes, you cannot to find functions in pg_class because functions are stored on system table pg_proc
postgres-# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------+------------------+----------------------+--------
public | foo | integer | a integer, b integer | normal
public | function_arguments | text | oid | normal
(2 rows)
Query for list of custom functions based on pg_proc is simply
postgres=# select p.oid::regprocedure
from pg_proc p
join pg_namespace n
on p.pronamespace = n.oid
where n.nspname not in ('pg_catalog', 'information_schema');
oid
-------------------------
foo(integer,integer)
function_arguments(oid)
(2 rows)
Most simply and fastest tests on functions existence are casting (without parameters) to regproc or regprocedure (with parameters):
postgres=# select 'foo'::regproc;
regproc
---------
foo
(1 row)
postgres=# select 'foox'::regproc;
ERROR: function "foox" does not exist
LINE 1: select 'foox'::regproc;
^
postgres=# select 'foo(int, int)'::regprocedure;
regprocedure
----------------------
foo(integer,integer)
(1 row)
postgres=# select 'foo(int, text)'::regprocedure;
ERROR: function "foo(int, text)" does not exist
LINE 1: select 'foo(int, text)'::regprocedure;
^
or you can do some similar with test against pg_proc
postgres=# select exists(select * from pg_proc where proname = 'foo');
exists
--------
t
(1 row)
postgres=# select exists(select *
from pg_proc
where proname = 'foo'
and function_arguments(oid) = 'integer, integer');
exists
--------
t
(1 row)
where:
CREATE OR REPLACE FUNCTION public.function_arguments(oid)
RETURNS text LANGUAGE sql AS $function$
select string_agg(par, ', ')
from (select format_type(unnest(proargtypes), null) par
from pg_proc where oid = $1) x
$function$
or you can use buildin functions:pg_get_function_arguments
p.s. trick for simply orientation in system catalog. Use a psql option -E:
[pavel@localhost ~]$ psql -E postgres
psql (9.2.8, server 9.5devel)
Type "help" for help.
postgres=# \df
********* QUERY **********
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 pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------------+------------------+----------------------+--------
public | foo | integer | a integer, b integer | normal
public | function_arguments | text | oid | normal
(2 rows)
这篇关于如何找到PostgreSQL中是否存在函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
