如何查找PostgreSQL中是否存在函数？

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?

与表或序列不同，无法通过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.

编辑：我想以自动方式在一个函数中使用它。哪种解决方案性能最佳？陷阱错误是非常昂贵的，所以我想对我来说最好的解决方案是没有额外的步骤将错误转换为错误，但我可能在这个假设中错了。

2 个解决方案

#1

Yes, you cannot to find functions in pg_class because 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_proc is 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 = $1) x
$function$

or you can use buildin functions:pg_get_function_arguments

或者您可以使用buildin函数：pg_get_function_arguments

p.s. trick for simply orientation in system catalog. Use a psql option -E:

附：在系统目录中简单定位的技巧。使用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)

#2

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.

缺点是如果函数不存在则会产生错误而不是简单地返回空结果。但这可能是例如通过编写捕获异常并返回false的PL / pgSQL函数来克服。

#1