macでPostgreSQLで各種設計書を作成する

DB系のソフトでWindowsは「A5:SQL Mk-2」という有名な万能フリーソフトがありますが、macは残念ながらありません。 postgreSQLの設計書を書き起こす必要があったので整理がてら記事に起こしておきたいと思います。

前提条件

Mac M1（AppleSilicon）
PostgreSQL 10.12

全体の流れ

ER図
テーブル設計書
関数一覧出力

1.ER図

ER図は「pgAdmin」や「DBeaver」で簡単に作れる。

pgAdminの場合

DBの接続をして、スキーマを選択「右クリック」→「データベースに対するERD」を選択するだけ

DBeaverの場合

DBの接続をして、スキーマを選択「右クリック」→「View Diagram」を選択するだけ

色々調べている途中に「SchemaSpy」という便利そうなツールを見つけたが、SSHトンネル対応していなさそうなので断念した。

2.テーブル設計書

テーブル設計書は、pgAdminでもDBeaverでも1つ1つテーブルを選択すれば出来ますが、面倒くさいので一括で出力したかった。出来ればExcelでシートに分けて出力したかったが、手っ取り早く出来るのがSQLでテーブル名とカラム名などを出力する方法だった。

1
SELECT
2
    sut.relname as tablename,
3
    pd.description as table_comment,
4
    att.attnum AS num,
5
    att.attname AS columnname,
6
    com.description AS column_comment,
7
    typ.typname AS COL_TYPE,
8
    CASE typ.typname WHEN 'varchar' THEN att.atttypmod - 4 WHEN 'bpchar' THEN
9
    att.atttypmod - 4 WHEN 'numeric' THEN att.atttypmod / 65536 WHEN 'date'
10
    THEN att.attlen WHEN 'timestamp' THEN att.attlen END AS COL_LENGTH,
11
    CASE att.attnotnull WHEN 't' THEN 'NOT NULL' WHEN 'f' THEN 'NULL' END AS
12
    NOTNULL,
13
    def.adsrc as default
14
FROM
15
    (((pg_attribute att
21 collapsed lines
16
     LEFT JOIN pg_description com ON att.attnum = com.objsubid AND att.attrelid = com.objoid)
17
     LEFT JOIN pg_attrdef def ON att.attrelid= def.adrelid AND att.attnum = def.adnum))
18
     LEFT JOIN pg_description pd on att.attrelid = pd.objoid AND pd.objsubid = 0 ,
19
     pg_stat_user_tables sut,
20
    -- pg_type
21
     pg_type typ
22
WHERE
23
    att.attrelid = sut.relid AND
24
    att.atttypid = typ.oid AND
25
    att.attnum > 0 AND
26
    sut.relname IN (
27
    SELECT
28
        c.relname
29
    FROM
30
        pg_namespace AS n JOIN pg_class AS c ON (n.oid = c.relnamespace)
31
    WHERE
32
        c.relkind = 'r' AND
33
        c.relname not like 'pg_%' AND
34
        c.relname not like 'sql_%'
35
    )
36
ORDER BY sut.relname, att.attnum

こちらも色々調べていて「tbls」というツールを見つけたが、こちらもSSHトンネル対応していなさそうなので断念した。

3.関数一覧出力

こちらもSQLで対応、関数内にダブルクオーテーションなど入っているケースがあるので、HTML形式などで出力した方がいい（一応ダブルクオーテーションをシングルクォーテーションに置換している）

1
SELECT
2
    p.proname AS function_name,
3
    REPLACE(pg_catalog.pg_get_functiondef(p.oid), '"', '''') AS function_definition
4
FROM
5
    pg_catalog.pg_proc p
6
JOIN
7
    pg_catalog.pg_namespace n ON n.oid = p.pronamespace
8
WHERE
9
    n.nspname = 'insite'
10
    AND p.proisagg = FALSE
11
ORDER BY
12
    function_name;