【PostgreSQL】関数一覧を確認する

\dfコマンドで関数一覧を確認する

PostgreSQLで関数一覧を確認するには、psqlコマンドラインツールの\dfコマンドを使う。

testdb=# \df total*
                           List of functions
 Schema |     Name     | Result data type | Argument data types | Type
--------+--------------+------------------+---------------------+------
 public | total_orders | numeric          | uid integer         | func
(1 row)

自作の関数だけを確認したい場合は、名前のパターンで絞り込むと見やすい。パターンを付けずに\dfを実行すると、publicスキーマにインストールされている拡張機能の関数もまとめて表示される。

testdb=# \df
                                       List of functions
 Schema |         Name          | Result data type |        Argument data types         | Type
--------+-----------------------+------------------+------------------------------------+------
 public | armor                 | text             | bytea                              | func
 public | crypt                 | text             | text, text                         | func
 public | dearmor               | bytea            | text                               | func
 ...
 public | total_orders          | numeric          | uid integer                        | func
(36 rows)

pgcrypto拡張をインストールしている場合、armor・crypt・digestのような拡張機能側の関数が自作の関数に混ざって表示される。自作の関数だけ見たい場合は名前のパターンで絞り込むか、次に説明するinformation_schema.routinesで拡張のスキーマを除外する。

参考: 【PostgreSQL】ビュー一覧を確認する

\df+で関数の本体も表示する

+を付けると、揮発性・並列実行の可否・所有者・ソースコードなど詳細な情報が表示される。

testdb=# \df+ total_orders
List of functions
 Schema |     Name     | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language |                       Source code                        | Description
--------+--------------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-----------------------------------------------------------+-------------
 public | total_orders | numeric          | uid integer         | func | volatile   | unsafe   | postgres   | invoker  |                   | sql      |  SELECT coalesce(sum(amount),0) FROM orders WHERE user_id = uid;  |

Source code列で関数の本体をそのまま確認できる。より整形された形式でソースコードだけを見たい場合は\sfを使う。

参考: 【PostgreSQL】psqlでクエリや関数定義をエディタで編集する

information_schema.routinesで関数一覧を確認する

SQLで関数一覧を取得する場合はinformation_schema.routinesを参照する。

testdb=# SELECT routine_schema, routine_name, data_type
testdb-# FROM information_schema.routines
testdb-# WHERE routine_schema = 'public' AND routine_name = 'total_orders';
 routine_schema | routine_name | data_type
----------------+--------------+-----------
 public         | total_orders | numeric
(1 row)

拡張機能のスキーマを除外したい場合は、routine_schema NOT IN ('pg_catalog', 'information_schema')に加えて、拡張機能をインストールしたスキーマも条件から除く必要がある。pgcryptoのようにpublicスキーマへ直接インストールされる拡張機能は、スキーマ名だけでは区別できない。

pg_procで関数一覧を確認する

権限に関係なくすべての関数を確認したい場合や、引数の型をより詳しく調べたい場合はpg_procを参照する。

testdb=# SELECT n.nspname, p.proname, pg_get_function_arguments(p.oid)
testdb-# FROM pg_proc p
testdb-# JOIN pg_namespace n ON n.oid = p.pronamespace
testdb-# WHERE n.nspname = 'public' AND p.proname = 'total_orders';
 nspname |   proname    | pg_get_function_arguments
---------+--------------+---------------------------
 public  | total_orders | uid integer
(1 row)

pg_get_function_argumentsは引数名・型・デフォルト値をまとめて人間が読みやすい形式で返す関数である。\dfもこの関数を内部的に利用してArgument data types列を組み立てている。

拡張機能がインストールした関数だけを調べる

拡張機能がどの関数を追加したかを調べたい場合は、pg_dependから拡張への依存関係をたどる。

testdb=# SELECT p.proname
testdb-# FROM pg_proc p
testdb-# JOIN pg_depend d ON d.objid = p.oid AND d.deptype = 'e'
testdb-# JOIN pg_extension e ON e.oid = d.refobjid
testdb-# WHERE e.extname = 'pgcrypto'
testdb-# ORDER BY p.proname
testdb-# LIMIT 5;
 proname
----------
 armor
 armor
 crypt
 dearmor
 decrypt
(5 rows)

deptype = 'e'は「拡張機能によって作られたオブジェクト」を意味する。自作関数と拡張機能に由来する関数を確実に区別したい場合はこの方法が正確である。