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

Project PostgreSQL


関連ツールを用いて作成したDBテーブル定義から、自動で

  • テーブル定義情報のDTO(フィールド、setter/getter)
  • テーブル情報1レコードに対するSELECT/INSERT/UPDATE/DELETEの処理を行う一連のサービスクラス(ベース：SpringJDBC)

を作れないかなと思っている。

最終的にはWebアプリケーションで「テーブル一覧」→「テーブル詳細」→「各種クラス作成(DTO/SQL/Entity等など)」を行えるものを用意したいと思っているのだが、それは先の話として…。

とりあえず手始めに、その辺の機能を満たすものを作るために必要なSQL文から調べてみる。

  • 対象テーブル一覧の取得

SELECT
    sut.relid,
    sut.schemaname,
    sut.relname,
    des.objsubid,
    des.description
FROM
    pg_stat_user_tables sut,
    pg_description des
WHERE
    sut.relid = des.objoid
AND des.objsubid = 0

WHERE句の最後の行、「des.objsubid = 0」は、テーブルのコメント文（ここではテーブル名をコメントとして登録した）を取得するための条件。テーブル名のコメント(0)→テーブル項目名のコメント(1…)と続いていたので、ここでは0(テーブル名のコメント?)を指定したが、果たしてこれは正しいのだろうか…。


  • 対象テーブルの詳細情報取得





SELECT

    att.attnum AS NUM,

    att.attname AS COL_NAME,

    typ.typname AS COL_TYPE,

    att.attlen  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 = '(テーブル名)'
ORDER BY

    att.attnum


項目名(英字)と項目名(日本語名：コメントから編集)はフィールド、setter/getterメソッド(及びそのコメント部分)を作成する上で必須。
SELECT時の値の取得方法やINSERT/UPDATE時のフォーマット指定の判定材料として、データの型も必要。
桁数は…DTOとかDBアクセスクラスを作成する上では無くても平気かな。桁数はエラーチェック方面の話になってくるし。


SQL作成にあたっては以下の情報を参考にさせて頂きました。m(_ _)m