sql - DROP FUNCTION 不知道参数的数量/类型？

Question

我将所有函数保存在一个包含'CREATE OR REPLACE FUNCTION somefunction' 的文本文件中。
因此，如果我添加或更改某些函数，我只需将文件提供给 psql。

现在，如果我向现有函数添加或删除参数，它会创建一个具有相同名称的重载并删除我需要的原始文件，我需要按照确切的顺序输入所有参数类型，这有点乏味。

是否有某种通配符可以用来 DROP 所有具有给定名称的函数，这样我就可以将 DROP FUNCTION 行添加到我的文件顶部？

Answer 1

基本查询

此查询创建所有必需的 DDL 语句:

SELECT 'DROP FUNCTION ' || oid::regprocedure
FROM   pg_proc
WHERE  proname = 'my_function_name'  -- name without schema-qualification
AND    pg_function_is_visible(oid);  -- restrict to current search_path

输出:

DROP FUNCTION my_function_name(string text, form text, maxlen integer);
DROP FUNCTION my_function_name(string text, form text);
DROP FUNCTION my_function_name(string text);

检查合理性后执行命令。

传递函数名称区分大小写且不添加双引号以匹配pg_proc.proname。

对象标识符类型的转换regprocedure (oid::regprocedure)，然后隐式到 text，生成带有参数类型的函数名，自动双引号和模式限定到当前 search_path在需要的地方。 没有 SQL 注入(inject)可能。

pg_function_is_visible(oid)将选择限制为当前 search_path 中的函数(“可见的”)。您可能需要也可能不需要。

如果您在多个架构中有多个同名函数，或者具有不同函数参数的重载函数，所有 将单独列出。您可能希望限制为特定模式或特定函数参数。

相关:

• When / how are default value expression functions bound with regard to search_path?

函数

您可以围绕它构建一个plpgsql 函数来立即执行带有EXECUTE 的语句。 .对于 Postgres 9.1 或更高版本:小心!它会降低您的功能!

CREATE OR REPLACE FUNCTION f_delfunc(_name text, OUT functions_dropped int)
   LANGUAGE plpgsql AS
$func$
-- drop all functions with given _name in the current search_path, regardless of function parameters
DECLARE
   _sql text;
BEGIN
   SELECT count(*)::int
        , 'DROP FUNCTION ' || string_agg(oid::regprocedure::text, '; DROP FUNCTION ')
   FROM   pg_catalog.pg_proc
   WHERE  proname = _name
   AND    pg_function_is_visible(oid)  -- restrict to current search_path
   INTO   functions_dropped, _sql;     -- count only returned if subsequent DROPs succeed

   IF functions_dropped > 0 THEN       -- only if function(s) found
     EXECUTE _sql;
   END IF;
END
$func$;

调用:

SELECT f_delfunc('my_function_name');

如果没有引发异常，该函数返回找到和删除的函数数。 0 如果没有找到。

进一步阅读:

• How does the search_path influence identifier resolution and the "current schema"
• Truncating all tables in a Postgres database
• PostgreSQL parameterized Order By / Limit in table function

对于早于 9.1 的 Postgres 版本或使用 regproc 和 pg_get_function_identity_arguments(oid) 的函数变体检查此答案的编辑历史。