postgresql 如何查找PostgreSQL中是否存在函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24773603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find if a function exists in PostgreSQL?
提问by Pavel V.
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 deleteor grantthem, 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?
与表或序列不同，无法通过pg_class找到用户定义的函数。关于如何查找所有函数的列表以删除或授予它们存在一些问题，但是如何查找单个函数（具有已知名称和参数类型）并不是不言而喻的。那么如何判断一个函数是否存在呢？
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.
编辑：我想以自动方式在函数中使用它。哪个解决方案在性能方面是最好的？捕获错误非常昂贵，所以我想对我来说最好的解决方案是没有将错误转换为 false 的额外步骤，但我在这个假设中可能是错误的。
回答by Pavel Stehule
Yes, you cannot to find functions in pg_classbecause functions are stored on system table pg_proc
是的，您无法在其中找到函数，pg_class因为函数存储在系统表中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_procis simply
查询基于的自定义函数列表pg_proc很简单
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):
对函数存在的最简单和最快的测试是将（不带参数）转换为 regproc 或 regprocedure（带参数）：
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
或者你可以做一些类似的测试 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 = ) x
$function$
or you can use buildin functions:pg_get_function_arguments
或者您可以使用内置函数：pg_get_function_arguments
p.s. trick for simply orientation in system catalog. Use a psqloption -E:
ps 在系统目录中简单定位的技巧。使用一个psql选项-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)
回答by a_horse_with_no_name
I think the easiest way would be to use pg_get_functiondef().
我认为最简单的方法是使用pg_get_functiondef().
If it returns something, the function is there, otherwise the function does not exist:
如果它返回一些东西，则该函数存在，否则该函数不存在：
select pg_get_functiondef('some_function()'::regprocedure);
select pg_get_functiondef('some_function(integer)'::regprocedure);
The drawback is that it will produce an error if the function isn't there instead of simply returning an empty result. But this could e.g. be overcome by writing a PL/pgSQL function that catches the exception and returns false instead.
缺点是如果函数不存在，它会产生错误，而不是简单地返回一个空结果。但这可以通过编写一个 PL/pgSQL 函数来捕获异常并返回 false 来克服。
回答by Greg0ry
Based on @PavelStehule answer this is how I am checking this in my scripts (using postgres exceptionsand available exception codes)
基于@PavelStehule 的回答，这就是我在脚本中检查它的方式（使用postgres 异常和可用的异常代码）
DO $_$
BEGIN
BEGIN
SELECT 'some_schema.some_function(text)'::regprocedure;
EXCEPTION WHEN undefined_function THEN
-- do something here, i.e. create function
END;
END $_$;
