PostgreSQL 清理表字段的备注脚本

select 'comment on column '||t.table_name||'.'||t.column_name||' is '''';' from user_col_comments t;

-- 创建表备注视图
CREATE 
        OR REPLACE VIEW table_comment AS SELECT
        n.nspname,
        relname AS tabname,
        obj_description ( relfilenode, 'pg_class' ) AS COMMENT,
        concat_ws ( '', 'COMMENT ON TABLE ', relname, ' is ''''' ) 
FROM
        pg_class
        C LEFT JOIN pg_catalog.pg_namespace n ON n.oid = C.relnamespace 
WHERE
        relkind = 'r' 
        AND nspname = 'public'
-- 创建字段备注视图
CREATE 
        OR REPLACE VIEW column_comment AS SELECT
        nspname,
        b.TABLE_NAME,
        A.attname,
        col_description ( A.attrelid, A.attnum ) AS COMMENT,
        concat_ws ( '', 'COMMENT ON COLUMN ', b.TABLE_NAME, '."', A.attname, '" is ''''' ) 
FROM
        pg_catalog.pg_attribute A,
        (
        SELECT C
                .oid,
                C.relname AS TABLE_NAME,
                n.nspname 
        FROM
                pg_catalog.pg_class
                C LEFT JOIN pg_catalog.pg_namespace n ON n.oid = C.relnamespace 
        WHERE
                C.relkind = 'r' 
                AND n.nspname = 'public' 
        ) b 
WHERE
        A.attrelid = b.oid 
        AND A.attnum > 0 
        AND NOT A.attisdropped 
        AND col_description ( A.attrelid, A.attnum ) IS NOT NULL 
ORDER BY
        b.TABLE_NAME,
        A.attnum;
        
--  执行脚本，循环删除表备注、字段备注      
DO $$ DECLARE
tableRecord record;
columnRecord record;
BEGIN
        FOR tableRecord IN ( SELECT * FROM table_comment )
        LOOP
                EXECUTE tableRecord.concat_ws;
        END LOOP;
        
        FOR columnRecord IN ( SELECT * FROM column_comment )
        LOOP
                EXECUTE columnRecord.concat_ws;
        END LOOP;
END $$;
-- 删除表备注视图、字段备注视图
DROP VIEW table_comment, column_comment;