テーブル一覧を取得したりカラム一覧を取得して、あれこれしたい、というときは結構あると思います。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･)';
※わかりづらいですが、間のはスペースではなくタブです
そうするとこうなります。
ばっちりね！
![image[1]](https://od10z.files.wordpress.com/2019/12/image1.png "image[1]")
コノ世界ニ非ズ 