システムの更新プロジェクトで既存のデータベースの列の一覧をを出そうとした。
対象のデータベースは PostgreSQL。
できたら
SQL Server での列一覧の取得
みたいな感じにしたい。
今回は具体的に調査したい DB があるのでまずそのデータベースのテーブルの属する Schema と Owner の確認。
SELECT DISTINCT
NS.nspname
, AUTH.rolname
FROM
PG_CLASS T
INNER JOIN PG_AUTHID AUTH
ON T.relowner = AUTH.oid
INNER JOIN PG_NAMESPACE NS
ON T.relnamespace = NS.oid
WHERE
relkind = 'r'
結果はこんな感じ。
nspname | rolname
--------------------+----------
information_schema | postgres
public | xxxxuser
pg_catalog | postgres
(3 rows)
上記の結果を踏まえ、以下は public スキーマのテーブルだけを表示するようにする。
まず、テーブルの一覧。
SELECT
NS.nspname
, T.relname
FROM
PG_CLASS T
INNER JOIN PG_NAMESPACE NS
ON T.relnamespace = NS.oid
WHERE
relkind = 'r'
AND NS.nspname = 'public'
ORDER BY
relname;
そんでもって列の一覧をつける。
列のコメントがある場合はそれも表示するようにしてみた。
SELECT
NS.nspname
, AUTH.rolname
, T.relname AS table_name
, C.attname AS column_name
, C.attnum AS column_id
, TY.typname AS type_name
, CASE WHEN C.atttypmod > 0 THEN C.atttypmod ELSE C.attlen END AS column_len
, C.attnotnull AS column_notnull
, CDEF.adsrc AS column_default
, CDESC.description AS column_description
FROM
PG_CLASS T
INNER JOIN PG_ATTRIBUTE C
ON T.oid = C.attrelid
INNER JOIN PG_TYPE TY
ON C.atttypid = TY.oid
INNER JOIN PG_AUTHID AUTH
ON T.relowner = AUTH.oid
INNER JOIN PG_NAMESPACE NS
ON T.relnamespace = NS.oid
LEFT OUTER JOIN pg_attrdef AS CDEF
ON CDEF.adrelid = T.oid
AND CDEF.adnum = C.attnum
LEFT OUTER JOIN pg_description CDESC
ON CDESC.objoid = T.oid
AND CDESC.objsubid = C.attnum
WHERE
relkind = 'r'
AND C.attnum > 0
AND NS.nspname = 'public'
ORDER BY
T.relname,C.attnum;
varchar型のカラムの列が文字数 +4 になっているが、
ここではデータ長として一旦そのままにしておく。
ここに主キーの情報を付けようとするがここから苦戦する。
index の情報は pg_index にある。（※pg_indexesと混同しないように)
ここで index の列の情報はindkey列に含まれる。この列の型はint2vector型となっており、列の情報を持つ pg_attrib の列番号を持つ attnum列に対応する値の配列となっている。
・・・どうあつかうんだ？
検索して Showing Indexes in Postgresql というページにたどり着いた。
これでいいかと一旦思ったがこれだとインデックスの列の順序の情報が欠落するのでだめ。
昔取った杵柄(14年前)でソースを見てみたが役立つ情報は見つけられなかった。
(※ただし、おまけでpg_catalog.format_type という関数を見つけた)
で、ここでやっと PostgreSQL の配列操作のドキュメントを見る。
・・・あるやん。 unnest() ちゅう関数が。
でも順序の保証が危なそうだが・・・って追加で調べたら
unnest() WITH ORDINALITY ってのが出てきた。
PostgreSQL unnest() with element number
そんなん PostgreSQL のドキュメントに書いてないぞーって思ったが
「WITH ORDINALITY」は unnest に限らず、テーブル値を返す関数一般に付けられるものだった。
FROM Clause
そういうわけで、主キーの情報を付け、また varchar の格納可能な文字数が分かる形にして次の形にした。
SELECT
NS.nspname
, AUTH.rolname
, T.relname AS table_name
, C.attname AS column_name
, C.attnum AS column_id
, pg_catalog.format_type(C.atttypid, C.atttypmod) AS type_name
, C.attnotnull AS column_notnull
, CDEF.adsrc AS column_default
, PK.ORDINALITY AS PRIMARY_KEY
, CDESC.description AS column_description
FROM
PG_CLASS T
INNER JOIN PG_ATTRIBUTE C
ON T.oid = C.attrelid
INNER JOIN PG_TYPE TY
ON C.atttypid = TY.oid
INNER JOIN PG_AUTHID AUTH
ON T.relowner = AUTH.oid
INNER JOIN PG_NAMESPACE NS
ON T.relnamespace = NS.oid
LEFT OUTER JOIN pg_attrdef AS CDEF
ON CDEF.adrelid = T.oid
AND CDEF.adnum = C.attnum
LEFT OUTER JOIN pg_description CDESC
ON CDESC.objoid = T.oid
AND CDESC.objsubid = C.attnum
LEFT OUTER JOIN (
SELECT
IDX.indrelid, attnum, ORDINALITY
FROM
PG_INDEX IDX
, unnest(IDX.indkey) WITH ORDINALITY as attnum
WHERE
IDX.indisprimary) PK
ON PK.indrelid = T.oid
AND PK.attnum = C.attnum
WHERE
relkind = 'r'
AND C.attnum > 0
AND NS.nspname = 'public'
ORDER BY
T.relname,C.attnum;
まだ改良の余地がある気はするがとりあえずはこれで。
なお、この SQL は with ORDINALITY が利用可能な PostgreSQL 9.4 以降でのみ使えます。