Snowflake函数能否返回查询结果？如何将存储过程改造为函数

核心限制说明 #

Snowflake的标量UDF不支持在内部执行SQL查询，snowflake.execute是存储过程专属的API，你直接将存储过程逻辑迁移到UDF必然会触发snowflake is not defined的报错。
存储过程只能单独CALL调用，不支持嵌入SELECT语句对表数据逐行处理，这是Snowflake原生的设计差异，不是功能缺失。

可行实现方案 #

方案1：将匹配位置列表作为参数传入UDF（推荐适配你的原有JS逻辑） #

你不需要在UDF内部查询位置表，提前将所有匹配项聚合为数组作为参数传入UDF即可，代码示例如下：

1. 创建适配参数的JS UDF

create or replace function GeoLocator (input_str VARCHAR(16777216), location_list ARRAY )
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
AS $$
// 遍历所有位置匹配规则
for (var i=0; i < LOCATION_LIST.length; i++) {
    const matchResult = INPUT_STR.match(LOCATION_LIST[i]);
    if (matchResult !== null) {
        return matchResult[0];
    }
}
// 无匹配返回空
return null;
$$;

2. 调用方式

WITH california_locations AS (
    -- 提前聚合所有需要匹配的加州位置为数组，仅执行一次查询
    SELECT ARRAY_AGG(LOCATION) AS loc_arr
    FROM A_TABLE_THAT_HAS_ALL_LOCATIONS
    WHERE "STATE" = 'California'
)
SELECT
    GeoLocator(your_table.COLUMN_NAMEHERE, california_locations.loc_arr) AS Location
FROM your_table, california_locations;

方案2：原生SQL实现（性能最优） #

如果你的逻辑仅为正则子串匹配，可以直接用Snowflake原生SQL函数实现，不需要JS UDF，执行效率更高：

WITH california_locations AS (
    -- 把所有位置拼接为正则表达式，用|分隔
    SELECT '(' || LISTAGG(LOCATION, '|') || ')' AS loc_regex
    FROM A_TABLE_THAT_HAS_ALL_LOCATIONS
    WHERE "STATE" = 'California'
)
SELECT
    REGEXP_SUBSTR(your_table.COLUMN_NAMEHERE, california_locations.loc_regex) AS Location
FROM your_table, california_locations;

方案3：存储过程批量写入结果表 #

如果你的逻辑非常复杂，必须在过程内执行多次SQL，可以用存储过程全量处理所有待匹配数据，将结果写入一张固定的结果表，后续直接查询该表即可，适合离线批量处理场景。

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