【PostgreSQL】PG读取元数据获取表结构及字段类型信息（过程拆解及其他应用场景）

〇、参考链接

 

一、代码

指定模式的表名和字段

select
  c.relname 表名,
  cast (
    obj_description (relfilenode, 'pg_class') as varchar
  ) 名称,
  d.description 字段备注,
  a.attname 字段,
  
  concat_ws (
    '',
    t.typname,
    SUBSTRING (
      format_type (a.atttypid, a.atttypmod)
      from
        '\(.*\)'
    )
  ) as 字段类型
from
  pg_class c,
  pg_attribute a,
  pg_type t,
  pg_description d
where
  a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
and d.objoid = a.attrelid
and d.objsubid = a.attnum
and c.relname in (
  select
    tablename
  from
    pg_tables
  where
    schemaname = 'tp'
  and position ('_2' in tablename) = 0
)
and c.relname = 'bd_bom_product_child'

二、查询不包含分区表的表名

select distinct
c.relname 表名
from
  pg_class c,
  pg_attribute a,
  pg_type t,
  pg_description d
where
  a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
and d.objoid = a.attrelid
and d.objsubid = a.attnum
and c.relname in (
  select
    tablename
  from
    pg_tables
  where
    schemaname = 'tp'
  and position ('_2' in tablename) = 0
)

三、查询带分区的表名

  select
    tablename
  from
    pg_tables
  where
    schemaname = 'tp'
  -- and position ('_2' in tablename) = 0