[PG]生成表注释SQL

由于PostgreSQL无论是表还是字段的注释是通过 comment on语句来处理的。

可通过如下语句生成表的注释SQL执行语句：

SELECT 
  CONCAT('COMMENT ON ', "type", '  模式.', "name", ' IS "', "comment", '";') AS "comment_sql"
FROM (
  SELECT 
    0 AS num, 
    c.relname AS "name", 
    'TABLE' AS "type", 
    obj_description(c.oid,'pg_class') AS "comment"
  FROM pg_class c
  WHERE c.relname = '表名'

UNION

  SELECT 
    a.attnum AS num, 
    CONCAT(c.relname, '.', a.attname) AS "name", 
    'COLUMN' AS "type", 
    col_description(c.oid, a.attnum) AS "comment"
  FROM pg_class AS c, pg_attribute AS a 
  WHERE c.oid = a.attrelid AND a.attnum > 0
  AND c.relname = '表名'

) AS U
ORDER BY num

替换语句中的模式和表名即可。

例如，模式为biz，表名为d_calendar的执行结果：

 

注意：该语句有个问题，就是如果多个模式下有相同表名的情况，将导致sql执行结果重复，这是由于该语句没有区分模式。目前还没想到如何解决，希望有人可以留言指导，谢谢