Snowflake如何在UDF或存储过程中使用参数返回表数据

以下两种方案均可以满足你不用为每个客户单独创建函数、直接返回结构化表数据的需求：

方案1：SQL动态表函数（优先推荐） #

Snowflake 新版已经支持在SQL UDTF中使用EXECUTE IMMEDIATE执行动态SQL，可以直接解决你之前提到的FROM子句不能用参数的限制。

实现代码 #

CREATE OR REPLACE FUNCTION get_customer_mv_data(
    customer_id STRING, 
    optional_filter STRING DEFAULT ''
)
RETURNS TABLE (
    -- 此处统一填写所有客户物化视图的公共输出字段，类型、名称必须完全一致
    id STRING,
    business_data STRING,
    create_time TIMESTAMP_NTZ,
    amount NUMBER(18,2)
)
LANGUAGE SQL
AS
$$
DECLARE
    -- 替换为你实际的物化视图命名规则
    target_mv_name STRING := 'MV_CUST_' || UPPER(customer_id);
    base_query STRING := 'SELECT id, business_data, create_time, amount FROM ' || target_mv_name;
    filter_condition STRING := CASE 
        WHEN optional_filter != '' THEN ' WHERE business_tag = ''' || REPLACE(optional_filter, '''', '''''') || ''''
        ELSE ''
    END;
    full_exec_sql STRING := base_query || filter_condition;
BEGIN
    -- 直接返回动态SQL执行的表结果
    RETURN TABLE(EXECUTE IMMEDIATE full_exec_sql);
END;
$$;

调用方式 #

API侧直接执行普通查询即可，返回结果是标准结构化表，无需额外解析：

SELECT * FROM TABLE(get_customer_mv_data('CUST001', '2024年业务'));

注意事项 #

• 所有客户对应的物化视图输出字段的名称、类型、顺序必须完全统一
• 代码中已添加单引号转义逻辑，避免SQL注入风险，如有其他特殊入参可自行补充转义规则
• 需要给函数调用角色开放所有客户物化视图的查询权限、以及函数的执行权限

方案2：存储过程+RESULT_SCAN（兼容老版本） #

如果你使用的Snowflake版本不支持UDTF内执行动态SQL，可以用这个兼容方案，不需要消费侧解析VARIANT。

实现代码 #

CREATE OR REPLACE PROCEDURE get_customer_mv_data_sp(
    customer_id STRING, 
    optional_filter STRING DEFAULT ''
)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
    target_mv_name STRING := 'MV_CUST_' || UPPER(customer_id);
    base_query STRING := 'SELECT id, business_data, create_time, amount FROM ' || target_mv_name;
    filter_condition STRING := CASE 
        WHEN optional_filter != '' THEN ' WHERE business_tag = ''' || REPLACE(optional_filter, '''', '''''') || ''''
        ELSE ''
    END;
    full_exec_sql STRING := base_query || filter_condition;
BEGIN
    EXECUTE IMMEDIATE full_exec_sql;
    -- 返回当前查询的ID，供后续取数用
    RETURN LAST_QUERY_ID();
END;
$$;

调用方式 #

API侧分两步调用即可，返回结果同样是标准结构化表：

1. 调用存储过程获取查询ID：

CALL get_customer_mv_data_sp('CUST001', '2024年业务');

2. 用查询ID拉取结果：

SELECT * FROM TABLE(RESULT_SCAN('{上一步返回的查询ID}'));

注意事项 #

• RESULT_SCAN默认保留24小时内的查询结果，完全满足API实时调用的需求
• 同样需要保证所有客户物化视图的输出结构统一

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