Snowflake SQL UDF动态传列名报错：无法识别数值'Col'

问题背景 #

我需要创建一个Snowflake SQL UDF来计算某列所有值的乘积并返回结果：

1. 先采用硬编码表名和列名的方式创建UDF，执行正常：

CREATE OR REPLACE FUNCTION PRODUCT_OF_COL_VAL()
RETURNS FLOAT
LANGUAGE SQL
AS
$$
    SELECT EXP(SUM(LN(COL))) AS RESULT FROM SCHEMA.SAMPLE_TABLE
$$

2. 但硬编码不符合需求，尝试动态传入列名时，执行报错 Numeric Value 'Col' is not recognized：

CREATE OR REPLACE FUNCTION PRODUCT_OF_COL_VAL(COL VARCHAR)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
SELECT EXP(SUM(LN(COL))) AS RESULT FROM SCHEMA.SAMPLE_TABLE
$$

注：目标列的数据类型为NUMBER(38,6)，实际需要执行的逻辑是EXP(SUM(LN(TO_DOUBLE(COL))))。

报错原因 #

两种场景的核心区别在于Snowflake对COL的解析逻辑不同：

• 硬编码场景中，COL被直接识别为SCHEMA.SAMPLE_TABLE的列名，Snowflake会读取该列的数值，依次执行LN()、SUM()、EXP()运算，逻辑正常执行。
• 动态传参的SQL UDF场景中，COL是字符串类型的参数（比如调用时传入'MY_COLUMN'），Snowflake会把它当成普通字符串值传入LN()函数，而LN()仅支持数值类型运算，因此抛出“无法识别数值'Col'”的错误——本质是试图计算字符串的对数，而非列值的对数。

解决方案 #

SQL UDF不支持动态解析列名，需改用JavaScript UDF实现动态SQL逻辑，示例代码如下：

CREATE OR REPLACE FUNCTION PRODUCT_OF_COL_VAL(COL VARCHAR)
RETURNS FLOAT
LANGUAGE JAVASCRIPT
AS
$$
    // 拼接动态SQL，用escapeIdentifier处理特殊列名，避免语法错误或注入风险
    var sql = `SELECT EXP(SUM(LN(TO_DOUBLE(${snowflake.escapeIdentifier(COL)})))) FROM SCHEMA.SAMPLE_TABLE`;
    var stmt = snowflake.createStatement({sqlText: sql});
    var resultSet = stmt.execute();
    resultSet.next();
    return resultSet.getColumnValue(1);
$$

代码说明： #

• 通过JavaScript拼接SQL语句，将传入的列名参数作为表的列标识符插入SQL中。
• 使用snowflake.escapeIdentifier()处理列名，确保列名包含特殊字符（如空格、大写字母）时能正常解析，同时避免SQL注入风险。
• 执行拼接后的SQL并返回计算结果。

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