PostgreSQL逆向工程：如何高效提取表结构的DDL定义

1. 为什么我们需要从PostgreSQL里“倒”出表结构？

你可能遇到过这样的场景：领导突然让你把生产环境的某个复杂表结构整理成文档，或者你需要把一个测试库里的表原封不动地搬到另一个环境。这时候，你总不能对着数据库管理工具，一个字段一个字段地手敲CREATE TABLE语句吧？那也太费劲了，而且容易出错。这个“把数据库里已经存在的表，还原成创建它的SQL语句”的过程，就是我们常说的逆向工程，或者更具体点，叫提取DDL（数据定义语言）。

在PostgreSQL的世界里，这事儿有点“灯下黑”。系统自带的pg_dump工具确实能导出整个库或单表的DDL，但它是个命令行工具，输出的是包含SET、CREATE等一堆信息的完整备份脚本。有时候，我们只是想在SQL客户端里，或者在自己的应用程序里，动态地、精准地获取某个表的创建语句，这时候pg_dump就显得有点“重”了。更让人纳闷的是，PostgreSQL提供了像pg_get_functiondef、pg_get_indexdef这样现成的函数来获取函数和索引的定义，偏偏没有一个官方的pg_get_tabledef来直接获取表定义。这就好比一个工具箱，螺丝刀、锤子都有，唯独缺了最常用的那把钳子。

所以，我们得自己动手，丰衣足食。通过查询PostgreSQL内部的系统目录表（也叫元数据表），比如pg_class、pg_attribute、pg_constraint等，把这些零散的信息像拼图一样组装成完整的CREATE TABLE语句。这不仅是解决一个具体的需求，更能帮你深入理解PostgreSQL内部是如何组织和存储表结构信息的，下次遇到元数据相关的问题，你就能自己排查了。

2. 核心原理：PostgreSQL把表结构“藏”在了哪里？

要自己提取DDL，首先得知道去哪儿找材料。PostgreSQL的所有家当，比如有哪些表、表里有哪些字段、字段是什么类型、有什么约束，都记录在它的一套“内部账本”里，这套账本就是系统目录（System Catalogs）。你可以把它们理解成一些特殊的表，只不过这些表里存的是数据库自身的结构信息，而不是你的业务数据。

我们需要重点关注的几张“账本”有：

• pg_class： 这张表记录了数据库中所有“关系型”对象，不仅是普通的表（relkind = 'r'），还有索引（i）、序列（S）、视图（v）、复合类型（c）等等。对我们来说，关键字段是oid（对象的唯一内部ID）、relname（对象名）、relnamespace（所属模式的OID，关联pg_namespace）、reloptions（表级选项，如填充因子fillfactor）。
• pg_attribute： 这是字段（列） 级别的核心表。每一行对应表中的一个字段。关键字段包括attrelid（所属表的OID，关联pg_class.oid）、attnum（字段序号，从1开始）、attname（字段名）、atttypid（数据类型的OID，关联pg_type）、attnotnull（是否非空）、atthasdef（是否有默认值）等。
• pg_type： 数据类型目录。通过pg_attribute.atttypid关联过来，可以获取到字段类型的可读名称（如text、int4）。
• pg_constraint： 存放所有约束信息，包括主键（contype = 'p'）、外键（f'）、唯一约束（'u'）、检查约束（'c'）。conrelid字段关联到所属的表。
• pg_attrdef： 存放字段的默认值表达式。通过adrelid（表OID）和adnum（字段序号）与pg_attribute关联。
• pg_namespace： 模式（Schema）目录。我们常用的public就是一个模式。通过它可以把pg_class.relnamespace的OID转换成模式名。

提取DDL的过程，本质上就是写一个复杂的SQL查询，把这些表通过JOIN连接起来，然后按照CREATE TABLE语法的规则，用字符串拼接函数（如format、string_agg）把信息组织成一条完整的SQL语句。这个过程需要考虑很多细节，比如字段的顺序、默认值的处理、约束的拼接方式、表是否是分区表等。

3. 实战：手把手编写一个可靠的DDL提取函数

理解了原理，我们来看一个我根据实际项目需求修改过的、功能比较完整的自定义函数。这个函数接受两个参数：模式名和表名，然后返回该表的CREATE TABLE语句。我会逐段解释，你甚至可以把它复制到你的数据库里直接使用。

3.1 函数骨架与参数

首先，我们创建一个返回text类型的SQL函数。使用STRICT关键字表示当输入参数为空时，函数直接返回空，避免错误。

CREATE OR REPLACE FUNCTION tabledef(schema_name text, table_name text)
RETURNS text
LANGUAGE sql
STRICT
AS $$
-- 函数体将在这里展开
$$;

3.2 第一步：收集字段基础信息（CTE: attrdef）

我们使用公共表表达式（CTE） 来让查询结构更清晰。第一个CTE叫attrdef，目标是获取表的所有字段及其核心属性。

WITH attrdef AS (
  SELECT
    n.nspname,
    c.relname,
    c.oid,
    -- 合并表本身和TOAST表的选项（如fillfactor）
    pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ') as relopts,
    c.relpersistence, -- 标识是普通表（'p'）、临时表（'t'）还是非日志表（'u'）
    a.attnum,
    a.attname,
    -- 格式化字段类型，包括长度修饰符（如varchar(255)）
    pg_catalog.format_type(a.atttypid, a.atttypmod) as atttype,
    -- 获取字段的默认值表达式
    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)
     FROM pg_catalog.pg_attrdef d
     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) as attdefault,
    a.attnotnull,
    -- 获取显式指定的排序规则（COLLATE）
    (SELECT c.collname
     FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
     WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) as attcollation,
    a.attidentity, -- 标识字段是否为IDENTITY列（'a'为ALWAYS, 'd'为BY DEFAULT）
    a.attgenerated -- 标识字段是否为生成列（'s'为STORED）
  FROM pg_catalog.pg_attribute a
  JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
  JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
  LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid) -- 关联TOAST表
  WHERE n.nspname = schema_name
    AND c.relname = table_name
    AND a.attnum > 0 -- 排除系统列（如ctid, xmin）
    AND NOT a.attisdropped -- 排除已被删除的列
  ORDER BY a.attnum -- 按列定义顺序排序
),

这段查询做了几件关键事：

1. 关联查询：把pg_attribute（字段）、pg_class（表）、pg_namespace（模式）连起来。
2. 过滤数据：通过WHERE条件精准定位到我们指定的那个表，并且只选取用户定义的字段（attnum > 0且未删除）。
3. 获取扩展属性：通过LEFT JOIN pg_class tc关联TOAST表，获取表级存储选项。format_type函数将类型OID转换成人可读的名字。子查询负责获取默认值和排序规则。

3.3 第二步：拼接单个字段的定义（CTE: coldef）

有了基础信息，下一步是把每个字段的信息拼接成字段名 数据类型 ...这样的子句。

coldef AS (
  SELECT
    attrdef.nspname,
    attrdef.relname,
    attrdef.oid,
    attrdef.relopts,
    attrdef.relpersistence,
    -- 核心拼接逻辑：使用format函数安全地处理标识符和值
    pg_catalog.format(
      '%I %s%s%s%s%s',
      attrdef.attname, -- 字段名，%I会加上引号（如果需要）
      attrdef.atttype, -- 已格式化的类型
      CASE WHEN attrdef.attcollation IS NULL THEN '' ELSE pg_catalog.format(' COLLATE %I', attrdef.attcollation) END,
      CASE WHEN attrdef.attnotnull THEN ' NOT NULL' ELSE '' END,
      CASE
        WHEN attrdef.attdefault IS NULL THEN ''
        WHEN attrdef.attgenerated = 's' THEN pg_catalog.format(' GENERATED ALWAYS AS (%s) STORED', attrdef.attdefault)
        WHEN attrdef.attgenerated <> '' THEN ' GENERATED AS NOT_IMPLEMENTED' -- 处理其他生成列类型（未来扩展）
        ELSE pg_catalog.format(' DEFAULT %s', attrdef.attdefault)
      END,
      CASE
        WHEN attrdef.attidentity <> '' THEN
          pg_catalog.format(' GENERATED %s AS IDENTITY',
            CASE attrdef.attidentity
              WHEN 'd' THEN 'BY DEFAULT'
              WHEN 'a' THEN 'ALWAYS'
              ELSE 'NOT_IMPLEMENTED'
            END)
        ELSE ''
      END
    ) as col_create_sql
  FROM attrdef
  ORDER BY attrdef.attnum
),

这里大量使用了CASE WHEN语句和pg_catalog.format函数。format函数类似于C语言的sprintf，%I用于安全地引用标识符（防止SQL注入和关键字冲突），%s用于插入字符串。这样，无论是普通的默认值、标识列（PostgreSQL 10+），还是存储生成列（PostgreSQL 12+），都能被正确地格式化成SQL片段。

3.4 第三步：聚合所有字段并加入主键（CTE: tabdef）

现在，我们需要把所有字段的定义用逗号连接起来，同时，主键约束（PRIMARY KEY）在PostgreSQL中通常是以表级约束的形式定义的，我们需要把它也加进来。

tabdef AS (
  SELECT
    coldef.nspname,
    coldef.relname,
    coldef.oid,
    coldef.relopts,
    coldef.relpersistence,
    -- 使用string_agg聚合所有列定义，用逗号+换行连接
    concat(
      string_agg(coldef.col_create_sql, E',\n '),
      -- 查找并拼接主键约束定义
      (SELECT concat(E',\n ', pg_get_constraintdef(oid))
       FROM pg_constraint
       WHERE contype = 'p' AND conrelid = coldef.oid)
    ) as cols_create_sql
  FROM coldef
  GROUP BY coldef.nspname, coldef.relname, coldef.oid, coldef.relopts, coldef.relpersistence
)

string_agg函数是这里的功臣，它把多行col_create_sql合并成一个字符串。然后，我们通过一个子查询去pg_constraint表里找到属于这个表（conrelid = oid）且类型为主键（contype='p'）的约束，用pg_get_constraintdef这个系统函数直接获取它的定义（如PRIMARY KEY (id)），拼接到字段列表的后面。

3.5 第四步：组装最终的CREATE TABLE语句

最后，我们把所有部分整合到最终的CREATE TABLE语句中。这一步还需要处理分区表和表选项。

SELECT FORMAT(
  'CREATE%s TABLE %I.%I%s%s%s;',
  -- 处理表类型：TEMPORARY 或 UNLOGGED
  CASE tabdef.relpersistence
    WHEN 't' THEN ' TEMP'
    WHEN 'u' THEN ' UNLOGGED'
    ELSE ''
  END,
  tabdef.nspname,
  tabdef.relname,
  -- 关键判断：如果是分区，使用PARTITION OF语法；否则使用字段定义列表
  COALESCE(
    (SELECT FORMAT(
        E'\n PARTITION OF %I.%I %s\n',
        pn.nspname,
        pc.relname,
        pg_get_expr(c.relpartbound, c.oid) -- 分区边界表达式
     )
     FROM pg_class c
     JOIN pg_inherits i ON c.oid = i.inhrelid
     JOIN pg_class pc ON pc.oid = i.inhparent
     JOIN pg_namespace pn ON pn.oid = pc.relnamespace
     WHERE c.oid = tabdef.oid),
    FORMAT(E' (\n %s\n)', tabdef.cols_create_sql) -- 普通表的字段定义部分
  ),
  -- 表级WITH选项，如fillfactor=90
  CASE WHEN tabdef.relopts <> '' THEN FORMAT(' WITH (%s)', tabdef.relopts) ELSE '' END,
  -- 如果表是分区父表，添加PARTITION BY子句
  COALESCE(E'\nPARTITION BY ' || pg_get_partkeydef(tabdef.oid), '')
) as table_create_sql
FROM tabdef;

这部分逻辑最复杂，也最能体现逆向工程的完整性：

1. 判断分区子表：通过查询pg_inherits系统表，判断当前表是否是某个父表的分区。如果是，则生成PARTITION OF parent_table FOR VALUES ...这样的语法，这是PostgreSQL声明式分区的特性。
2. 添加表选项：将之前获取的relopts（如fillfactor=90, autovacuum_enabled=false）以WITH (options)的形式加上。
3. 处理分区父表：使用pg_get_partkeydef系统函数，如果当前表是分区父表，则获取其分区键定义（如PARTITION BY RANGE (created_at)），并拼接到语句末尾。

至此，一个健壮的DDL提取函数就完成了。你可以这样调用它：

SELECT tabledef('public', 'my_table');

它就会返回一个完整的、可以直接执行的CREATE TABLE语句。

4. 避坑指南与进阶技巧

自己写函数虽然灵活，但实践中我也踩过不少坑。这里分享几个关键点，能帮你省下不少调试时间。

坑1：字段顺序和隐藏列 一定要按pg_attribute.attnum排序，这是字段定义的物理顺序。务必过滤掉attnum <= 0的系统列（如ctid, xmin, cmin等），以及attisdropped = true的已删除列，否则生成的DDL会包含无效信息。

坑2：默认值中的函数和运算符 从pg_attrdef取出的默认值表达式，是数据库内部存储的形式。虽然pg_get_expr能将其解析为文本，但如果默认值中包含了自定义函数或位于非search_path中的运算符，在另一个没有相同环境的数据中执行这个DDL可能会失败。对于跨环境迁移，需要额外小心。

坑3：外键约束和依赖关系 上面的函数主要处理了主键。一个完整的表定义还可能包含外键（FOREIGN KEY）、唯一约束（UNIQUE）、检查约束（CHECK）。要获取这些，你需要扩展查询，从pg_constraint中根据contype筛选，并用pg_get_constraintdef获取定义。注意，外键涉及另一个表，在迁移时需要考虑创建顺序，或者先创建没有外键的表，最后再添加外键约束。

坑4：索引、注释和所有者 我们提取的是最核心的表结构DDL。一个表完整的“克隆”通常还包括：

• 索引：使用pg_get_indexdef函数可以轻松获取。
• 注释：存储在pg_description中，需要单独提取并生成COMMENT ON语句。
• 表所有者：信息在pg_class.relowner，关联pg_authid。生成ALTER TABLE ... OWNER TO ...语句需要这个。
• 权限（GRANTS）：存储在pg_class相关的权限系统表中。

对于这些，更全面的做法是直接使用pg_dump --schema-only，或者使用一些成熟的开源工具（如pg_dump的代码库本身就是最好的参考）。但在应用程序内部动态获取核心表结构，我们自研的函数已经足够强大和高效。

进阶技巧：封装成视图或工具 如果你经常需要这个功能，可以把这个函数部署到你的常用数据库模板中。更进一步，可以写一个简单的Shell脚本或Python程序，连接数据库，调用这个函数，并将结果格式化输出或保存到文件。这样，你就拥有了一个轻量级的、可编程的pg_dump替代品，特别适合集成到CI/CD流程中，做数据库结构的版本比对或自动化归档。