我想让sql在Postgresql中删除一个函数.我从pg_proc写了DROP FUNCTION和get函数名.这没有问题.但是如果我留下空白参数,它不会丢弃该功能.
我检查了手册,然后写入,然后我必须使用其参数来标识函数,例如DROP FUNCTION some_func(text,integer)不仅仅是DROP FUNCTION some_func.
Postgres有一个专门的功能为此目的.
Per documentation:
pg_get_function_identity_arguments(func_oid)
… get argument list to identify a function (without default values)
…
pg_get_function_identity_arguments
returns the argument list
necessary to identify a function,in the form it would need to appear
in withinALTER FUNCTION
,for instance. This form omits default values.
使用该方法,以下查询生成DDL语句以删除与搜索词匹配的函数:
SELECT 'DROP ' || CASE WHEN p.proisagg THEN 'AGGREGATE ' ELSE 'FUNCTION ' END || quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ');' AS stmt FROM pg_catalog.pg_proc p JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'public' -- schema name (optional) AND p.proname ILIKE 'crosstab%' -- function name -- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user ORDER BY 1;
返回:
stmt --------------------------------------------------- DROP FUNCTION public.dblink(text); DROP FUNCTION public.dblink(text,boolean); DROP FUNCTION public.dblink(text,text); DROP FUNCTION public.dblink(text,text,boolean);
在该示例中找到四个匹配,因为dblink使用overloaded functions.有选择地运行DDL语句!