Re: Dropping functions from pg_proc table w/ querry - Mailing list pgsql-admin

On 2019/10/30 12:05, Wells Oliver wrote:
> I have this query:
> 
> select * from pg_proc where probin like '%someoldstuff%';
> 
> Which shows abou 20 functions I want dropped. Is there a quick convenient SQL query for dropping all of these? Can't
quitefigure it out.
 

Something like this should do the trick:

     DO
     $$
       DECLARE
         rec RECORD;
       BEGIN
         FOR rec IN
            SELECT n.nspname || '.' || p.proname || '(' || pg_catalog.pg_get_function_arguments(p.oid)  ||')' AS func
              FROM pg_catalog.pg_proc p
         LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
             WHERE p.proname LIKE 'foo%'
         LOOP
           RAISE NOTICE 'Dropping: %', rec.func;
           EXECUTE 'DROP FUNCTION ' || rec.func;
         END LOOP;
       END;
     $$;


Use at your own risk etc. etc.


Regards

Ian Barwick


-- 
  Ian Barwick                   https://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services