PostgreSQL:テーブル・カラム情報をクエリで取得する

 テーブル一覧を取得したりカラム一覧を取得して、あれこれしたい、というときは結構あると思います。PostgreSQLでも当然そういう要望はあってぐぐれば出てくるんですが、いちいちクエリ書くの面倒くさいのでViewとして定義して使っています。折角なので自分が使ってるやつをここにペタペタしときますので、ご参考までにどうぞ。

--DROP VIEW public.view_columnlist;
CREATE OR REPLACE VIEW public.view_columnlist AS
    SELECT      cls.oid          AS object_id
               ,nms.nspname      AS schema_name
               ,cls.relname      AS table_name
               ,des1.description AS table_logical_name
               ,att.attnum       AS column_no
               ,att.attname      AS column_name
               ,des2.description AS column_logical_name
               ,typ.typname      AS column_type
               ,COALESCE(idx.indisprimary, FALSE) AS column_is_primary_key
               ,att.attnotnull   AS column_is_not_null
               ,cls.relkind      AS object_type
    FROM        pg_catalog.pg_class       cls
    LEFT  JOIN  pg_catalog.pg_namespace   nms
            ON  cls.relnamespace = nms.oid
    LEFT  JOIN  pg_catalog.pg_attribute   att
            ON  cls.oid = att.attrelid
           AND  att.attnum > 0
    LEFT  JOIN  pg_catalog.pg_type typ
            ON  att.atttypid = typ.oid
    LEFT  JOIN  pg_catalog.pg_description des1
            ON  cls.oid = des1.objoid
           AND  des1.objsubid = 0
    LEFT  JOIN  pg_catalog.pg_description des2
            ON  cls.oid = des2.objoid
           AND  att.attnum = des2.objsubid
    LEFT  JOIN  (SELECT indrelid,UNNEST(indkey) AS pk_column_no,indisprimary FROM pg_catalog.pg_index WHERE indisprimary) idx
            ON  cls.oid = idx.indrelid
           AND  att.attnum = idx.pk_column_no
    WHERE       cls.relkind IN ('r','v','m')
      AND       nms.nspname = 'public'
    ORDER BY    schema_name
               ,table_name
               ,column_no
    ;

ALTER TABLE public.view_columnlist
    OWNER TO ユーザー名;


COMMENT ON VIEW view_columnlist IS 'VIEW_テーブルカラム一覧取得';
COMMENT ON COLUMN view_columnlist.object_id IS 'OID';
COMMENT ON COLUMN view_columnlist.schema_name IS 'スキーマ名';
COMMENT ON COLUMN view_columnlist.table_name IS 'テーブル名';
COMMENT ON COLUMN view_columnlist.table_logical_name IS 'テーブル論理名';
COMMENT ON COLUMN view_columnlist.column_no IS 'カラム番号';
COMMENT ON COLUMN view_columnlist.column_name IS 'カラム名';
COMMENT ON COLUMN view_columnlist.column_logical_name IS 'カラム論理名';
COMMENT ON COLUMN view_columnlist.column_type IS 'カラム型';
COMMENT ON COLUMN view_columnlist.object_type IS 'オブジェクト型';
COMMENT ON COLUMN view_columnlist.column_is_primary_key IS '主キー制約';
COMMENT ON COLUMN view_columnlist.column_is_not_null IS '非NULL制約';

 ながい!
 けどそんなに複雑なことはしてないので、簡単に解説しておきます。

◆ざっくり解説

 全てPostgreSQLのシステムカタログテーブルです。まあ当然ですよね。詳細は公式ドキュメントを参照していただくとして、何のテーブルで何のために結合しているかだけ羅列しておきます。尚、各システムカタログテーブルは内部的にoid列をもっており、これが各テーブル間を結合するためのキーとなっています。(ただしoid同士を直接結び付けるわけではない)

テーブル名 説明
pg_class テーブルやビューなどの一覧が定義されている。クエリのメインのテーブル。
pg_namespace 各オブジェクトの名前空間を管理する。
取得対象を絞り込むために使用。(システム系のものを排除)
pg_class.relnamespace と同じ値がこのテーブルのoidになっている。
pg_attribute テーブルやビューの列情報を管理する。
列番号や名前を取得するために使用。
このテーブルのattrelidがpg_class.oidと同じ値になっている。
列番号が1以上の行が列情報。
pg_type 型情報が定義されている。
各カラムの型情報を取得するために使用。
pg_attribute.atttypidと同じ値がこのテーブルのoidになっている。
pg_description 各オブジェクトのコメントを管理する。
テーブルやカラムのコメント情報を取得するために使用。
pg_class.oidと同じ値がこのテーブルのobjoidに格納される。
テーブル情報(objsubid = 0)とカラム情報の両方を取得。
pg_index インデックス情報の一部を管理する。
主キー情報を取得するために使用。(サブクエリでindisprimary(bool)を絞り込んで使用)

indrelidが対象となるテーブルのOID(pg_class.oid)、indkeyが列番号(pg_attribute.attnum)と同じ値になる。
※ただしindkeyは配列型なので扱いに注意

 各テーブルの結合部分がTry&Errorで作ってったので面倒くさかったです。とはいえ、ドキュメントと実テーブルの内容さえわかってしまえば、あとは時間の問題だけでした。

 残りについては各カラムにつけた別名を見て頂ければ意味はわかってもらえると思います。また、WHERE句にある「cls.relkind IN (‘r’,’v’,’m’)」についてですが、これはオブジェクトの種類を絞り込んでおり、具体的には「r:テーブル」「v:ビュー」「m:マテリアライズドビュー」となります。詳細は公式ドキュm(略)

◆実際に使ってみる

 まずはテキトーに、テーブルを用意します。こんな具合に。

image

image

image

 そして、先ほど作成したビューを開いてみます。

image

 ちゃんと取得できてますね。なんか表示されてないテーブルがある気がするかもしれませんが、目の錯覚です。

 つたないクエリですが、よければご参考までに。(結構前に作ったやつなので、環境によってはうまく動かないかもしれませんが)

◆補足

 テーブルやカラムのコメント欄を論理名としているのは、普段はフリーツールの「A5:SQL Mk-2」を使用している関係からです。このツールではコメントを論理名として扱ってくれます。
 尚、論理名を指定しつつコメントも設定したい場合は、コメント部分をタブ区切りにすると認識してくれます。(その場合は上のクエリもタブで区切るように手直ししてください。具体的には↓みたいに)

--               ,des2.description AS column_logical_name
,COALESCE(CASE POSITION(CHR(9) IN des2.description) 
		 WHEN 0 THEN des2.description
		 ELSE        SUBSTRING(des2.description FROM 0 FOR POSITION(CHR(9) IN des2.description))
		 END
		,att.attname) 
				 AS column_logical_name
,CASE POSITION(CHR(9) IN des2.description) 
	WHEN 0 THEN NULL
	ELSE        SUBSTRING(des2.description FROM POSITION(CHR(9) IN des2.description))
END              AS column_comment

 んでもってこんなクエリを発行しておきます。

COMMENT ON COLUMN my_favorite_koto_hono.ss_evaluation IS 'SS評価    やっぱりことほのなんだよちゅんなぁ(・8・)';

※わかりづらいですが、間のはスペースではなくタブです

 そうするとこうなります。

image

image

 ばっちりね!

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト /  変更 )

Google フォト

Google アカウントを使ってコメントしています。 ログアウト /  変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト /  変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト /  変更 )

%s と連携中