テーブル一覧、テーブル定義情報取得用SQL Ver.2

前回記事のSQLではテーブル項目の桁数が取得出来ていなかったのだが、取得方法がどうやらあるようなのでちょっぴり改造。

まず、以下の様なテーブルCREATE文を用意し、テーブルを作成。。

DROP TABLE mst_smpl;

CREATE TABLE mst_smpl
(
  user_id character varying(10) NOT NULL, -- サンプル管理マスタ.ユーザID
  data_id character varying(5), -- サンプル管理マスタ.データID
  user_name character varying(50), -- サンプル管理マスタ.ユーザ名
  tel_home character(15), -- サンプル管理マスタ.自宅電話番号
  points numeric(10), -- サンプル管理マスタ.点数
  reg_ymd date, -- サンプル管理マスタ.登録日
  upd_timestamp timestamp without time zone, -- サンプル管理マスタ.更新タイムスタンプ
  CONSTRAINT pk_mst_smpl PRIMARY KEY (user_id)
)
WITHOUT OIDS;
ALTER TABLE mst_smpl OWNER TO (任意のユーザ名);
GRANT ALL ON TABLE mst_smpl TO (任意のユーザ名);
GRANT ALL ON TABLE mst_smpl TO (任意のユーザ名);
COMMENT ON TABLE mst_smpl IS 'サンプル管理マスタ';
COMMENT ON COLUMN mst_smpl.user_id IS 'サンプル管理マスタ.ユーザID';
COMMENT ON COLUMN mst_smpl.data_id IS 'サンプル管理マスタ.データID';
COMMENT ON COLUMN mst_smpl.data_name IS 'サンプル管理マスタ.ユーザ名';
COMMENT ON COLUMN mst_smpl.tel_home IS 'サンプル管理マスタ.自宅電話番号';
COMMENT ON COLUMN mst_smpl.points IS 'サンプル管理マスタ.点数';
COMMENT ON COLUMN mst_smpl.reg_ymd IS 'サンプル管理マスタ.登録日';
COMMENT ON COLUMN mst_smpl.upd_timestamp IS 'サンプル管理マスタ.更新タイムスタンプ';

commit;

一部改良を加えたSQL文。前回と異なる箇所は「COL_LENGTH」の取得部分で、前回取れなかった部分の桁数情報はシステムカタログのpg_attribute.atttypmodに存在する事が判明。管理用に付加されたバイト数などを編集しなおして(VARCHAR型は+4バイト、数値型は×65536？)、意図した値を取得する事が出来ました。

参考となったのはこちら。
カラムサイズの取得 - 教えて！goo

SELECT
    att.attnum AS NUM,
    att.attname AS COL_NAME,
    typ.typname AS COL_TYPE,
    CASE typ.typname
        WHEN 'varchar'   THEN att.atttypmod-4
        WHEN 'bpchar'    THEN att.atttypmod-4
        WHEN 'numeric'   THEN att.atttypmod/65536
        WHEN 'date'      THEN att.attlen
        WHEN 'timestamp' THEN att.attlen
    END AS COL_LENGTH,
    CASE att.attnotnull
        WHEN 't' THEN 'NOT NULL'
        WHEN 'f' THEN 'NULL'
    END AS NOTNULL,
    com.description AS COL_COMMENT

FROM
    pg_attribute att,
    pg_stat_user_tables sut,
    pg_type typ,
    pg_description com
WHERE
    att.attrelid = sut.relid
AND att.attrelid = com.objoid
AND att.attnum = com.objsubid
AND att.atttypid = typ.oid
AND att.attnum > 0
AND sut.relname = 'mst_smpl'

ORDER BY
    att.attnum

情報取得SQL実行結果。

	NUM	COL_NAME	COL_TYPE	COL_LENGTH	NOTNULL	COL_COMMENT
1	user_id	varchar	10	NOT NULL	サンプル管理マスタ.ユーザID
2	data_id	varchar	5	NULL	サンプル管理マスタ.データID
3	user_name	varchar	50	NULL	サンプル管理マスタ.ユーザ名
4	tel_home	varchar	15	NULL	サンプル管理マスタ.自宅電話番号
5	points	numeric	10	NULL	サンプル管理マスタ.点数
6	reg_ymd	date	4	NULL	サンプル管理マスタ.登録日
7	upd_timestamp	timestamp	8	NULL	サンプル管理マスタ.更新タイムスタンプ

とりあえず、これで欲しい情報は一通りカバー出来そう。あとは上記SQLで取得仕切れてないデータ型を対応する位かな…。