如何获取函数参数列表(这样我就可以删除一个函数)

问题描述

我想让 SQL 删除 PostgreSQL 中的一个函数.我编写了 DROP FUNCTION 和一个来自 pg_proc 的 get 函数名.那不是问题.但是，如果我将参数留空，则不会删除该功能.

I want to get the SQL to drop a function in PostgreSQL. I write DROP FUNCTION and a get function name from pg_proc. That is not problem. However if I leave blank parameters it will not drop the function.

我查看了手册，上面写着然后我必须用它的参数识别函数才能删除它，例如 DROP FUNCTION some_func(text,integer) 不仅仅是 DROP FUNCTION some_func.

I checked the manual and there is written then I have to identify the function with its parameters to drop it, eg DROP FUNCTION some_func(text,integer) not just DROP FUNCTION some_func.

在哪里可以找到参数?在 pg_proc 表中的函数行中没有参数.那么如何让 SQL 删除函数呢?

Where can I find the parameters? In the function's row on in the pg_proc table there is no parameters. So how can I get the SQL to drop the function?

推荐答案

Postgres 有一个专门用于此目的的函数.随 Postgres 8.4 引入.手册:

Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual:

pg_get_function_identity_arguments(func_oid) ... 获取参数列表以识别函数(无默认值)...

pg_get_function_identity_arguments 返回参数列表识别功能所必需的，以它需要出现的形式例如，在 ALTER FUNCTION 内.这种形式省略了默认值.

pg_get_function_identity_arguments returns the argument list necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance. This form omits default values.

使用它(和 format()，随 Postgres 9.1 引入)，以下查询生成 DDL 语句以删除与您的搜索词匹配的函数:

Using that (and format(), introduced with Postgres 9.1), the following query generates DDL statements to drop functions matching your search terms:

SELECT format('DROP %s %I.%I(%s);'
            , CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
            , n.nspname
            , 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  p.proname = 'dblink'                     -- function name
-- AND n.nspname = 'public'                     -- schema name (optional)
-- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user
ORDER  BY 1;

系统目录 pg_proc 在 Postgres 11 中发生了变化.proisagg 被替换为 prokind，添加了真正的存储过程.你需要适应.见:

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. See:

• 如何删除我在 PostgreSQL 中的所有函数?

返回:

                  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 使用了重载函数.
有选择地运行 DROP 语句！

Found four matches in the example because dblink uses overloaded functions.
Run DROP statements selectively!

或者，您可以使用方便的转换到对象标识符类型regprocedure，它返回一个完整的函数签名，包括参数类型:

Alternatively, you can use the convenient cast to the object identifier type regprocedure which returns a complete function signature including argument types:

-- SET LOCAL search_path = '';  -- optional, to get all names schema-qualified
SELECT format('DROP %s %s;'
            , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
            , oid::regprocedure
             ) AS stmt
FROM   pg_catalog.pg_proc
WHERE  proname = 'dblink'   -- function name
ORDER  BY 1;

这篇关于如何获取函数参数列表(这样我就可以删除一个函数)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！