PL/pgSQLでDDLを取得してみる

これは[PostgreSQL Advent Calendar 2018](https://qiita.com/advent-calendar/2018/postgresql) 11日目の記事となります。

昨日は@harukat1232000さんで「[クォーラムコミットでインメモリクラスタを作ってみる](https://qiita.com/harukat1232000/items/5fed0ec1c42aca6f6e5d)」でした。

# はじめに
PostgreSQLのテーブル定義のDDL(以下DDL)を出力するには、`pg_dump`コマンドに`-s`オプションをつけることで出力されます。
しかし、サーバーに接続しないでDDLを取得したいことってありますよね。
そこで、SQLからDDLを取得出来るストアドプロシージャーを作成しましたのでご紹介します。

# 使い方
ただのストアドプロシージャーなので、追加すれば使用が可能です。
<ol><li>[レポジトリ](https://github.com/ester41/pg_scripts/blob/master/pg_get_tabledef.sql)からコードをコピー、ストアドプロシージャーの作成を行います。</li><li>DDLを作成したいテーブルを、以下のように指定して実行します。

```sql
SELECT pg_get_tabledef('<スキーマ名>', '<テーブル名>');
```
`'<スキーマ名>'`に`NULL`を指定した場合は、カレントスキーマが設定されます。</li></ol>

# 出力例
```
test=# create table data (
test-#   id integer not null
test-#   , unique_data integer unique
test-#   , check_data integer check (check_data > 0)
test-#   , ref_id integer references code_mst(id)
test-#   , primary key (id)
test-# );
CREATE TABLE
test=# select pg_get_tabledef(null, 'data');
                 pg_get_tabledef
--------------------------------------------------
 DROP TABLE IF EXISTS data CASCADE;              +
 CREATE TABLE data(                              +
   id integer not null                           +
   , unique_data integer                         +
   , check_data integer                          +
   , ref_id integer                              +
   , PRIMARY KEY (id)                            +
   , UNIQUE (unique_data)                        +
   , CHECK (check_data > 0)                      +
   , FOREIGN KEY (ref_id) REFERENCES code_mst(id)+
 );                                              +
                                                 +

(1 row)
```
1レコードとするため改行を含めています。
そのため、psqlで出力を行った場合は末尾に`+`が表示されます。

# 制限
以下の機能には対応していません。

* 継承(INHERITS)
* パーティション(PARTITION)

# 動作確認バージョン

* 9.6.2
* 11.1

# 解説
今回作成したストアドでメイン部分を解説します。
SQL中の`<スキーマ名>`や`<テーブル名>`には、小文字の名称が入ります。

## 使用するシステムカタログテーブル
PostgreSQLのシステム情報は、システムカタログ(pg_catalog)と呼ばれるスキーマに格納されています。
システムカタログにアクセスすることで様々な情報を取得できます。
今回使用したテーブルは以下の通りです。

* [pg_attrdef](https://www.postgresql.jp/document/current/html/catalog-pg-attrdef.html) 列のデフォルト値を格納しています。
* [pg_attribute](https://www.postgresql.jp/document/current/html/catalog-pg-attribute.html) テーブルの列情報を格納しています。
* [pg_class](https://www.postgresql.jp/document/current/html/catalog-pg-class.html) テーブルまたは、テーブルに似たすべての基本的な情報(インデックス、ビューなど)を格納しています。
* [pg_constraint](https://www.postgresql.jp/document/current/html/catalog-pg-constraint.html) テーブル上の制約情報を格納しています。
* [pg_index](https://www.postgresql.jp/document/current/html/catalog-pg-index.html) インデックス情報を格納しています。
* [pg_namespace](https://www.postgresql.jp/document/current/html/catalog-pg-namespace.html) 名前空間(スキーマ)情報を格納しています。

## カラム情報取得
```sql
select
  pa.attnum
  , pa.attname
  , pg_catalog.format_type(pa.atttypid, pa.atttypmod) || case 
    when pa.attnotnull 
    then ' not null' 
    else '' 
    end || ' ' || coalesce( 
    ( 
      select
        substring( 
          pg_catalog.pg_get_expr(pd.adbin, pd.adrelid) for 128
        ) 
      from
        pg_catalog.pg_attrdef pd 
      where
        pd.adrelid = pa.attrelid 
        and pd.adnum = pa.attnum 
        and pa.atthasdef
    ) 
    , ''
  ) as format 
from
  pg_catalog.pg_attribute pa 
  inner join pg_catalog.pg_class pc 
    on pa.attrelid = pc.oid 
  inner join pg_catalog.pg_namespace pn 
    on pn.oid = pc.relnamespace 
where
  pn.nspname = '<スキーマ名>' 
  and pc.relname = '<テーブル名>' 
  and pa.attnum > 0 
order by
  attnum;
```
`pg_catalog.pg_namespace`と`pg_catalog.pg_attribute`でテーブルのOIDを取得し、`pg_catalog.pg_attribute`からカラム情報を取得します。
[システムカタログ情報関数](https://www.postgresql.jp/document/current/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE)の`pg_catalog.format_type()`関数でカラムのデータ定義を取得することが出来ます。
また、`NOT NULL`制約は`pg_attribute.attnotnull`に`bool`型として格納しています。
それ以外の定義(デフォルト値や列制約、表制約など)は[システムカタログ情報関数](https://www.postgresql.jp/document/current/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE)の`pg_catalog.pg_get_expr()`関数で取得することが出来ます。

## インデックス情報取得
```sql
select
  pg_catalog.pg_get_constraintdef(pco.oid, true) as ct_str
  , pg_catalog.pg_get_indexdef(pi.indexrelid, 0, true) as ci_str 
from
  pg_catalog.pg_class pc 
  inner join pg_catalog.pg_namespace pn 
    on pn.oid = pc.relnamespace 
  inner join pg_catalog.pg_index pi 
    on pc.oid = pi.indrelid 
  inner join pg_catalog.pg_class pc2 
    on pi.indexrelid = pc2.oid 
  left join pg_catalog.pg_constraint pco 
    on ( 
      pco.conrelid = pi.indrelid 
      and pco.conindid = pi.indexrelid 
      and pco.contype in ('p', 'u', 'x')
    ) 
where
  pn.nspname = '<スキーマ名>' 
  and pc.relname = '<テーブル名>' 
order by
  pi.indisprimary desc
  , pi.indisunique desc
  , pc.relname;
```
`pg_catalog.pg_namespace`と`pg_catalog.pg_attribute`でテーブルのOIDを取得し、`pg_catalog.pg_constraint`からチェック制約のOIDを取得します。
`pg_catalog.pg_constraint`の検索条件として、`contype`で`p`(主キー制約)または`u`(一意制約)または'x'(排他制約)を指定します。
[システムカタログ情報関数](https://www.postgresql.jp/document/current/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE)の`pg_catalog.pg_get_constraintdef()`関数及び`pg_catalog.pg_get_indexdef()`関数で制約の定義を取得することが出来ます。
`pg_catalog.pg_get_constraintdef()`関数で取得できる定義は`CREATE TABLE`文内の定義で、`pg_catalog.pg_get_indexdef()`関数で取得できる定義は`CREATE INDEX`文となります。
ストアド内では、`pg_catalog.pg_get_constraintdef()`関数で取得できる場合は`CRTE TABLE`文内に含めるようにしています。

## チェック制約情報取得
```sql
select
  pg_catalog.pg_get_constraintdef(pr.oid, true) as condef 
from
  pg_catalog.pg_constraint pr 
  inner join pg_catalog.pg_class pc 
    on pr.conrelid = pc.oid 
  inner join pg_catalog.pg_namespace pn 
    on pn.oid = pc.relnamespace 
where
  pn.nspname = '<スキーマ名>' 
  and pc.relname = '<テーブル名>' 
  and pr.contype = 'c';
```
`pg_catalog.pg_namespace`と`pg_catalog.pg_attribute`でテーブルのOIDを取得し、`pg_catalog.pg_constraint`からチェック制約のOIDを取得します。
`pg_catalog.pg_constraint`の検索条件として、`contype`で`c`(チェック制約)を指定します。
[システムカタログ情報関数](https://www.postgresql.jp/document/current/html/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE)の`pg_catalog.pg_get_constraintdef()`関数で制約の定義を取得することが出来ます。

## コメント情報取得
```sql
with check_data(schema_name, table_name) as ( 
  select
    '<スキーマ名>' as schema_name
    , '<テーブル名>' as table_name
) 
, table_data(schema_name, table_name, table_comment, positon) as ( 
  select
    pn.nspname as schema_name
    , pc.relname as table_name
    , pg_catalog.obj_description(pc.oid, 'pg_class') as table_comment
    , 0 as positon 
  from
    pg_catalog.pg_class pc 
    inner join pg_catalog.pg_namespace pn 
      on pn.oid = pc.relnamespace 
    inner join check_data cd 
      on pn.nspname = cd.schema_name 
      and pc.relname = cd.table_name 
  where
    pg_catalog.obj_description(pc.oid, 'pg_class') is not null
) 
, column_data( 
  schema_name
  , table_name
  , column_name
  , column_comment
  , positon
) as ( 
  select
    cd.schema_name
    , cd.table_name
    , pa.attname as column_name
    , pg_catalog.col_description(pc.oid, pa.attnum) as column_comment
    , pa.attnum as positon 
  from
    pg_catalog.pg_attribute pa 
    inner join pg_catalog.pg_class pc 
      on pc.oid = pa.attrelid 
    inner join pg_catalog.pg_namespace pn 
      on pn.oid = pc.relnamespace 
    inner join check_data cd 
      on pn.nspname = cd.schema_name 
      and pc.relname = cd.table_name 
  where
    pa.attnum > 0 
    and pg_catalog.col_description(pc.oid, pa.attnum) is not null 
  order by
    pa.attnum
) 
select
  array_to_string( 
    array ( 
      select
        str 
      from
        ( 
          select
            'COMMENT ON TABLE ' || td.table_name || ' IS ''' || td.table_comment || ''';' as str
            , td.positon 
          from
            table_data td 
          union 
          select
            'COMMENT ON COLUMN ' || cd.table_name || '.' || cd.column_name || ' IS ''' || cd.column_comment || ''';'
             as str
            , cd.positon 
          from
            column_data cd
        ) base 
      order by
        positon
    ) 
    , chr(10)
  ); 
```
このSQLだけは他の情報と独立しているため、単体で動作します。

### テーブルのコメント取得(table_data)  
`pg_catalog.pg_namespace`と`pg_catalog.pg_attribute`でテーブルのOIDを取得します。
[コメント情報関数](https://www.postgresql.jp/document/current/html/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE)の`pg_catalog.obj_description()`関数でテーブルのコメントを取得することが出来ます。
`obj_description()`関数は、テーブルのOIDで指定されるテーブルのコメントを返します。

### カラムのコメント取得(column_data)
`pg_catalog.pg_namespace`と`pg_catalog.pg_attribute`でテーブルのOIDを取得し、`pg_catalog.pg_attribute`からカラム名とカラム番号を取得します。
[コメント情報関数](https://www.postgresql.jp/document/current/html/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE)の`pg_catalog.col_description()`関数でカラムのコメントを取得することが出来ます。
`col_description()`関数は、テーブルのOIDとその列番号で指定されるテーブルの列のコメントを返します。

# おわりに
今回扱ったテーブル以外にも[システムカタログはたくさん存在している](https://www.postgresql.jp/document/current/html/catalogs.html)ので、もっと有用な情報を取得できます。
PostgreSQLのシステムカタログを掘り下げてみていくと、奥が深くパズルを解いていく感じで結構楽しいですよ！

明日の[PostgreSQL Advent Calendar 2018](https://qiita.com/advent-calendar/2018/postgresql)は、@houseisland さんです。