目的
オブジェクト識別子(以下、OID)とキャストについては、すでに詳細な解説記事があります。
オブジェクト識別子を活用する | Let's Postgres
本エントリではこの解説記事をベースにしつつ、理解し辛い点を補足します。
本エントリの目次を示します。
解説
オブジェクト識別子(OID)
OIDはPostgreSQLのシステムテーブル(システムカタログ)の主キーとして使用されています。また、OIDによってオブジェクト(テーブルやインデックス、関数、演算子、データ型定義など)を一意に識別できます。例えば、pg_classカタログではOIDとテーブル・インデックス・ビューなどが紐付けられています。
参考:50.11. pg_class
OIDの別名データ型(オブジェクト識別子データ型)とキャスト
OIDには別名データ型(オブジェクト識別子データ型)があります。この一覧はマニュアルに記載されています。
8.18. オブジェクト識別子データ型
型名 参照 説明 値の例 oid すべて 数値オブジェクト識別子 564182 regproc pg_proc 関数名 sum regprocedure pg_proc 引数の型を持つ関数 sum(int4) regoper pg_operator 演算子名 + regoperator pg_operator 引数の型を持つ演算子 *(integer,integer) or -(NONE,integer) regclass pg_class リレーション名 pg_type regtype pg_type データ型名 integer regrole pg_authid ロール名 smithee regnamespace pg_namespace 名前空間名 pg_catalog regconfig pg_ts_config テキスト検索設定 english regdictionary pg_ts_dict テキスト検索辞書 simple
オブジェクト識別子データ型は型キャストの構文で型に指定します。参考までに、型キャストの構文を引用します。
PostgreSQLは型キャストに2つの等価な構文を受け付けます。
CAST ( expression AS type ) expression::typeCAST構文はSQLに準拠したものです。 ::を使用する構文は、PostgreSQLで伝統的に使用されている方法です。
4.2. 評価式
ここで重要なのが、オブジェクト識別子データ型であっても、キャストの挙動は変わらない、ということです。これに関連して、解説記事では以下の記述があります。
例えばregclassへ(スキーマ名.)テーブル名を渡す(キャストする)と、OIDへ変換してくれます。逆にOIDを渡すと(スキーマ名.)テーブル名に変換してくれます。
オブジェクト識別子を活用する | Let's Postgres
この記述からはregclassへのキャストでOIDとテーブル名を相互に変換できるように見えますが、実際にはregclassへのキャストはregclass型へ変換しているにすぎません。
実際の挙動から確認します。
-- WHERE句を見ると、'pg_class'::regclassでOIDに変換されて見える。 SELECT oid, relname FROM pg_class WHERE oid = 'pg_class'::regclass; oid | relname ------+---------- 1259 | pg_class -- だが、SELECTしてみるとOIDを取得できない。 SELECT 'pg_class'::regclass; regclass ---------- pg_class -- OIDを取得するにはregclassへのキャストを、さらにoidへキャストする必要がある。 SELECT 'pg_class'::regclass::oid; oid ------ 1259 -- テーブル名 -> regclass -> oid となるわけでもない。 SELECT 'pg_class'::regclass::regclass; regclass ---------- pg_class
regclassへのキャストで、SELECT句ではOIDを取得できず、WHERE句ではOIDに変換できたのは、暗黙的な型変換が関係しています。
まず、pg_castカタログからregclassのデータ型変換パスを確認します。
SELECT castsource::regtype, casttarget::regtype, castcontext FROM pg_cast WHERE castsource = 'regclass'::regtype OR casttarget = 'regclass'::regtype ORDER BY castsource, casttarget; castsource | casttarget | castcontext -------------------+------------+------------- bigint | regclass | i smallint | regclass | i integer | regclass | i text | regclass | i oid | regclass | i character varying | regclass | i regclass | bigint | a regclass | integer | a regclass | oid | i
この結果より、text ⇒ regclass, regclass ⇒ oidへの変換があることが分かります。
"SELECT 'pg_class'::regclass;"は"text ⇒ regclass"でregclass型へ変換し、その結果としてpgclass.relnameを出力したと考えられます。
次に、左辺もしくは右辺がoid型の"="演算子について、pg_operatorカタログから確認します。
SELECT oprname, oprleft::regtype, oprright::regtype FROM pg_operator WHERE oprname = '=' AND ( oprleft = 'oid'::regtype OR oprright = 'oid'::regtype); oprname | oprleft | oprright ---------+---------+---------- = | oid | oid
"="演算子は左辺・右辺共にoid型であることを要求していることが分かります。
"SELECT oid, relname FROM pg_class WHERE oid = 'pg_class'::regclass;"の場合、WHERE句の左辺の"oid"列がoid型ですので、右辺のregclass型が暗黙的にoid型へ変換されたものと考えられます。
PostgreSQLのマニュアルでも、以下の2つのSQLはほぼ同等であることが示されています。
-- オブジェクト識別子データ型のキャストを利用したSQL SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass; -- オブジェクト識別子データ型のキャストを利用しないSQL SELECT * FROM pg_attribute WHERE attrelid = ( SELECT oid FROM pg_class WHERE relname = 'mytable');
ただし、後者のSQLはスキーマの考慮がされておらず、完全に同等ではありません。スキーマパスを考慮する前者のSQLのほうが望ましいとされています。本節の主題から外れるため、詳細は割愛します。詳しくはマニュアルをご参照ください。
参考:8.18. オブジェクト識別子データ型
オブジェクト識別子データ型のキャストの使いどころ
オブジェクト識別子データ型のキャストの使い方の一例として、システムカタログの参照が挙げられます。システムカタログにはPostgreSQL固有の情報が格納されています。例えば、テーブルや列の情報などのスキーマメタデータ、及び内部的な情報です。
pg_locksビュー(50.65. pg_locks)で現在のロックを確認するSQLの例がありました。
SELECT l.pid, l.granted, d.datname, l.locktype, relation, relation::regclass, transactionid, l.mode FROM pg_locks l LEFT JOIN pg_database d ON l.database = d.oid WHERE l.pid != pg_backend_pid() ORDER BY l.pid;※SQLを見やすくするため、オリジナルに対して整形を行っています。
【PostgreSQL-0007】ロック(Lock)の確認方法とデットロックの解除 - OSS ERP Compiere Distribution Lab
relation列は参照先がpg_class.oidとなっており、オブジェクト識別子データ型の一覧より、regclassでキャスト可能なことが分かります。
これに対し、database列は参照先がpg_database.oidとなっており、オブジェクト識別子データ型の一覧にありません。そこで、pg_locks.databaseとpg_database.oidをキーに結合して参照しています。
regprocとregoper別名型の制限
マニュアルに以下の記述があります。
regprocとregoper別名型は、一意な(オーバーロードしていない)名前のみを入力として受け入れるため、これらの使用には限度があります。 ほとんどの場合、regprocedureまたはregoperatorを使用するのが適切です。
8.18. オブジェクト識別子データ型
この記述を実際の挙動から確認します。
-- pg_procカタログでpiとsumの定義数を確認する SELECT proname, count(*) FROM pg_proc WHERE proname IN ('pi', 'sum') GROUP BY proname; proname | count ---------+------- pi | 1 sum | 8 -- pg_procカタログ内で関数名が一意な場合 SELECT 'pi'::regproc; regproc --------- pi -- pg_procカタログ内で関数名が一意でない場合 SELECT 'sum'::regproc; ERROR: more than one function named "sum" -- 引数の型を指定してregprocedureでキャストする SELECT 'sum(integer)'::regprocedure; regprocedure -------------- sum(integer)
regprocでは一意な名前を指定しないとエラーになりました。このような場合、マニュアルに記載の通り、引数の型を指定してregprocedureでキャストする必要があります。
まとめ
オブジェクト識別子データ型のキャストを活用することで、SQLを正確にそして簡潔に記述することができます。オブジェクト識別子データ型は必要な全てのパターンが用意されているわけではないため、参照先と結合することが必要な場合もあります。