DROP FUNCTION without knowing the number/type of parameters?

ddl, dynamic-sql, plpgsql, postgresql, sql

Solution

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.

Problem

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?

Original source

Related problems