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?

Decasyllabic

Hi Steinthor, would you mind accepting Erwin's answer instead? I'd like to delete mine in favor of his. – Tollhouse

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.

Tollhouse

Wow, thanks. This works beautifully. Guess it's not as trivial as I thought it would be :P – Decasyllabic

Yeah, although I missed pg_catalog.pg_get_function_arguments() that @Erwin's answer mentions. You might combine the two if you want it to look more elegant. – Tollhouse

Answer 2

Basic query

This query creates all necessary DDL statements:

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

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 checking plausibility.

Pass the function name case-sensitive and with no added double-quotes to match against pg_proc.proname.

The cast to the object identifier type regprocedure (oid::regprocedure), and then to text implicitly, produces function names with argument types, automatically double-quoted and schema-qualified according to the current search_path where needed. No SQL injection possible.

pg_function_is_visible(oid) restricts the selection to functions in the current search_path ("visible"). You may or may not want this.

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

Related:

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

Function

You can build a PL/pgSQL function around this to execute the statements immediately with EXECUTE.

Careful! It drops your functions!

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

Call:

SELECT f_delfunc('my_function_name');

The function returns the number of functions found and dropped if no exceptions are raised. 0 if none were found.

Further reading:

• 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

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

Fabiano

+1 Missed pg_catalog.pg_get_function_arguments() and did it manually :/ – Tollhouse

I find psql -E extremely useful. That's where I got the idea for pg_catalog.pg_get_function_arguments(p.oid). – Fabiano

This won't work if there are defaults for some arguments as pg_get_function_arguments returns the full varname vartype default syntax that is unusable in DROP FUNCTION and will raise a syntax error – Lashondra

@GhislainLeveque: Good catch! I fixed my answer with the more appropriate function pg_get_function_identity_arguments(). – Fabiano

@ErwinBrandstetter - Oh, interesting. Thanks for letting me know! – Tollhouse

Is it equivalent use proname instead of oid::regproc? – Possum

@JackDouglas: No, it's not. I added some more explanation. – Fabiano

Ah, OK, thanks. Is it also not a good idea to use concatenation rather that the format function then? – Possum

@JackDouglas: Since I am only using %s (plain string) with format() here (and both arguments are NOT NULL), you might as well just concatenate. Same thing. format() is a bit shorter, concatenation a bit faster. – Fabiano

@ErwinBrandstetter In 9.5 if function does not exist, f_delfunc throws error query string argument of EXECUTE is null . How to make f_delfunc to work if function does not exist ? – Custodial

@Andrus: I updated with an improved function to take care of this. – Fabiano

Answer 3

Improving original answer in order to take schema into account, ie. 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';

Durand

For some reason oid::regproc::text not always have the schema prefix – Suite

@basin: the text representation depends on the current search_path setting. The schema is only prefixed where necessary. You could SET LOCAL search_path = '' in your transaction to get all object names schema-qualified. (You'll have to schema-qualify all objects in the same transaction, too.) – Fabiano

Answer 4

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

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

Consequence

dangerous function. dryrun should be the default and not the other way – Sanity

I'm not sure it's a great idea doing a 'like' match. If you don't know the exact name of the function, why would you want to be dropping it? – Escritoire

'like' is needed when you want to kill group of functions not knowing their names beforehand. In my particular case all functions related to entity XXX and having names like XXX_create|update|delete|doSomting are defined in file XXX.sql - at the beginning of that script i have function call which removes all previous versions of the 'XXX' functions. This is particularly helpful when functions are renamed - e.g. XXX_doSomting -> XXX_doSomething. – Consequence

Answer 5

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

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

Shouldst

Answer 6

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:

drop function if exists my_func;

Documentation here.

Perhaps

This does not work in 11. I am using V11.10. Below statement when i execute in pgadmin DROP PROCEDURE IF EXISTS create_account; it looks for procedure or fun with zero arguments only. So it wont drop varying arguments ones though documentation states. – Superimposed

Answer 7

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.

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

Ecclesiasticus