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

これはPostgreSQL Advent Calendar 2018 11日目の記事となります。

昨日は@harukat1232000さんで「クォーラムコミットでインメモリクラスタを作ってみる」でした。

はじめに

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

使い方

ただのストアドプロシージャーなので、追加すれば使用が可能です。

1. [レポジトリ](https://github.com/ester41/pg_scripts/blob/master/pg_get_tabledef.sql)からコードをコピー、ストアドプロシージャーの作成を行います。
2. DDLを作成したいテーブルを、以下のように指定して実行します。

   SELECT pg_get_tabledef('<スキーマ名>', '<テーブル名>');
   

   '<スキーマ名>'にNULLを指定した場合は、カレントスキーマが設定されます。

出力例

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 列のデフォルト値を格納しています。
• pg_attribute テーブルの列情報を格納しています。
• pg_class テーブルまたは、テーブルに似たすべての基本的な情報(インデックス、ビューなど)を格納しています。
• pg_constraint テーブル上の制約情報を格納しています。
• pg_index インデックス情報を格納しています。
• pg_namespace 名前空間(スキーマ)情報を格納しています。

カラム情報取得

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からカラム情報を取得します。
システムカタログ情報関数のpg_catalog.format_type()関数でカラムのデータ定義を取得することが出来ます。
また、NOT NULL制約はpg_attribute.attnotnullにbool型として格納しています。
それ以外の定義(デフォルト値や列制約、表制約など)はシステムカタログ情報関数のpg_catalog.pg_get_expr()関数で取得することが出来ます。

インデックス情報取得

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'(排他制約)を指定します。
システムカタログ情報関数の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文内に含めるようにしています。

チェック制約情報取得

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(チェック制約)を指定します。
システムカタログ情報関数のpg_catalog.pg_get_constraintdef()関数で制約の定義を取得することが出来ます。

コメント情報取得

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を取得します。
コメント情報関数のpg_catalog.obj_description()関数でテーブルのコメントを取得することが出来ます。
obj_description()関数は、テーブルのOIDで指定されるテーブルのコメントを返します。

カラムのコメント取得(column_data)

pg_catalog.pg_namespaceとpg_catalog.pg_attributeでテーブルのOIDを取得し、pg_catalog.pg_attributeからカラム名とカラム番号を取得します。
コメント情報関数のpg_catalog.col_description()関数でカラムのコメントを取得することが出来ます。
col_description()関数は、テーブルのOIDとその列番号で指定されるテーブルの列のコメントを返します。

おわりに

今回扱ったテーブル以外にもシステムカタログはたくさん存在しているので、もっと有用な情報を取得できます。
PostgreSQLのシステムカタログを掘り下げてみていくと、奥が深くパズルを解いていく感じで結構楽しいですよ！

明日のPostgreSQL Advent Calendar 2018は、@houseisland さんです。