Snowflake动态UNPIVOT存储过程语法优化咨询

优化后的存储过程 #

/* 动态UNPIVOT表
 * 无需指定列名即可对表进行动态UNPIVOT，将所有列转换为VARCHAR类型后返回列名和列值
 */
CREATE OR REPLACE PROCEDURE dynamic_unpivot(table_name TEXT)
    RETURNS TABLE(col_name STRING, col_val STRING)
    LANGUAGE SQL
    COMMENT = '动态UNPIVOT表'
AS
DECLARE
    res RESULTSET;
    pivot_cols TEXT;
    recast_cols TEXT;
    select_statement TEXT;
BEGIN
    -- 从INFORMATION_SCHEMA获取列信息，直接生成列列表和带类型转换的列字符串
    SELECT 
        LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS cols,
        LISTAGG(COLUMN_NAME || '::VARCHAR AS ' || COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS recast_cols
    INTO pivot_cols, recast_cols
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = CURRENT_SCHEMA()
      AND TABLE_NAME = :table_name;

    -- 构建UNPIVOT查询语句
    select_statement := 'SELECT * FROM (' ||
                            'SELECT ' || :recast_cols || ' FROM ' || IDENTIFIER(:table_name) ||
                        ') t_recast UNPIVOT (col_val FOR col_name IN (' || :pivot_cols || '))';
    
    res := (EXECUTE IMMEDIATE :select_statement);
    RETURN TABLE(res);
END;

测试代码（保留原逻辑） #

-- 创建临时测试表
CREATE OR REPLACE TEMPORARY TABLE tmp_t1 (col_a VARCHAR, col_b NUMBER);

-- 插入测试数据
INSERT INTO tmp_t1 (col_a, col_b) VALUES
   ('one', 1),
   ('two', 2),
   ('three', 3),
   ('four', 4);

-- 调用存储过程并查看结果
CALL dynamic_unpivot('tmp_t1');
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) ORDER BY col_name;

-- 清理资源
DROP PROCEDURE IF EXISTS dynamic_unpivot(TEXT);
DROP TABLE IF EXISTS tmp_t1;

优化点说明 #

1. 减少字符串操作依赖

   • 替换原有的SHOW COLUMNS + RESULT_SCAN方案，改用INFORMATION_SCHEMA.COLUMNS直接查询列信息，避免依赖会话级的查询结果扫描，逻辑更稳定。
   • 直接在查询中通过LISTAGG同时生成原始列列表和带类型转换的列字符串，无需后续的字符串替换操作，减少出错风险。

2. 替换REGEXP_REPLACE的优雅实现

   • 去掉了原代码中用于列转换的REGEXP_REPLACE调用，改为在LISTAGG中直接拼接COLUMN_NAME::VARCHAR AS COLUMN_NAME，逻辑更直观，也避免了正则表达式可能带来的边界问题（比如列名包含特殊字符时正则匹配失效）。
   • 利用ORDINAL_POSITION保证列的顺序和原表一致，符合预期行为。

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