如何生成将现有数据库视图转为等价表的SQL CREATE TABLE语句？

需求说明 #

我有一个应用连接到现有共享数据库，其中部分数据来自指向其他schema表的穿透视图。应用仅做数据读取，不关心这些是视图，且出于多种原因必须从视图而非底层表读取数据。为了本地运行及自动化测试，无需重建视图→表的两层结构，直接将这些视图转为可插入测试数据的基础表会更简便，需要生成对应的SQL CREATE TABLE 语句来实现这一转换。

示例场景 #

真实数据库中的表与视图定义：

CREATE TABLE other_schema.my_table (
    id integer NOT NULL,
    name text,
    other_value text
);

CREATE VIEW my_schema.my_table AS
 SELECT my_table.id,
    my_table.name
   FROM other_schema.my_table;

期望生成的本地测试表语句：

CREATE TABLE my_table (
    id integer NOT NULL,
    name text
);

实现方案 #

方法一：通过查询系统元数据表生成语句（以PostgreSQL为例） #

直接查询数据库的系统信息表，提取视图的列名、数据类型和非空约束，自动拼接成CREATE TABLE语句：

SELECT 'CREATE TABLE my_table (' || string_agg(
    quote_ident(column_name) || ' ' || data_type || 
    CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END,
    ', ' ORDER BY ordinal_position
) || ');' AS create_table_stmt
FROM information_schema.columns
WHERE table_schema = 'my_schema' AND table_name = 'my_table';

语句说明：

• 从information_schema.columns获取目标视图的所有列元数据
• quote_ident确保列名包含特殊字符时仍能正确识别
• 根据is_nullable值判断是否添加NOT NULL约束
• string_agg按列的原始顺序拼接所有列定义，生成完整的建表语句

方法二：利用数据库导出工具批量转换（以PostgreSQL的pg_dump为例） #

如果需要处理多个视图，可通过导出视图结构后批量替换：

1. 导出指定视图的结构：

pg_dump -d 你的数据库名 -n my_schema -t my_table --schema-only > view_def.sql

2. 打开导出的view_def.sql文件，将CREATE VIEW替换为CREATE TABLE，并删除AS SELECT ...及之后的所有内容，即可得到目标建表语句。

其他数据库适配思路（以MySQL为例） #

MySQL可通过类似的系统表查询生成语句：

SELECT CONCAT('CREATE TABLE my_table (', GROUP_CONCAT(
    CONCAT(COLUMN_NAME, ' ', DATA_TYPE, 
           IF(IS_NULLABLE = 'NO', ' NOT NULL', '')
    ) ORDER BY ORDINAL_POSITION SEPARATOR ', '
), ');') AS create_table_stmt
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'my_schema' AND TABLE_NAME = 'my_table';

内容的提问来源于stack exchange，提问作者M. Justin