PGSql查看表结构以及注释信息

创建视图

CREATE OR REPLACE VIEW dbo.v_sys_tableinfo

AS SELECT pc.relname AS tablename,

pa.attname AS columnname,

pt.typname AS columntype,

CASE

WHEN pa.attlen > 0 THEN pa.attlen::integer

ELSE pa.atttypmod - 4

END AS columnlength,

pa.attnotnull AS isnullable,

CASE

WHEN (( SELECT count(*) AS count

FROM pg_constraint

WHERE pg_constraint.conrelid = pa.attrelid AND pg_constraint.conkey[1] = pa.attnum AND pg_constraint.contype = 'p'::"char")) > 0 THEN true

ELSE false

END AS isprimary,

pd.description AS columndescription

FROM pg_class pc,

pg_attribute pa,

pg_type pt,

pg_description pd

WHERE pc.oid = pa.attrelid AND pt.oid = pa.atttypid AND pd.objoid = pa.attrelid AND pd.objsubid = pa.attnum;

执行效果

   select  *  from  dbo.v_sys_tableinfo