DB内のテーブル名一覧、テーブル情報などを取得するSQL

PHPで新人教育向けにSQL（ここでは PostgreSQL）勉強用プログラムを制作しているのだけど、その中で、DB内のテーブル名一覧や、テーブル情報、列情報などを表示させたい、と思った。

で、お題を追加や変更時になるべく最小限の変更で済むようにするには……とかそんな事を思ったら、やっぱりDBの事はDBに聞け、って結論に。

始めて pg_* 系のテーブルをあーだこーだして、たどり着いたSQLを（ツッコミどころがありそうで心配なのですが）備忘録代わりに記載しておきます。

テーブル一覧の取得

SELECT
    cls.oid,     -- pg_class.oid
    nsp.nspname, -- テーブルを持つスキーマ名 public とか
    usr.usename, -- 所有ユーザー名
    cls.relname  -- テーブル名
FROM
    pg_class cls
        INNER JOIN pg_namespace nsp
            ON (cls.relnamespace = nsp.oid)
        INNER JOIN pg_user usr
            ON (cls.relowner = usr.usesysid)
WHERE
    cls.relkind = 'r'
--     AND nsp.nspname = 'public' -- スキーマ名で絞込み
--     AND usr.usename = 'user1' -- 所有ユーザー名で絞込み
ORDER BY
    nsp.nspname,
    usr.usename,
    cls.relname

pg_class を基点に、ネームスペースとユーザー情報を取得します。
relkind は種別を表していますが、小文字の r が通常のテーブル、となります。

シーケンス一覧の取得

SELECT
    cls.oid,     -- pg_class.oid
    nsp.nspname, -- シーケンスを持つスキーマ名 public とか
    usr.usename, -- 所有ユーザー名
    cls.relname  -- テーブル名
FROM
    pg_class cls
        INNER JOIN pg_namespace nsp
            ON (cls.relnamespace = nsp.oid)
        INNER JOIN pg_user usr
            ON (cls.relowner = usr.usesysid)
WHERE
    cls.relkind = 'S'
--     nsp.nspname = 'public' -- スキーマ名で絞込み
--     usr.usename = 'user1' -- 所有ユーザー名で絞込み
ORDER BY
    nsp.nspname,
    usr.usename,
    cls.relname

やっている事は、テーブル一覧の取得と全く変わりません。
relkind が大文字の S がシーケンスを表します。

テーブル列一覧の取得

上記テーブル一覧取得の際にゲットした「pg_class.oid」を基に列の一覧を取得します。

SELECT
    colname as "列名",
    CASE
        WHEN size IS NULL
            THEN datatype
        -- サイズ情報が取得できた場合は付加
        ELSE datatype || ' (' || size || ')'
    END AS "データ型",
    CASE
        -- NOT NULL情報 と DEFAULT情報をマージ
        WHEN "notnull" IS NOT NULL AND "default" IS NOT NULL
            THEN "notnull" || ' ' || "default" -- 
        ELSE COALESCE("notnull", "default")
    END AS "属性"
FROM
    (
        SELECT
            attr.attnum AS idx, -- 列番号
            attr.attname AS colname, -- 列名
            CASE typ.typname -- 分かりやすい名前に変更
                WHEN 'int2'   THEN 'SMALLINT'
                WHEN 'int4'   THEN 'INT'
                WHEN 'int8'   THEN 'BIGINT'
                WHEN 'float4' THEN 'REAL'
                WHEN 'float8' THEN 'DOUBLE'
                WHEN 'bpchar' THEN 'CHAR'
                ELSE UPPER(typ.typname)
            END AS datatype, -- データ型
            -- attr.atttypmod が正の整数なら、
            -- サイズ付加情報あり？
            -- （よく調べて無いので自信なし）
            CASE WHEN attr.atttypmod > 0 THEN
                CASE typ.typname
                    WHEN 'numeric'
                        THEN (attr.atttypmod - 4) / 65536
                    WHEN 'decimal'
                        THEN (attr.atttypmod - 4) / 65536
                    WHEN 'date'
                        THEN COALESCE(attr.atttypmod - 4, 10)
                    WHEN 'time'
                        THEN COALESCE(attr.atttypmod - 4, 8)
                    WHEN 'timestamp'
                        THEN COALESCE(attr.atttypmod - 4, 19)
                    ELSE attr.atttypmod - 4
                END
            END AS size,
            -- attnotnull が true なら NOT NULL
            CASE attr.attnotnull
                WHEN TRUE THEN 'NOT NULL'
                ELSE NULL
            END AS notnull,
            -- atthasdef が true なら DEFAULT 値あり
            CASE attr.atthasdef
                WHEN TRUE THEN 'DEFAULT ' || adef.adsrc
                ELSE NULL
            END AS default
        FROM
            pg_class cls
                INNER JOIN pg_attribute attr
                    ON (cls.oid = attr.attrelid)
                INNER JOIN pg_type typ
                    ON (attr.atttypid = typ.oid)
                -- デフォルト値情報
                LEFT OUTER JOIN pg_attrdef adef
                    ON (cls.oid = adef.adrelid
                        AND attr.attnum = adef.adnum)
        WHERE
            cls.oid = ? -- pg_class.oid を入れよ
            -- 列番号が0以下の場合、システム列なので対象にしない
            AND attr.attnum >= 0
            -- attisdropped が TRUE なら、
            -- この列はすでに削除されていて無効状態
            AND attr.attisdropped IS NOT TRUE
            AND typ.typisdefined
    ) AS tbl
ORDER BY idx;

メジャーどころの型でしか試してないし、複合型などの事は一切考えずに書いているので、問題があるかもしれません。
特にサイズ情報の取得は自信がない（＝適当）です。
必要があったら、また調べて書き直したいと思います。
というか、コメントで教えていただけると助かります。（自堕落ですみません）

テーブルインデックスの取得

「pg_class.oid」を基にテーブルインデックス情報を取得します。

SELECT
    cls.relname AS name, -- インデックス名
    (
        CASE
            -- プライマリキー
            WHEN idx.indisprimary
                THEN 'PRIMARY KEY, '
            -- ユニーク属性
            WHEN idx.indisunique
                THEN 'UNIQUE, '
        END
        || am.amname -- インデックスのアクセスメソッド名
        || ' ('
        -- 列名の取得（カンマ区切りで並べる）
        || ARRAY_TO_STRING(ARRAY(
            SELECT
                attr.attname
            FROM
                pg_class cls2
                    INNER JOIN pg_attribute attr
                        ON (cls2.oid = attr.attrelid)
            WHERE
                cls2.oid = idx.indrelid
                AND attr.attnum = ANY(idx.indkey)
        ), ', ')
        || ')'
    )AS value -- 情報
FROM
    pg_index idx
        INNER JOIN pg_class cls
            ON (idx.indexrelid = cls.oid)
        INNER JOIN pg_am am
            ON (cls.relam = am.oid)
WHERE
    idx.indrelid = ? -- pg_class.oid を入れよ
ORDER BY
    indexrelid;

テーブルチェック制約 / 外部キー制約の取得

「pg_class.oid」を基にチェック制約 / 外部キー制約を取得します。

SELECT
    con.conname AS name, -- 制約名
    pg_get_constraintdef(con.oid) AS value -- 情報
FROM
    pg_constraint con
WHERE
    con.conrelid = ? -- pg_class.oid を入れよ
    AND (con.contype = 'c' OR con.contype = 'f')
ORDER BY
    con.contype,
    conname;

テーブルトリガの取得

「pg_class.oid」を基にテーブルトリガ名、実行する関数情報を取得します。

SELECT
    trg.tgname, -- トリガ名
    prc.proname, -- 実行する関数名
    rtrim(replace(encode(trg.tgargs, 'escape')
        , E'\\000', ', '),', ') -- 引数
FROM
    pg_trigger trg
    INNER JOIN pg_proc prc ON (trg.tgfoid = prc.oid)
WHERE
    trg.tgrelid = 17812 -- pg_class.oid を入れよ
    AND trg.tgenabled
    AND trg.tgisconstraint IS FALSE

トリガが実行されるタイミングは取得していません。
（面倒そうだったので、機会があれば調べてみようと思います。）

間違い、過不足、他のいい方法などありましたら、コメントで教えてください。
よろしくお願いします。