Subject: Re: Dropping functions from pg_proc table w/ querry
To: Wells Oliver <wells.oliver@gmail.com>,
 pgsql-admin <pgsql-admin@postgresql.org>
References: 
 <CAOC+FBWN5bf56SwUbnhbymt0c_bmS6+-C1aiPwNOPe089+DqHA@mail.gmail.com>
From: Ian Barwick <ian.barwick@2ndquadrant.com>
Message-ID: <6ebc3e08-a2e3-b8f9-773e-0bc24e79e75f@2ndquadrant.com>
Date: Wed, 30 Oct 2019 12:26:46 +0900
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAOC+FBWN5bf56SwUbnhbymt0c_bmS6+-C1aiPwNOPe089+DqHA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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 quite figure 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