PostgreSQL, drop custom functions

Question

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.

user330315 · Accepted Answer · 2014-01-26 10:35:05Z

1

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

answered Jan 26, 2014 at 10:35

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Wine Too Over a year ago

Yes, that now work exactly as expected and wanted. From pgAdmin and from .NET too. Since my program automatically created those functions at startup (if not exists) now I have again clean situation to catch backup with those functions back without any dependable jobs/issues. Thank you.

Collectives™ on Stack Overflow

PostgreSQL, drop custom functions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related