DROP FUNCTION without knowing the number/type of parameters?

Question

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.

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?

Answer 1

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.

EDIT: 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.