Snowflake中动态从多表生成单一结果集的实现问题

你的代码存在几个关键问题导致执行失败，以下是问题点和修正后的实现：

核心问题 #

1. 循环语法与数组索引错误：Snowflake数组采用0基索引，原代码中ARRAY_RANGE(1, ARRAY_SIZE(table_names))会跳过第一个表，且FOR i IN 1 : ARRAY_RANGE(...)的语法不符合Snowflake匿名块规范。
2. 动态SQL变量绑定错误：EXECUTE IMMEDIATE的INTO子句不应拼接到SQL字符串内部，否则无法正确绑定:min_date变量。
3. 缺少结果输出逻辑：代码未将收集的数组转换为可查看的表结构。

修正后代码 #

无需存储过程或临时表，直接执行即可输出目标结果：

DECLARE
   resultItems ARRAY;
   table_names ARRAY;
   table_name STRING;
   min_date TIMESTAMP;

BEGIN
   -- 获取MY_SCHEMA下所有表名并转为数组
   SELECT ARRAY_AGG(TABLE_NAME) INTO table_names
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = 'MY_SCHEMA'; 

   resultItems := ARRAY_CONSTRUCT();

   -- 遍历数组（覆盖所有0基索引）
   FOR i IN ARRAY_RANGE(0, ARRAY_SIZE(table_names)) LOOP
       table_name := table_names[i];
   
       -- 修正动态SQL的变量绑定逻辑
       EXECUTE IMMEDIATE 
           'SELECT MIN(pub_date) FROM MY_SCHEMA.' || table_name 
       INTO :min_date;
      
       -- 将单表结果存入数组
       resultItems := ARRAY_APPEND(resultItems, OBJECT_CONSTRUCT(
            'table_name', table_name, 'pub_date', min_date));
   END LOOP;
   
   -- 将数组展开为标准两列表格输出
   SELECT 
       value:table_name::STRING AS TABLE_NAME,
       value:pub_date::TIMESTAMP AS PUB_DATE
   FROM TABLE(FLATTEN(input => :resultItems));
END;

关键修正说明 #

• 循环范围改为ARRAY_RANGE(0, ARRAY_SIZE(table_names))，确保遍历所有表。
• 将INTO :min_date移至动态SQL字符串外部，保证变量正确绑定。
• 使用FLATTEN函数将数组转换为常规表结构，直接输出TABLE_NAME和PUB_DATE列。

额外注意事项 #

• 需确保MY_SCHEMA下所有目标表都存在pub_date列，否则会抛出列不存在错误。若存在无该列的表，可提前通过INFORMATION_SCHEMA.COLUMNS过滤掉，例如：

  SELECT ARRAY_AGG(TABLE_NAME) INTO table_names
  FROM INFORMATION_SCHEMA.TABLES t
  JOIN INFORMATION_SCHEMA.COLUMNS c 
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
    AND t.TABLE_NAME = c.TABLE_NAME
  WHERE t.TABLE_SCHEMA = 'MY_SCHEMA'
    AND c.COLUMN_NAME = 'PUB_DATE';
  

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