【数据库】Postgresql/PG-编写函数实现字段对应加备注

〇、资料链接

 

一、背景

构建分区表时，删除了表的字段备注信息

1、查询语句

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 = 'ap'
  and position ('_2' in tablename) = 0
)
and c.relname = 'fact_purchase_receipt_order'

2、原来的表：

3、新的表：

元数据有实际表中没有了

二、思路：

1、旧表取数据，连表更新到新表

2、旧表取数据，构造添加字段备注的语句

 

三、实现