PostgreSQL SQL項目⇒日本語コメント(論理名)変換マクロ作成用SQL


SELECT
    -- colname as "列名",
    --tbl.description as "コメント",
	 distinct 'clpstr = clpstr.replace(/([^A-Za-z0-9_]+)'
	 ||  coalesce(colname,'') 
	 ||'/g,"$1'
	 ||translate(coalesce(tbl.description,''),'()/{}^$"?','()/{}^$”?') 
	 ||'");' as sakura
	 
	,char_length(colname)
	,tbl.description
FROM
    (
        SELECT
            cls.oid,
        cls.relname,
            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,
            pd.description
        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 JOIN pg_description pd
                    ON (pd.objoid = cls.oid
                    and pd.objoid = attr.attrelid
            and pd.objsubid = attr.attnum
            )
                -- デフォルト値情報
                LEFT OUTER JOIN pg_attrdef adef
                    ON (cls.oid = adef.adrelid AND attr.attnum = adef.adnum)
        WHERE
            cls.oid in (
        SELECT
            cls.oid
        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' -- スキーマ名で絞込み
        ORDER BY
            nsp.nspname,
            usr.usename,
            cls.relname
                ) -- pg_class.oid を入れよ
            -- 列番号が0以下の場合、システム列なので対象にしない
            AND attr.attnum >= 0
            -- attisdropped が TRUE なら、
            -- この列はすでに削除されていて無効状態
            AND attr.attisdropped IS NOT TRUE
            AND typ.typisdefined
    ) AS tbl
left join pg_description pd
on tbl.oid = pd.objoid and pd.objsubid=0
WHERE tbl.description is not null
--where tbl.description like '%特別控除%'

ORDER BY char_length(colname) desc;

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です