PostGreSQL----DDL获取方式

这个sql我是将建表语句等数据拼接成 "create table"的语法,后面是表的注释,和列的注释,以及索引等信息.

 WITH all_tables AS (
            -- 从 pg_class 系统表中选择表的 OID、模式名和表名
            SELECT
                c.oid,
                n.nspname AS schema_name,
                c.relname AS table_name
            FROM
                pg_class c
                -- 连接 pg_namespace 系统表来获取模式信息
                JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE
                -- 过滤出普通表（relkind = 'r'）且不是系统模式（如 pg_catalog, information_schema）的表
                c.relkind = 'r'
                AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        ),
        table_info AS (
            -- 为每个表生成创建语句
            SELECT
                at.schema_name,
                at.table_name,
                'CREATE TABLE ' || quote_ident(at.schema_name) || '.' || quote_ident(at.table_name) || ' (' ||
                string_agg(
                    quote_ident(a.attname) || ' ' ||
                    -- 转换 PostgreSQL 内置数据类型为常见类型
                    CASE
                        WHEN format_type(a.atttypid, a.atttypmod) = 'character varying' THEN 'VARCHAR'
                        WHEN format_type(a.atttypid, a.atttypmod) = 'character' THEN 'CHAR'
                        WHEN format_type(a.atttypid, a.atttypmod) = 'text' THEN 'TEXT'
                        WHEN format_type(a.atttypid, a.atttypmod) = 'integer' THEN 'INT'
                        WHEN format_type(a.atttypid, a.atttypmod) = 'bigint' THEN 'BIGINT'
                        WHEN format_type(a.atttypid, a.atttypmod) = 'numeric' THEN 'NUMERIC'
                        WHEN format_type(a.atttypid, a.atttypmod) = 'timestamp without time zone' THEN 'TIMESTAMP'
                        ELSE format_type(a.atttypid, a.atttypmod)
                    END ||
                    CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||
                    CASE WHEN a.atthasdef THEN ' DEFAULT ' || pg_get_expr(d.adbin, d.adrelid) ELSE '' END,
                    ', '
                ) ||
                ');' AS create_table_sql
            FROM
                all_tables at
                -- 连接 pg_attribute 系统表获取列信息
                JOIN pg_attribute a ON at.oid = a.attrelid
                -- 左连接 pg_attrdef 系统表获取列的默认值信息
                LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
            WHERE
                a.attnum > 0
                AND NOT a.attisdropped
            GROUP BY
                at.schema_name, at.table_name, at.oid
        ),
        table_comment AS (
            -- 获取每个表的注释
            SELECT
                at.schema_name,
                at.table_name,
                'COMMENT ON TABLE ' || quote_ident(at.schema_name) || '.' || quote_ident(at.table_name) || ' IS ''' ||
                COALESCE(pg_description.description, '') || ''';' AS comment_sql
            FROM
                all_tables at
                -- 左连接 pg_description 系统表获取表注释
                LEFT JOIN pg_description ON at.oid = pg_description.objoid AND pg_description.objsubid = 0
        ),
        column_comments AS (
            -- 获取每个表的列注释
            SELECT
                at.schema_name,
                at.table_name,
                'COMMENT ON COLUMN ' || quote_ident(at.schema_name) || '.' || quote_ident(at.table_name) || '.' || quote_ident(a.attname) || ' IS ''' ||
                COALESCE(pg_description.description, '') || ''';' AS comment_sql
            FROM
                all_tables at
                -- 连接 pg_attribute 系统表获取列信息
                JOIN pg_attribute a ON at.oid = a.attrelid
                -- 左连接 pg_description 系统表获取列注释
                LEFT JOIN pg_description ON a.attrelid = pg_description.objoid AND a.attnum = pg_description.objsubid
            WHERE
                a.attnum > 0
                AND NOT a.attisdropped
        ),
        index_info AS (
            -- 获取每个表的索引信息
            SELECT
                at.schema_name,
                at.table_name,
                pg_get_indexdef(idx.indexrelid) AS index_definition
            FROM
                all_tables at
                -- 连接 pg_index 系统表获取索引信息
                JOIN pg_index idx ON at.oid = idx.indrelid
                -- 连接 pg_class 系统表获取索引的类信息
                JOIN pg_class c ON idx.indexrelid = c.oid
                -- 连接 pg_namespace 系统表获取索引的模式信息
                JOIN pg_namespace n ON c.relnamespace = n.oid
            WHERE
                n.nspname NOT IN ('pg_catalog', 'information_schema')
        )
        SELECT
            ti.schema_name,
            ti.table_name,
            ti.create_table_sql,
            tc.comment_sql AS table_comment,
            string_agg(cc.comment_sql, E'\n') AS column_comments,
            string_agg(ii.index_definition, E'\n') AS index_definitions,
            -- 当前时间
            current_date as crtime_data
        FROM
            table_info ti
            -- 连接 table_comment 获取表注释
            JOIN table_comment tc ON ti.schema_name = tc.schema_name AND ti.table_name = tc.table_name
            -- 左连接 column_comments 获取列注释
            LEFT JOIN column_comments cc ON ti.schema_name = cc.schema_name AND ti.table_name = cc.table_name
            -- 左连接 index_info 获取索引信息
            LEFT JOIN index_info ii ON ti.schema_name = ii.schema_name AND ti.table_name = ii.table_name
        GROUP BY
            ti.schema_name, ti.table_name, ti.create_table_sql, tc.comment_sql
        ORDER BY
            ti.schema_name, ti.table_name