PostgreSQL批量获取指定表最大创建日期并写入表的问题

你的核心问题是动态SQL中不能用参数占位符（$1/$2）替换表名、列名这类标识符，PostgreSQL要求标识符必须直接作为SQL的一部分（需安全转义），而非参数值。以下是修正后的完整代码及关键说明：

修正后的完整代码 #

DO $$ 
DECLARE 
    table_rec record ; 
    max_date TIMESTAMP; -- 改用TIMESTAMP类型更匹配日期列，避免TEXT转换问题
    cursor1 CURSOR FOR 
      SELECT DISTINCT c.table_schema, c.table_name
      FROM information_schema."columns" c 
      WHERE c.table_schema = 'datawarehouse' 
        AND c.table_name NOT LIKE 'partition%' 
        AND c.column_name = 'z_date_creation'; -- 精准匹配目标列，避免误选其他含creation的列
BEGIN
  -- 清空目标表（可选，根据需求决定是否保留历史数据）
  -- TRUNCATE TABLE ods.dates_derniere_maj;

  FOR table_rec IN cursor1 LOOP
    -- 用format函数安全拼接动态SQL，%I自动转义标识符（处理表名/列名含特殊字符的情况）
    EXECUTE format('SELECT MAX(z_date_creation) FROM %I.%I', 
                   table_rec.table_schema, table_rec.table_name)
    INTO max_date; -- 将查询结果赋值给max_date变量

    -- 插入结果到监控表
    INSERT INTO ods.dates_derniere_maj (schema_name, table_name, z_date_creation_max)
    VALUES (table_rec.table_schema, table_rec.table_name, max_date);
  END LOOP;
END $$;

关键修改说明 #

1. 标识符转义与动态SQL构建
   原代码尝试用参数传递表名/列名，这在PostgreSQL中不被允许——参数占位符仅用于传递值（如WHERE条件的参数），而非标识符。使用format()函数的%I占位符可以自动转义标识符，避免语法错误和SQL注入风险。

2. 变量类型优化
   将max_date的类型从TEXT改为TIMESTAMP，与z_date_creation列的类型匹配，避免不必要的类型转换问题。

3. 游标查询条件优化
   将c.column_name LIKE '%creation%'改为c.column_name = 'z_date_creation'，精准定位目标列，避免误选其他含"creation"的列（如update_creation_date等）。

4. 结果赋值方式
   使用EXECUTE ... INTO语法直接将动态查询的结果赋值给变量，这是PostgreSQL中获取动态SQL返回值的标准方式。

原代码错误点分析 #

• PREPARE nom_req (text, text) AS SELECT MAX($1) FROM $2：表名（$2）是标识符，不能用参数占位符替代，PostgreSQL会抛出语法错误。
• SELECT MAX(date_column) INTO max_date FROM CONCAT(from_clause)：date_column是字符串变量，会被当作字面量处理，而非列名；CONCAT(from_clause)无法直接作为表名使用，语法不合法。

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