これはPostgreSQL Advent Calendar 2018 11日目の記事となります。
昨日は@harukat1232000さんで「クォーラムコミットでインメモリクラスタを作ってみる」でした。
はじめに
PostgreSQLのテーブル定義のDDL(以下DDL)を出力するには、pg_dump
コマンドに-s
オプションをつけることで出力されます。
しかし、サーバーに接続しないでDDLを取得したいことってありますよね。
そこで、SQLからDDLを取得出来るストアドプロシージャーを作成しましたのでご紹介します。
使い方
ただのストアドプロシージャーなので、追加すれば使用が可能です。
- [レポジトリ](https://github.com/ester41/pg_scripts/blob/master/pg_get_tabledef.sql)からコードをコピー、ストアドプロシージャーの作成を行います。
- 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 さんです。
Comments
Let's comment your feelings that are more than good