How to get function parameter lists (so I can drop a function)

Question

I want to get the SQL to drop a function in PostgreSQL. I write DROP FUNCTION and a get function name from pg_proc. That is not problem. However if I leave blank parameters it will not drop the function.

I checked the manual and there is written then I have to identify the function with its parameters to drop it, eg DROP FUNCTION some_func(text,integer) not just DROP FUNCTION some_func.

Where can I find the parameters? In the function's row on in the pg_proc table there is no parameters. So how can I get the SQL to drop the function?

Answer 1

Here is a variation on Erwin's answer that works for newer versions of PostgreSQL. I've also limited it to just overloaded functions.

;with cte_overloaded_functions as
(
    select n.nspname
        , p.proname
        , count(*) as ct
    from   pg_catalog.pg_proc p
    join   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    where 1=1
    --and n.nspname = 'namespace'
    --and p.proname = 'object_name'
    group by n.nspname
           , p.proname
    having count(*) > 1
)
select c.*, z.drop_statement
from cte_overloaded_functions as c
join pg_catalog.pg_proc p on p.proname = c.proname
join pg_catalog.pg_namespace n ON n.oid = p.pronamespace
cross join lateral
    (   select n.nspname || '.' || p.proname as fn_name
            , format('DROP %s %I.%I(%s);'
                        , CASE WHEN p.prokind = 'a' THEN 'AGGREGATE' ELSE 'FUNCTION' END
                        , n.nspname
                        , p.proname
                        , pg_catalog.pg_get_function_identity_arguments(p.oid)
                         ) AS drop_statement
    ) as z
;

Answer 2

Postgres has a dedicated function for that purpose. Introduced with Postgres 8.4. The manual:

pg_get_function_identity_arguments(func_oid) ... get argument list to identify a function (without default values) ...

pg_get_function_identity_arguments returns the argument list necessary to identify a function, in the form it would need to appear in within ALTER FUNCTION, for instance. This form omits default values.

Using that (and format(), introduced with Postgres 9.1), the following query generates DDL statements to drop functions matching your search terms:

SELECT format('DROP %s %I.%I(%s);'
            , CASE WHEN p.proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
            , n.nspname
            , p.proname
            , pg_catalog.pg_get_function_identity_arguments(p.oid)
             ) AS stmt
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  p.proname = 'dblink'                     -- function name
-- AND n.nspname = 'public'                     -- schema name (optional)
-- AND pg_catalog.pg_function_is_visible(p.oid) -- function visible to user
ORDER  BY 1;

The system catalog pg_proc changed in Postgres 11. proisagg was replaced by prokind, true stored procedures were added. You need to adapt. See:

• How to drop all of my functions in PostgreSQL?

Returns:

                  stmt
---------------------------------------------------
 DROP FUNCTION public.dblink(text);
 DROP FUNCTION public.dblink(text, boolean);
 DROP FUNCTION public.dblink(text, text);
 DROP FUNCTION public.dblink(text, text, boolean); 

Found four matches in the example because dblink uses overloaded functions.
Run DROP statements selectively!

Alternatively, you can use the convenient cast to the object identifier type regprocedure which returns a complete function signature including argument types:

-- SET LOCAL search_path = '';  -- optional, to get all names schema-qualified
SELECT format('DROP %s %s;'
            , CASE WHEN proisagg THEN 'AGGREGATE' ELSE 'FUNCTION' END
            , oid::regprocedure
             ) AS stmt
FROM   pg_catalog.pg_proc
WHERE  proname = 'dblink'   -- function name
ORDER  BY 1;

Answer 3

In Postgres 10, you can delete a function without knowing the list of parameters, as long as it is unique in its schema.

drop function if exists some_func;

See the docs.

Of course, if you have overloaded the function (or are trying to delete over multiple schemas), you will still need the above answers.

Answer 4

If you are working on an old previous version of postgres, for which pg_get_function_identity_arguments(func_oid) doesn't exist, I create my own function get the parameters from the function, you only need to pass the oid for the function, you need to deploy the function below to your postgres db.

CREATE OR REPLACE FUNCTION public.getFunctionParameter(functionOid oid)
  RETURNS text AS
$BODY$

declare
    t_paras text;
    paras oid[];
    res text :='(';
begin

select proargtypes into t_paras from pg_proc where oid=functionOid;
if t_paras is null or t_paras='' then
    return '()';
else
    paras:=string_to_array(t_paras,' ');
    for i in  array_lower(paras,1) .. array_upper(paras,1)
    loop
        raise notice 'para is %',paras[i];
        select format_type(paras[i]::oid,NULL) into t_paras;
        res:=res||t_paras||',';
    end loop;
    res:=substring(res from 1 for char_length(res)-1);
    res:=res||')';
    return res;
end if;

end 

    $BODY$
      LANGUAGE plpgsql ;

The function below will list the function name and parameters, change the schema name if you want to get function under some other schema, I am using public for example

    SELECT n.nspname||'.'||p.proname||public.getFunctionParameter(p.oid)
FROM    pg_proc p JOIN   pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname='public' 

You will the result like below

1 "public.getfunctionparameter(integer,text)"
2 "public.getfunctionparameter(oid)"

Answer 5

use pgadminIII and direct access to function list and right click it then select delete