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（略）

◆実際に使ってみる

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

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

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

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

◆補足

　テーブルやカラムのコメント欄を論理名としているのは、普段はフリーツールの「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･)';

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

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

　ばっちりね！

ブログに載せてたコード（の一部）をSyntaxHighlightに対応させました

 

　令和がはじまりましたね。ようこそ新時代。

　3月と4月は、炎上案件にぶち込まれて社畜ってた合間に、推し事でライブやら劇場版やらを見に行きまくってたせいで、すっかり更新をサボってしまいました。特に書けるネタが無かったというのもありますが。（ちなみにサンシャインの劇場版は40回見てきました）

 

　で、タイトルの通りですが。
　ブログに載せていたSQLのコードとかが、変な表示になってしまっていた件、遅くなりましたが修正しておきました。一部、というのは「コノ世界ニ非ズ」に改名して以降の分だけ、です。（コード載せてるのなんて最近のくらいしかないはずですが……）

　現在ならばこんな感じで綺麗にシンタックスハイライトされて表示されています。

 

　うちのブログはWordpressの無料版の「Wordpress.com」を使っており、こいつではシンタックスハイライトって出来ないのかな？（追加プラグインとかで）と思って、調べてみたら、普通にやり方が書いてあるサイトがあって判明しました。思い込みしないで、ちゃんと調べないとダメですね。

　で、やり方は、なんてことはないです、コード部分を [code]で囲ってやるだけでいいみたいです。
　また、SQLとかCSSとか、言語に応じてハイライトを対応させることも出来るみたいで、例えばSQLならば[code language="sql"]って書いて囲ってやればいいみたいです。楽ちんね！

　と、いうわけで今後コードを載せるときは、ちゃんとこの機能を使って載せていきたいと思います。それでは令和もよろしくお願いします。