DROP FUNCTION不知道参数的数量/类型? [英] DROP FUNCTION without knowing the number/type of parameters?

问题描述

我使用'CREATE OR REPLACE FUNCTION somefunction'将所有功能保存在文本文件中. 因此，如果我添加或更改某些功能，我只会将文件提供给psql.

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.

现在，如果我在现有函数中添加或删除参数，它将创建一个具有相同名称的重载，并按照确切的顺序删除所有参数类型中我需要的原始类型.

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.

是否可以使用某种通配符来删除具有给定名称的所有功能，以便仅在文件顶部添加DROP FUNCTION行?

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

推荐答案

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

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 DROP for each one.

编辑:事实证明，这比我想象的要难得多.看来information_schema没有在其routines目录中保留必要的参数信息.因此，您需要使用PostgreSQL的补充表pg_proc和pg_type:

This turned out to be a lot harder than I thought. It looks like information_schema doesn't keep the necessary parameter information in its routines catalog. So you need to use PostgreSQL's supplementary tables pg_proc and 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.

这篇关于DROP FUNCTION不知道参数的数量/类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！