PostgreSQL: すべてのfunctionを一括で削除したい

 

　前に書いた「PostgreSQL：複数定義があるFunctionの一覧をクエリで取得する」って記事の続き的なもの。すっかり書くの忘れてましたが。

　さて、上にあげた記事のように、うちのPJではめちゃくちゃFunctionが多くて色々と問題が起きておりまして。そのうえ、環境ごとにDBが乱立してしまい、あるDBのFunctionが最新版では無かったりして環境ごとに挙動が異なり、混乱を招いたりするのは日常茶飯事でした。
　もう完全に管理できてへんやんけ！という本音は横に置いておき、ひとまず事態を収めねばなりません。幸い、FunctionそのものはテキストファイルにしてSVN上で保存してあったので、最新版を当て直すだけならば、さほど難しくありません。

　しかし、どうせ当て直しするなら消し忘れの古い定義のFunctionとか、不要になったFunctionとか、一括して整理したいなぁと思いまして。なんか一括で消せないのかなーと思って調べたのですが、特にそういう機能は無いらしく、1個ずつ手動で消すのはあまりにも面倒だったので、↓みたいな方法で消すことにしました。

 

◆FunctionでFunctionを消す

　やり方は単純で、システムテーブルからFunction名とか引数定義とかを引っ張ってきて、動的SQL文を作って削除するってのを繰り返すだけです。らくちんね。
　自分自身を消さないようにしたり、対象を絞るようにするのを忘れないでください。例えば弊社の場合は、関数名の先頭に「fn_」ってつけることが規則になっていたので、絞り込むのは簡単でした。

・システムテーブルからFunction名と引数定義を取得する

SELECT
    nmsp.nspname || '.' || pgp.proname || '(' || pg_catalog.oidvectortypes(pgp.proargtypes) || ')' AS functionName
FROM
    pg_catalog.pg_proc pgp 
    LEFT JOIN pg_catalog.pg_namespace nmsp
    ON nmsp.oid = pgp.pronamespace
WHERE
        pgp.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
    AND NOT pgp.proisagg
    AND nmsp.nspname = 'public'
    AND pgp.proname like 'fn_%'
;

　これを実行すると、publicに定義されてる、名前がfn_ではじまるfunction一覧が引数の型定義とセットで手に入ります。「名前(引数1の型, 引数2の型)」みたいな形で。あとはこれを、「DROP FUNCTION 名前(引数1の型, 引数2の型);」という文字列に加工してやって、ループで動的SQLとして実行し続ければOKです。

・動的SQLとしてDROP文を実行する

DECLARE
    rec_funcname_list  record;
    txt_sql            text;
BEGIN
    FOR rec_funcname_list IN
    SELECT
            nmsp.nspname || '.' || pgp.proname || '(' || pg_catalog.oidvectortypes(pgp.proargtypes) || ')' AS functionName
    FROM
            pg_catalog.pg_proc pgp
            LEFT JOIN pg_catalog.pg_namespace nmsp 
                   ON nmsp.oid = pgp.pronamespace
    WHERE   pgp.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
      AND   NOT pgp.proisagg
      AND   nmsp.nspname = 'public' 
      AND   pgp.proname like 'fn_%'
    LOOP
        txt_sql = 'DROP FUNCTION ' || rec_funcname_list.functionName;
        execute txt_sql;
    END LOOP;
END;

　エラー処理とかは省略してます。実際に使ってるやつからは若干改変してます。（ほんとちょとだけ）
　先に書いたSQLの結果件数だけループさせて、「execute txt_sql;」で実行し続けるだけの簡単なfunctionです。

　使う場合には自己責任でどうぞ。（事前に、SELECT部分だけ実行してちゃんと目的のものだけになってるか、は目を通しておいたほうがいいと思います）