CODEWITHSUNDEEP
postgresql
Wine Too
Wine Too

Reputation: 4655

PostgreSQL, drop custom functions

In order to restore my data from pgdump I need to delete custom functions from database.
This function identify them well:

SELECT pp.proname
  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';

So, immediately after I hear for dynamic SQL and ensure that I can use it from .NET I try to apply it for that purpose.

do
$$
declare
   func_rec record;
begin
   for func_rec in (SELECT pp.proname 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')
   loop
     execute 'drop function '||func_rec.funcname||' cascade';
   end loop;
end;
$$

In meantime I found that for drop a function I have to supply it's parameters to DROP command.
Here is one example subquery on how those parameters can be retrieved:

 (SELECT typname FROM pg_type WHERE oid = funcrow.proargtypes[i])

Now remain a problem that I don't know how to put those in functional code which will add needed parameters to func_rec.funcname in order to delete such functions.

So, please help to get query for deleting custom functions from all functions.

Upvotes: 1

Views: 697

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions