SELECT
COALESCE(SPLIT_PART(t.COMMENT, ':', 1), c.column_name) AS "コメント名"
, c.column_name AS "列の名前"
, (
CASE c.udt_name
WHEN 'date' THEN 'DateTime'
WHEN 'varchar' THEN 'string'
WHEN 'text' THEN 'string'
WHEN 'timestamp' THEN 'DateTime'
WHEN 'numeric' THEN 'decimal'
ELSE (
CASE
WHEN strpos(c.udt_name, 'int') > 0
THEN 'decimal'
ELSE c.udt_name
END
)
END
) AS "を選択してオプションを設定します。"
, ordinal_position AS "行の順序"
, DENSE_RANK() OVER (ORDER BY c.table_name) AS "表の順序"
, c.table_name AS "テーブル名"
FROM
information_schema.columns c
LEFT JOIN (
SELECT
a.table_name AS table_name
, a.column_name AS column_name
, a.data_type AS data_type
, a.COMMENT AS COMMENT
FROM
(
SELECT
pg_stat_user_tables.relname AS table_name
, information_schema.columns.column_name AS column_name
, information_schema.columns.data_type AS data_type
, (
SELECT
description
FROM
pg_description
WHERE
pg_description.objoid = pg_stat_user_tables.relid
AND pg_description.objsubid = information_schema.columns.ordinal_position
) AS COMMENT
FROM
pg_stat_user_tables
, information_schema.columns
WHERE
pg_stat_user_tables.relname = information_schema.columns.table_name
AND pg_stat_user_tables.schemaname = current_schema()
) AS a
WHERE
a.COMMENT IS NOT NULL
) t
ON c.table_name = t.table_name
AND c.column_name = t.column_name
WHERE
table_schema = 'public' -- schemaname
ORDER BY
c.table_name
, ordinal_position;