Snowflake存储过程执行报错：无效标识符REC.TABLE_NAME求助

需求：对Snowflake指定schema下所有表中的varchar、char、text类型列执行TRIM操作，存储过程创建成功，但执行时触发错误：

Error: invalid identifier 'REC.TABLE_NAME' (line 33)

原存储过程代码：

CREATE OR REPLACE PROCEDURE trim_all_varchars(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  v_table_name   STRING;
  v_column_name  STRING;
  v_schema_name  STRING := UPPER(COALESCE(schema_name, CURRENT_SCHEMA()));
  v_sql_cmd      STRING;
  v_processed    NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT table_name, column_name
    FROM information_schema.columns
    WHERE table_schema = v_schema_name
      AND (
           data_type ILIKE 'VARCHAR%' 
        OR data_type ILIKE 'CHAR%'
        OR data_type ILIKE 'TEXT'
      )
    ORDER BY table_name, ordinal_position
  )
  DO
    -- access record fields as rec.TABLE_NAME / rec.COLUMN_NAME
    v_table_name  := rec.TABLE_NAME;
    v_column_name := rec.COLUMN_NAME;

    -- build UPDATE safe-quoted (schema & table & column)
    v_sql_cmd := 
      'UPDATE "' || CURRENT_DATABASE() || '"."' || v_schema_name || '"."' || v_table_name || '"' ||
      ' SET "' || v_column_name || '" = TRIM("' || v_column_name || '")' ||
      ' WHERE "' || v_column_name || '" IS NOT NULL AND "' || v_column_name || '" <> TRIM("' || v_column_name || '")';

    -- execute the update
    EXECUTE IMMEDIATE v_sql_cmd;

    v_processed := v_processed + 1;
  END FOR;

  RETURN 'Trimmed VARCHAR-like columns in ' || v_processed || ' column(s) in schema ' || v_schema_name;
END;
$$;

错误原因 #

• 核心问题：游标字段引用大小写不匹配。在Snowflake SQL存储过程的FOR循环中，游标记录的字段名严格遵循SELECT语句中指定的大小写。你在查询中写的是SELECT table_name, column_name（小写），但引用时用了rec.TABLE_NAME和rec.COLUMN_NAME（大写），导致Snowflake无法识别该标识符。
• 次要问题：构建SQL语句时使用了HTML转义的双引号"，这会导致生成的SQL语法错误，应替换为实际的双引号"。

修复方案 #

修改两处内容即可解决问题：

1. 将游标字段引用改为小写：rec.table_name和rec.column_name
2. 将SQL语句中的"替换为"

修复后的完整代码：

CREATE OR REPLACE PROCEDURE trim_all_varchars(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  v_table_name   STRING;
  v_column_name  STRING;
  v_schema_name  STRING := UPPER(COALESCE(schema_name, CURRENT_SCHEMA()));
  v_sql_cmd      STRING;
  v_processed    NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT table_name, column_name
    FROM information_schema.columns
    WHERE table_schema = v_schema_name
      AND (
           data_type ILIKE 'VARCHAR%' 
        OR data_type ILIKE 'CHAR%'
        OR data_type ILIKE 'TEXT'
      )
    ORDER BY table_name, ordinal_position
  )
  DO
    v_table_name  := rec.table_name;
    v_column_name := rec.column_name;

    -- 构建带正确引号的UPDATE语句
    v_sql_cmd := 
      'UPDATE "' || CURRENT_DATABASE() || '"."' || v_schema_name || '"."' || v_table_name || '"' ||
      ' SET "' || v_column_name || '" = TRIM("' || v_column_name || '")' ||
      ' WHERE "' || v_column_name || '" IS NOT NULL AND "' || v_column_name || '" <> TRIM("' || v_column_name || '")';

    EXECUTE IMMEDIATE v_sql_cmd;

    v_processed := v_processed + 1;
  END FOR;

  RETURN 'Trimmed VARCHAR-like columns in ' || v_processed || ' column(s) in schema ' || v_schema_name;
END;
$$;

可选优化建议 #

当前代码会对每个列单独执行一次UPDATE，效率较低。可以改为按表分组，一次性更新表中所有符合条件的列，减少执行次数：

CREATE OR REPLACE PROCEDURE trim_all_varchars(schema_name STRING)
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
DECLARE
  v_table_name   STRING;
  v_set_clause   STRING;
  v_schema_name  STRING := UPPER(COALESCE(schema_name, CURRENT_SCHEMA()));
  v_sql_cmd      STRING;
  v_processed_tables NUMBER := 0;
  v_processed_columns NUMBER := 0;
BEGIN
  -- 按表分组获取所有需要修剪的列
  FOR rec IN (
    SELECT 
      table_name,
      LISTAGG('"' || column_name || '" = TRIM("' || column_name || '")', ', ') AS set_clause,
      COUNT(*) AS column_count
    FROM information_schema.columns
    WHERE table_schema = v_schema_name
      AND (
           data_type ILIKE 'VARCHAR%' 
        OR data_type ILIKE 'CHAR%'
        OR data_type ILIKE 'TEXT'
      )
    GROUP BY table_name
    ORDER BY table_name
  )
  DO
    v_table_name := rec.table_name;
    v_set_clause := rec.set_clause;
    v_processed_columns := v_processed_columns + rec.column_count;

    -- 构建UPDATE语句，一次性更新表中所有目标列
    v_sql_cmd := 
      'UPDATE "' || CURRENT_DATABASE() || '"."' || v_schema_name || '"."' || v_table_name || '"' ||
      ' SET ' || v_set_clause ||
      ' WHERE EXISTS (' ||
      '   SELECT 1 FROM "' || CURRENT_DATABASE() || '"."' || v_schema_name || '"."' || v_table_name || '" t' ||
      '   WHERE ' || LISTAGG('t."' || column_name || '" IS NOT NULL AND t."' || column_name || '" <> TRIM(t."' || column_name || '")', ' OR ') ||
      ')';

    EXECUTE IMMEDIATE v_sql_cmd;

    v_processed_tables := v_processed_tables + 1;
  END FOR;

  RETURN 'Trimmed ' || v_processed_columns || ' VARCHAR-like columns across ' || v_processed_tables || ' tables in schema ' || v_schema_name;
END;
$$;

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