Drop Function Without Knowing the Number/Type of Parameters

DROP FUNCTION without knowing the number/type of parameters?

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.

How to drop function from all schemas

You could try this:

do $$
declare s record;
begin
  for s in select schema_name from information_schema.schemata loop
    execute 'drop function if exists ' || s.schema_name || '.yourfunctionname()';
  end loop;
end; $$;

Drop function in Postgres

DROP FUNCTION "updateStat"(timestamp without time zone, integer, integer);

You might want to consider using CREATE OR REPLACE FUNCTION... in your create script, so you can redefine it without needing to drop it each time.

PostgreSQL: How to DROP FUNCTION IF EXISTS without specifying parameters?

In Postgres functions can be overloaded, so parameters are necessary to distinguish overloaded functions. To unambiguously identify a function you can put only types of its parameters.

DROP FUNCTION IF EXISTS Foo(INT);

Drop all functions in a namespace? (Execute generated DDL commands?)

The system catalogs changed in Postgres 11! (prokind instead of proisagg) See:

• How to drop all of my functions in PostgreSQL?

---

Could look like this:

CREATE OR REPLACE FUNCTION public.f_delfunc(_schema text, _del text = '')
  RETURNS text AS
$func$
DECLARE
   _sql   text;
   _ct    text;
BEGIN
   SELECT INTO _sql, _ct
          string_agg('DROP '
                   || CASE p.proisagg WHEN true THEN 'AGGREGATE '
                                                ELSE 'FUNCTION ' END
                   || quote_ident(n.nspname) || '.' || quote_ident(p.proname)
                   || '('
                   || pg_catalog.pg_get_function_identity_arguments(p.oid)
                   || ')'
                    , E'\n'
          )
          ,count(*)::text
   FROM   pg_catalog.pg_proc p
   LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
   WHERE  n.nspname = _schema;
   -- AND p.proname ~~* 'f_%';                     -- Only selected funcs?
   -- AND pg_catalog.pg_function_is_visible(p.oid) -- Only visible funcs?

   IF _ct = '0' THEN
      RETURN 'Found 0 functions to delete';
   ELSIF lower(_del) = 'del' THEN                        -- Actually delete!
      EXECUTE _sql;
      RETURN _ct || E' functions deleted:\n' || _sql;
   ELSE                                               -- Else only show SQL.
      RETURN _ct || E' functions to delete:\n' || _sql;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call to show:

SELECT f_delfunc('public');         -- 2nd parameter is covered by default.

Call to delete:

SELECT f_delfunc('public','del');

Major points

• You need dynamic SQL for that. Use a plpgsql function or a DO statement (PostgreSQL 9.0+) with EXECUTE.

• Note the use of the functions pg_get_function_identity_arguments() and pg_function_is_visible. The latter can be omitted. It's a safeguard so you don't delete functions outside of the current user's search_path.

• I added a "safe mode". Only delete if $2 = 'del'. Else only show generated SQL.

• Be aware that the function will delete itself if it lives in the schema you delete from.

• I also added quote_ident() to safeguard against SQLi. Consider the following:

CREATE FUNCTION "; DELETE FROM users;"()
  RETURNS int AS
'SELECT 1'
  LANGUAGE sql;

• This fails if there are dependencies on any involved function. May be resolved by adding CASCADE, but I did not do that here, since it makes the function more dangerous, yet.

Related:

• DROP FUNCTION without knowing the number/type of parameters?
• How to get function parameter lists (so I can drop a function)

Is there any formal difference at all between PostgreSQL functions with OUT parameters and with TABLE results?

\df public.f_* does this

select
    n.nspname as "Schema",
    p.proname as "Name",
    pg_catalog.pg_get_function_result(p.oid) as "Result data type",
    pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
    case
        when p.proisagg then 'agg'
        when p.proiswindow then 'window'
        when p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype then 'trigger'
        else 'normal'
    end as "Type"
from
    pg_catalog.pg_proc p
    left join
    pg_catalog.pg_namespace n on n.oid = p.pronamespace
where
    p.proname ~ '^(f_.*)$'
    and n.nspname ~ '^(public)$'
order by 1, 2, 4;

which returns this

                                          List of functions
 Schema | Name |       Result data type        |            Argument data types             |  Type  
--------+------+-------------------------------+--------------------------------------------+--------
 public | f_1  | integer                       | v1 integer, OUT v2 integer                 | normal
 public | f_2  | TABLE(v2 integer)             | v1 integer                                 | normal
 public | f_3  | record                        | v1 integer, OUT v2 integer, OUT v3 integer | normal
 public | f_4  | TABLE(v2 integer, v3 integer) | v1 integer                                 | normal
(4 rows)

To drop a function it is necessary to pass its input (IN and INOUT) arguments data types. Then I guess the function name and its input arguments data types do form its signature. And to change the returned data type it is necessary to first drop it and recreate.

How to check which function uses a type?

You can find all dependencies in the system catalog pg_depend.

This returns all functions depending on the type. I.e. not only those with the type in the RETURNS clause, but also those with the type as function parameter:

SELECT objid::regproc                            AS function_name
     , pg_get_functiondef(objid)                 AS function_definition
     , pg_get_function_identity_arguments(objid) AS function_args
     , pg_get_function_result(objid)             AS function_returns
FROM   pg_depend
WHERE  refclassid = 'pg_type'::regclass
AND    refobjid   = 'my_type'::regtype    -- insert your type name here
AND    classid    = 'pg_proc'::regclass;  -- only find functions

This also works for table functions:

...
RETURNS TABLE (foo my_type, bar int)

Using system catalog information functions.

There may be other dependencies (not to functions). Remove the last WHERE condition from my query to test (and adapt the SELECT list, obviously).

And there is still the possibility of the type being used explicitly (in a cast for instance) in queries in the function body or in dynamic SQL. You can only identify such use cases by parsing the text of the function body. There are no explicit dependencies registered in the system.

Related:

• How to get function parameter lists (so I can drop a function)
• DROP FUNCTION without knowing the number/type of parameters?

PostgreSQL, drop custom functions

Because functions can be overloaded in Postgres, you need to include the function's signature in the drop.

Suppose you have these functions:

get_answer(p1 integer);
get_answer(p1 integer, p2 integer);

then Postgres wouldn't know which one to drop when using drop function get_answer;.

Luckily Postgres has a function to format the arguments so that they can be used for that purpose: pg_get_function_identity_arguments.

So you need to change your select to:

SELECT pp.proname||'('||pg_get_function_identity_arguments(pp.oid)||')' as funcname
  FROM pg_proc pp
 INNER JOIN pg_namespace pn on (pp.pronamespace = pn.oid)
 INNER JOIN pg_language pl on (pp.prolang = pl.oid)
 WHERE pl.lanname NOT IN ('c','internal') 
   AND pn.nspname NOT LIKE 'pg_%'
   AND pn.nspname <> 'information_schema';

---

Related Topics