PostgreSQL视图中自动为列名添加表名前缀/后缀且不修改底层列名的实现方案

我完全懂你现在的困境：不想改底层列名（怕牵一发动全身重写Python接口），又不想硬编码列名（扩展性太差，新增列就得改代码），还得解决多表关联时同名id列冲突的问题。下面给你一个彻底自动化的解决方案——用PostgreSQL的PL/pgSQL写一个存储过程，自动生成并执行符合需求的视图创建语句。

核心思路 #

通过查询information_schema.columns动态获取目标表的所有列，自动拼接表名.列名 AS 表名_列名的格式，然后用动态SQL直接执行视图创建，全程不需要手动复制代码。

步骤1：创建自动生成视图的存储过程 #

CREATE OR REPLACE PROCEDURE create_standard_view()
LANGUAGE plpgsql
AS $$
DECLARE
    view_sql TEXT;
BEGIN
    -- 动态拼接创建视图的完整SQL
    SELECT 'CREATE OR REPLACE VIEW standard AS SELECT ' 
           || string_agg(
               -- 用%I处理特殊标识符，避免语法错误或注入风险
               format('%I.%I AS %I', table_name, column_name, table_name || '_' || column_name),
               ', ' ORDER BY ordinal_position  -- 保持原表列顺序
           )
           || ' FROM public.products 
               INNER JOIN public.collectors ON products.id = collectors.id_product 
               INNER JOIN public.measures ON collectors.measure = measures.id'
    INTO view_sql
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name IN ('products', 'collectors', 'measures');

    -- 执行生成的SQL语句
    EXECUTE view_sql;

    RAISE NOTICE '视图standard已成功创建/更新！';
END;
$$;

步骤2：执行存储过程完成视图创建 #

只需要调用一次存储过程，就能自动搞定视图的创建或更新：

CALL create_standard_view();

执行后你会收到PostgreSQL的提示NOTICE: 视图standard已成功创建/更新！，此时查询视图就能看到所有列都自动加上了表名前缀，比如products_id、collectors_id_product、measures_id，彻底解决同名列冲突问题。

方案优势 #

• 零硬编码：新增列或修改表结构后，只需要重新调用存储过程，视图会自动同步更新
• 安全可靠：用format('%I')处理标识符，即使表/列名包含特殊字符（比如空格、关键字）也能正常工作
• 完全自动化：不需要手动拼接或复制SQL，一次编写永久复用

补充：为什么你之前的子查询会报错？ #

你之前尝试的子查询select (select column_name from information_schema.columns...)返回了多行结果（所有列名），但主查询的每一行只能对应一个标量值，因此触发了“子查询返回多行结果”的错误。而我们的方案用string_agg把所有列名拼接成一个完整的SELECT子句，完美解决了这个问题。

内容的提问来源于stack exchange，提问作者Imeguras