PostgreSQLでテーブル定義を取得するSQL
商用RDBMSだとこの辺楽チンなんだけど。。。
select attnum as 列番, attname as 列名,
case typname
when '_bpchar' then 'char'
when '_varchar' then 'varchar'
when '_date' then 'date'
when '_float8' then 'float8'
when '_int4' then 'integer'
when '_interval' then 'interval'
when '_numeric' then 'numeric'
when '_float4' then 'float4'
when '_int2' then 'smallint'
when '_text' then 'text'
when '_time' then 'time'
when '_timestamp' then 'timestamp'
end as 型,
case typname
when '_bpchar' then atttypmod - 4
when '_varchar' then atttypmod - 4
when '_numeric' then (atttypmod - 4) / 65536
else attlen
end as 長さ,
case typname
when '_numeric' then (atttypmod - 4) % 65536
else 0
end as 小数,
case b.attnotnull
when 't' then 'Yes'
when 'f' then 'No'
else ''
end as "NotNull"
from pg_stat_user_tables as a, pg_attribute as b, pg_type as c
where a.schemaname = 'スキーマ名'
and a.relname = 'テーブル名'
and a.relid = b.attrelid
and b.attnum > 0
and b.atttypid = c.typelem
and substr(typname,1, 1) = '_'
order by schemaname,relname,attnum;
