与表或序列不同,用户定义函数不能通过pg_class找到。关于如何找到所有函数的列表以删除或授予它们,存在一些问题,但是如何找到单个函数(具有已知的名称和参数类型)并不是不言而喻的。那么如何判断一个函数是否存在呢?
编辑:我想在一个函数中使用它,以自动的方式。哪个解决方案的性能最好?捕获错误是相当昂贵的,所以我想对我来说最好的解决方案是没有将错误转换为false的额外步骤,但是我的这个假设可能是错误的。
是的,你不能在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)
查询基于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)
对函数是否存在的最简单和最快的测试是将函数类型转换为(不带参数)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;
^
或者你可以对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)
地点:
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$
或者可以使用内置函数:pg_get_function_arguments
注。在系统目录中简单定位的技巧。使用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)
我认为最简单的方法是使用pg_get_functiondef()
。
如果返回值,则表示该函数存在,否则表示该函数不存在:
select pg_get_functiondef('some_function()'::regprocedure);
select pg_get_functiondef('some_function(integer)'::regprocedure);
的缺点是,如果函数不存在,它将产生一个错误,而不是简单地返回一个空结果。但这可以通过编写一个捕获异常并返回false的PL/pgSQL函数来克服。
基于@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 $_$;
迟到了,但它可能是这样的(不要使用select
代替perform
,如果你不使用的结果,否则你会得到一个错误的抱怨:
错误:查询结果数据没有目的地
所以下面的代码可以工作:
DO $$
BEGIN
BEGIN
perform pg_get_functiondef('some_function()'::regprocedure);
raise notice 'it exists!';
EXCEPTION WHEN undefined_function THEN
raise notice 'Does not exist';
END;
END $$;