PostgreSQL：自作ストアド関数の数を、組み込み言語別にSQLで調べる

昨日書いたとおりPL/Pythonをポータブル版PostgreSQL 9.4に入れたので、9.4以前の古いデータベースにPL/Pythonがあっても基本的に移行できるようになりました（PostgreSQLやPythonのバージョン間の差異は別として）。

自分にとって次の課題は、PL/Rを使ったWindows版PostgreSQL 9.3のデータベース。現時点ではPL/Rの公式サイトにWindows 32bit + PostgreSQL 9.4用のコンパイル済ライブラリがなく、すぐには移行できません。かといってPL/Rだけのためにずっと9.3のデータベースを持ち続けるのも面倒なので対策を考え始め、まず現状PL/Rの自作ストアド関数がどのくらいあるのか、確認する必要が出ました。

pgAdminのオブジェクトブラウザでの検索は、下のとおり関数名、定義文（ソース）、コメントの3つが対象で、今回のように言語で絞りたい場合は使えません。

こんな時のために関数名かコメントで判別できるようにしておくといいわけですが、それは置いといて、システムカタログに対して関数の情報を得るSQLを実行することで、言語別の自作ストアド関数の数などが分かります。以下そのメモ。実行環境は ↓のようにPL/Python、PL/Rといくつかの拡張を入れたWindows版PostgreSQL9.3。

SQLの例と結果

下のクエリで、記事タイトルどおり自作関数を言語別に数えます。pg_procというのが関数の情報の入ったシステムカタログで、その列prolangに言語がOID型で入っているので、言語名を得るため別のカタログpg_languageと結合。またWHERE句でスキーマ名から自作関数だけに絞ります（デフォルトスキーマのpublicに自作関数を入れない前提。ここにPostGISなど拡張機能の関数が多数入るので）。

SELECT lanname, count(*)
FROM pg_language lang
JOIN pg_proc p ON lang.oid = p.prolang
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('information_schema', 'pg_catalog', 'public')
GROUP BY 1
ORDER BY 1;

今回使った3つのシステムカタログの日本語ドキュメントは、下記にあります。その下は参考までに、自作関数に限らず全てを対象にしたクエリの結果。

集計クエリにせずpg_procの各列を表示するようにすれば、一関数について一行ずつ、名前や引数の型など全ての情報が表示されます。また下は、スキーマ別に集計する例。それぞれの言語がいくつのスキーマで使われているか調べています。

SELECT lanname, 
    count(DISTINCT nspname),
    array_agg(DISTINCT nspname ORDER BY nspname)
FROM pg_language lang
JOIN pg_proc p ON lang.oid = p.prolang
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('information_schema', 'pg_catalog', 'public')
GROUP BY 1
ORDER BY 1;

PL/Rストアドが数個程度なら一つずつ検討すればいいと思っていましたが、あちこちのスキーマに分散して多く存在することが分かり、全部見るのは大変そう…。何か別の方法を考えます。