PostgreSQL での列一覧(主キー情報付き) 

システムの更新プロジェクトで既存のデータベースの列の一覧をを出そうとした。

対象のデータベースは PostgreSQL。
できたら
[SQL Server での列一覧の取得](https://kazamai7610.wordpress.com/2012/02/06/ms-sql-server-200520082008r2-の列一覧を取得する型定義付き-2/)

みたいな感じにしたい。


　今回は具体的に調査したい 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](https://www.postgresql.org/docs/10/static/catalog-pg-index.html) にある。（※[pg_indexes](https://www.postgresql.org/docs/10/static/view-pg-indexes.html)と混同しないように)
ここで index の列の情報は`indkey`列に含まれる。この列の型は`int2vector`型となっており、列の情報を持つ `pg_attrib` の列番号を持つ `attnum`列に対応する値の配列となっている。
・・・どうあつかうんだ？

　検索して [Showing Indexes in Postgresql](https://robert-reiz.com/2012/01/17/showing-indexes-in-postgresql/) というページにたどり着いた。
これでいいかと一旦思ったがこれだとインデックスの列の順序の情報が欠落するのでだめ。

　[昔取った杵柄(14年前)](http://10395.diarynote.jp/200405120103090000/)でソースを見てみたが役立つ情報は見つけられなかった。
(※ただし、おまけで[pg_catalog.format_type](https://www.postgresql.jp/document/8.0/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE) という関数を見つけた)

で、ここでやっと PostgreSQL の配列操作の[ドキュメント](https://www.postgresql.org/docs/10/static/functions-array.html)を見る。
・・・あるやん。 `unnest()` ちゅう関数が。
でも順序の保証が危なそうだが・・・って追加で調べたら
`unnest() WITH ORDINALITY` ってのが出てきた。
[PostgreSQL unnest() with element number](https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number)

そんなん　PostgreSQL のドキュメントに書いてないぞーって思ったが
「WITH ORDINALITY」は unnest に限らず、テーブル値を返す関数一般に付けられるものだった。
[FROM Clause](https://www.postgresql.org/docs/10/static/sql-select.html#SQL-FROM)


そういうわけで、主キーの情報を付け、また 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 以降でのみ使えます。