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

DROP FUNCTION without knowing the number/type of parameters?

提问by Steinthor.palsson

I keep all my functions in a text file with 'CREATE OR REPLACE FUNCTION somefunction'. So if I add or change some function I just feed the file to psql.

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

Now if I add or remove parameters to an existing function, it creates an overload with the same name and to delete the original I need type in all the parameter types in the exact order which is kind of tedious.

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

Is there some kind of wildcard I can use to DROP all functions with a given name so I can just add DROP FUNCTIONlines to the top of my file?

是否有某种通配符可以用来删除具有给定名称的所有函数，以便我可以在DROP FUNCTION文件顶部添加行？

采纳答案by Paul Bellora

You would need to write a function that took the function name, and looked up each overload with its parameter types from information_schema, then built and executed a DROPfor each one.

您需要编写一个函数，该函数采用函数名称，并从 中查找每个重载及其参数类型information_schema，然后DROP为每个重载构建并执行 a 。

EDIT:This turned out to be a lot harder than I thought. It looks like information_schemadoesn't keep the necessary parameter information in its routinescatalog. So you need to use PostgreSQL's supplementary tables pg_procand pg_type:

编辑：结果证明这比我想象的要困难得多。看起来information_schema它的routines目录中没有保留必要的参数信息。所以你需要使用 PostgreSQL 的补充表pg_proc和pg_type：

CREATE OR REPLACE FUNCTION udf_dropfunction(functionname text)
  RETURNS text AS
$BODY$
DECLARE
    funcrow RECORD;
    numfunctions smallint := 0;
    numparameters int;
    i int;
    paramtext text;
BEGIN
FOR funcrow IN SELECT proargtypes FROM pg_proc WHERE proname = functionname LOOP

    --for some reason array_upper is off by one for the oidvector type, hence the +1
    numparameters = array_upper(funcrow.proargtypes, 1) + 1;

    i = 0;
    paramtext = '';

    LOOP
        IF i < numparameters THEN
            IF i > 0 THEN
                paramtext = paramtext || ', ';
            END IF;
            paramtext = paramtext || (SELECT typname FROM pg_type WHERE oid = funcrow.proargtypes[i]);
            i = i + 1;
        ELSE
            EXIT;
        END IF;
    END LOOP;

    EXECUTE 'DROP FUNCTION ' || functionname || '(' || paramtext || ');';
    numfunctions = numfunctions + 1;

END LOOP;

RETURN 'Dropped ' || numfunctions || ' functions';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

I successfully tested this on an overloaded function. It was thrown together pretty fast, but works fine as a utility function. I would recommend testing more before using it in practice, in case I overlooked something.

我在一个重载的函数上成功地测试了这个。它很快被组合在一起，但作为一个实用函数运行良好。我建议在实际使用之前进行更多测试，以防我忽略了一些东西。

回答by Erwin Brandstetter

Basic query

基本查询

This query creates all necessary DDL statements. Later simplified with a cast to regprocedure, which displays as function with argument types, schema-qualified and/or double-quoted where necessary. Exactly what we need:

此查询创建所有必需的 DDL 语句。后来通过强制转换简化为regprocedure，它显示为具有参数类型、模式限定和/或必要时双引号的函数。正是我们需要的：

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 ..
                                     -- .. you may or may not want this

Output:

输出：

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);

Execute the commands (after a plausibility check).

执行命令（在合理性检查之后）。

The function name is case-sensitive and with no added double-quotes when passed as textparameter to match against pg_proc.proname.

函数名称区分大小写，并且在作为text参数传递以匹配pg_proc.proname.

The cast to the object identifier type regprocedure(oid::regprocedure) makes all identifiers safe against SQL injection(by way of maliciously malformed identifiers). When converting to text, the function name is double-quoted and schema-qualified according to the current search_pathautomatically where needed.

转换为对象标识符类型regprocedure( oid::regprocedure) 使所有标识符都可以安全地防止 SQL 注入（通过恶意格式错误的标识符）。转换为 时text，函数名称会search_path在需要时自动根据当前的情况进行双引号和模式限定。

pg_function_is_visible(oid)restricts the selection to functions in the current search_path. You may or may not want that. With the condition pg_function_is_visible(oid)in place, the function is guaranteed to be visible.

pg_function_is_visible(oid)将选择限制为当前 中的函数search_path。你可能想要也可能不想要。条件pg_function_is_visible(oid)到位后，函数保证可见。

If you have multiple functions of the same name in multiple schemas, or overloaded functions with various function arguments, allof those will be listed separately. You may want to restrict to specific schema(s) or specific function parameter(s) after all.

如果您在多个模式中有多个同名函数，或者具有各种函数参数的重载函数，则所有这些都将单独列出。毕竟，您可能希望限制为特定模式或特定功能参数。

Related:

有关的：

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

• 何时/如何绑定关于 search_path 的默认值表达式函数？

Function

功能

You can build a plpgsqlfunction around this to execute the statements immediately with EXECUTE. For Postgres 9.1or later: Careful! It drops your functions!

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

CREATE OR REPLACE FUNCTION f_delfunc(_name text, OUT func_dropped int) AS
$func$
DECLARE
   _sql text;
BEGIN
   SELECT count(*)::int
        , 'DROP FUNCTION ' || string_agg(oid::regprocedure::text, '; DROP FUNCTION ')
   FROM   pg_proc
   WHERE  proname = _name
   AND    pg_function_is_visible(oid)
   INTO   func_dropped, _sql;  -- only returned if trailing DROPs succeed

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

Call:

称呼：

SELECT * FROM f_delfunc('my_function_name');

Or just:

要不就：

SELECT f_delfunc('my_function_name');

This way you don't get the column namefunc_droppedfor the result column. May not matter.

这样你就不会得到结果列的列名func_dropped。可能没关系。

The function returns the number of functions found and dropped (no exception raised) - 0if none were found.

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

It assumes a (default) search_pathwhere pg_cataloghas not been moved around. See:

它假定一个（默认）search_path，其中pg_catalog尚未搬来搬去。看：

• 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

• search_path 如何影响标识符解析和“当前模式”
• 截断 Postgres 数据库中的所有表
• PostgreSQL 参数化 Order By / Limit in table 函数

For Postgres versions older than 9.1 or older variants of the function using regprocand pg_get_function_identity_arguments(oid)check the edit history of this answer.

对于早于 9.1 或更旧的函数变体的 Postgres 版本regproc，pg_get_function_identity_arguments(oid)请使用并检查此答案的编辑历史记录。

回答by Сухой27

Improving original answerin order to take schemainto account, ie. schema.my_function_name,

改进原始答案以考虑schema到，即。schema.my_function_name,

select
    format('DROP FUNCTION %s(%s);',
      p.oid::regproc, pg_get_function_identity_arguments(p.oid))
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
    p.oid::regproc::text = 'schema.my_function_name';

回答by Mark McKelvy

As of Postgres 10 you can drop functions by name only, as long as the names are unique to their schema. Just place the following declaration at the top of your function file:

从 Postgres 10 开始，您可以仅按名称删除函数，只要名称对于其架构来说是唯一的。只需将以下声明放在函数文件的顶部：

drop function if exists my_func;

Documentation here.

文档在这里。

回答by Muslimchik Kholjuraev

pgsql generates an error if there exists more than one procedure with the same name but different arguments when the procedure is deleted according to its name. Thus if you want to delete a single procedure without affecting others then simply use the following query.

如果根据名称删除过程时存在多个同名但参数不同的过程，pgsql 会产生错误。因此，如果您想删除单个过程而不影响其他过程，那么只需使用以下查询。

SELECT 'DROP FUNCTION ' || oid::regprocedure
FROM   pg_proc
WHERE  oid = {$proc_oid}

回答by Bo Guo

Here is the query I built on top of @Сухой27 solution that generates sql statements for dropping all the stored functions in a schema:

这是我在@Сухой27 解决方案之上构建的查询，它生成用于删除模式中所有存储函数的 sql 语句：

WITH f AS (SELECT specific_schema || '.' || ROUTINE_NAME AS func_name 
        FROM information_schema.routines
        WHERE routine_type='FUNCTION' AND specific_schema='a3i')
SELECT
    format('DROP FUNCTION %s(%s);',
      p.oid::regproc, pg_get_function_identity_arguments(p.oid))
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE
    p.oid::regproc::text IN (SELECT func_name FROM f);

回答by Xtra Coder

Slightly enhanced version of Erwin's answer. Additionally supports following

欧文答案的略微增强版本。另外支持以下

• 'like' instead of exact function name match
• can run in 'dry-mode' and 'trace' the SQL for removing of the functions

• 'like' 而不是精确的函数名称匹配
• 可以在“干模式”下运行并“跟踪”用于删除函数的 SQL

Code for copy/paste:

复制/粘贴代码：

/**
 * Removes all functions matching given function name mask
 *
 * @param p_name_mask   Mask in SQL 'like' syntax
 * @param p_opts        Combination of comma|space separated options:
 *                        trace - output SQL to be executed as 'NOTICE'
 *                        dryrun - do not execute generated SQL
 * @returns             Generated SQL 'drop functions' string
 */
CREATE OR REPLACE FUNCTION mypg_drop_functions(IN p_name_mask text,
                                               IN p_opts text = '')
    RETURNS text LANGUAGE plpgsql AS $$
DECLARE
    v_trace boolean;
    v_dryrun boolean;
    v_opts text[];
    v_sql text;
BEGIN
    if p_opts is null then
        v_trace = false;
        v_dryrun = false;
    else
        v_opts = regexp_split_to_array(p_opts, E'(\s*,\s*)|(\s+)');
        v_trace = ('trace' = any(v_opts)); 
        v_dryrun = ('dry' = any(v_opts)) or ('dryrun' = any(v_opts)); 
    end if;

    select string_agg(format('DROP FUNCTION %s(%s);', 
        oid::regproc, pg_get_function_identity_arguments(oid)), E'\n')
    from pg_proc
    where proname like p_name_mask
    into v_sql;

    if v_sql is not null then
        if v_trace then
            raise notice E'\n%', v_sql;
        end if;

        if not v_dryrun then
            execute v_sql;
        end if;
    end if;

    return v_sql;
END $$;

select mypg_drop_functions('fn_dosomething_%', 'trace dryrun');