PostgreSQL: dropping functions using information_scheme

Question

I have learned that I can select all of my functions with the same name using:

select *
from information_schema.routines
where routine_type='FUNCTION' and routine_name='test';

However, apparently that is a view, and when I try:

delete
from information_schema.routines
where routine_type='FUNCTION' and routine_name='test';

I get the message that I cannot delete from a view.

My initial reason for this approach is because I want a lazy drop function where I don’t have to name the parameters — I am developing a few new functions, and, at this state, the parameter list will be changeable.

Can I use this technique to drop functions with the same name? How?

a lazy way to delete function is using autocomplete in psql, not a deleting a row from catalog — Vao Tsun
– Vao Tsun, Commented Apr 10, 2017 at 7:55

user330315 · Accepted Answer · 2017-04-10 08:19:28Z

Never mess around with system catalogs.

You can create a little script to do what you want:

do
$$
declare
  proc_rec record;
  drop_ddl   text;
begin
  for proc_rec in SELECT n.nspname, p.proname, 
                         pg_get_function_arguments(p.oid) as args
                  FROM pg_catalog.pg_proc p  
                    JOIN pg_catalog.pg_namespace n on p.pronamespace = n.oid 
                  where n.nspname = 'public' -- don't forget the function's schema!
                    and p.proname = 'test'
  loop 
    drop_ddl := format('drop function %I.%I(%s)', proc_rec.nspname, proc_rec.proname, proc_rec.args);
    raise notice '%', drop_ddl;
    -- execute drop_ddl; -- uncomment to actually drop the function
  end loop;
end;
$$

If you need to do that more frequently, you could put that code into a function.

Collectives™ on Stack Overflow

PostgreSQL: dropping functions using information_scheme

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related