OID 別名型を使って PostgreSQL の内部情報を取り出す

こちらは [PostgreSQL Advent Calendar 2021](https://qiita.com/advent-calendar/2021/postgresql) の 21 日目の記事です。

今回は、PostgreSQL の内部情報を調べるときに役立つ、`regclass` や `regnamespace` などの [OID 別名型](https://www.postgresql.jp/document/current/html/datatype-oid.html) を紹介します。

PostgreSQL では、テーブルやスキーマなど、すべてのオブジェクトには OID と呼ばれる識別子が割り振られていて、システムカタログと呼ばれる内部情報が入ったテーブルでは、この OID が至るところに出てきます。

例えば、システムカタログ `pg_class` にはテーブルやインデックスなど、リレーションの情報が入っています。

```sql
=# SELECT oid, relname, relnamespace, relowner FROM pg_class;
  oid  |                    relname                    | relnamespace | relowner
-------+-----------------------------------------------+--------------+----------
 16391 | pgbench_accounts                              |         2200 |       10
 16394 | pgbench_branches                              |         2200 |       10
 16385 | pgbench_history                               |         2200 |       10
 16388 | pgbench_tellers                               |         2200 |       10
 16401 | pgbench_branches_pkey                         |         2200 |       10
 16403 | pgbench_tellers_pkey                          |         2200 |       10
 16405 | pgbench_accounts_pkey                         |         2200 |       10
  2619 | pg_statistic                                  |           11 |       10
  1247 | pg_type                                       |           11 |       10
  2836 | pg_toast_1255                                 |           99 |       10
(以下略)
```

`oid` 列はリレーション自体の OID、`relname` 列はリレーションの名前、`relnamespace` 列はリレーションを含むスキーマの OID、`relowner` 列はリレーションを所有するロールの OID を表します。が、OID はただの数字なので、スキーマやロールの OID を見ても、どのスキーマでどのロールか分かりません。

OID をオブジェクトの名前に変換するには、スキーマやロールの情報が入ったシステムカタログがあるので、それらと結合すればできます。

```sql
=# SELECT c.oid, c.relname, n.nspname, r.rolname FROM pg_class AS c
-#   JOIN pg_namespace AS n ON c.relnamespace = n.oid
-#   JOIN pg_roles AS r ON c.relowner = r.oid;

  oid  |                    relname                    |      nspname       | rolname
-------+-----------------------------------------------+--------------------+----------
 16391 | pgbench_accounts                              | public             | postgres
 16394 | pgbench_branches                              | public             | postgres
 16385 | pgbench_history                               | public             | postgres
 16388 | pgbench_tellers                               | public             | postgres
 16401 | pgbench_branches_pkey                         | public             | postgres
 16403 | pgbench_tellers_pkey                          | public             | postgres
 16405 | pgbench_accounts_pkey                         | public             | postgres
  2619 | pg_statistic                                  | pg_catalog         | postgres
  1247 | pg_type                                       | pg_catalog         | postgres
  2836 | pg_toast_1255                                 | pg_toast           | postgres
(以下略)
```

ただ、見てのとおり、SQL が少々長いと思ったと思います。これをシンプルに書き換えるのが OID 別名型です。

```sql
=# SELECT oid, relname, relnamespace::regnamespace, relowner::regrole FROM pg_class;
  oid  |                    relname                    |    relnamespace    | relowner
-------+-----------------------------------------------+--------------------+----------
 16391 | pgbench_accounts                              | public             | postgres
 16394 | pgbench_branches                              | public             | postgres
 16385 | pgbench_history                               | public             | postgres
 16388 | pgbench_tellers                               | public             | postgres
 16401 | pgbench_branches_pkey                         | public             | postgres
 16403 | pgbench_tellers_pkey                          | public             | postgres
 16405 | pgbench_accounts_pkey                         | public             | postgres
  2619 | pg_statistic                                  | pg_catalog         | postgres
  1247 | pg_type                                       | pg_catalog         | postgres
  2836 | pg_toast_1255                                 | pg_toast           | postgres
(以下略)
```

OID をオブジェクトに応じた OID 別名型、スキーマであれば `regnamespace` 型、ロールであれば `regrole` 型にキャストすることでオブジェクトの名前に変換できます。

また、逆にオブジェクトの名前を OID 別名型にキャストすると、OID に変換できるので、例えば、`public` スキーマに含まれるリレーションを取り出すとき、副問い合わせを使わずに条件式を書くことができます。

```sql
=# SELECT oid, relname, relnamespace::regnamespace, relowner::regrole FROM pg_class
-#   WHERE relnamespace = 'public'::regnamespace;
  oid  |        relname        | relnamespace | relowner
-------+-----------------------+--------------+----------
 16391 | pgbench_accounts      | public       | postgres
 16394 | pgbench_branches      | public       | postgres
 16385 | pgbench_history       | public       | postgres
 16388 | pgbench_tellers       | public       | postgres
 16401 | pgbench_branches_pkey | public       | postgres
 16403 | pgbench_tellers_pkey  | public       | postgres
 16405 | pgbench_accounts_pkey | public       | postgres
(7 行)
```

OID 別名型には全部で以下のものがあります。今回紹介した型以外ではリレーションの変換に `regclass` 型をよく使います。

|型名|参照先のシステムカタログ|オブジェクト|
|---|---|---|
|`regclass`|`pg_class`|リレーション|
|`regcollation`|`pg_collation`|照合順序 (バージョン 13 以降)|
|`regconfig`|`pg_ts_config`|テキスト検索設定|
|`regdictionary`|`pg_ts_dict`|テキスト検索辞書|
|`regnamespace`|`pg_namespace`|スキーマ|
|`regoper`|`pg_operator`|演算子|
|`regoperator`|`pg_operator`|引数の型をもつ演算子|
|`regproc`|`pg_proc`|関数|
|`regprocedure`|`pg_proc`|引数の型をもつ関数|
|`regrole`|`pg_authid`|ロール|
|`regtype`|`pg_type`|型|

OID 別名型を使わなくても SQL は書けますが、使えばシンプルに書くことができるので、知らなかった人は覚えておくと、何かの役に立つと思います。