获取将现有视图转换为等效表所需的SQL语句

后端开发 2026-07-09

我有一个应用程序，它连接到一个已有的共享数据库，该数据库中的部分数据来自指向另一个模式中表的穿透视图。应用程序并不在意它们是视图；它只是从中读取数据。出于各种原因，我们希望应用程序直接从这些视图读取数据，而不是从另一模式中的底层表读取。

在本地运行和自动化测试时，我并不想再现两级的视图到表的动态关系。如果这些视图只是普通的表，我就可以向它们插入测试数据，测试起来会更直接。

我希望用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
);

解决方案

这并非完全万无一失（例如，我没有对保留关键字的列名进行转义），但从 information_schema.columns 读取并拼接成一个 CREATE TABLE 语句对我有用：

SELECT format(E'CREATE TABLE %s (\n    %s\n);\n', table_name,
string_agg(format('%s %s %s', column_name, data_type,
  CASE WHEN (is_nullable = 'YES') THEN 'NULL' else 'NOT NULL' END),
  E',\n    ' order by ordinal_position))
FROM information_schema.columns WHERE table_schema = 'other_schema'
AND table_name = 'my_table' GROUP BY table_name;

请注意，这通常会将所有列设为可为空，即使底层表的列不是可空的，因为大多数PostgreSQL视图列在底层表的列被标记为可空时也会被注册为可空。因此，对于此类情形，去掉 NULL / NOT NULL 的输出，并仅接受 CREATE TABLE 语句的默认 NOT NULL 行为，可能就足够了。

另外，这也不会创建任何索引或其他约束。

SQL