Snowflake自定义函数无法返回多行数据的问题求助

原函数是标量函数，要求必须返回单一值，当子查询返回多行时就会触发Single-row subquery returns more than one row错误。改成表函数后提示不存在，是因为调用方式不符合表函数的规则。以下是两种场景的解决办法：

一、继续用标量函数（返回单个值） #

如果需求是返回单一结果，需要处理多行匹配的情况，可选以下方式：

• 只返回匹配到的第一行
  在查询末尾添加LIMIT 1，确保只返回一行：

  CREATE OR REPLACE FUNCTION test (field_value STRING)
  RETURNS STRING
  AS
  $$
    SELECT value_x
    FROM mapping_table_test 
    WHERE field_value = num1
    LIMIT 1;
  $$;
  

• 拼接所有匹配值为单个字符串
  使用LISTAGG将多行结果合并成一个用分隔符连接的字符串：

  CREATE OR REPLACE FUNCTION test (field_value STRING)
  RETURNS STRING
  AS
  $$
    SELECT LISTAGG(value_x, ', ') WITHIN GROUP (ORDER BY value_x)
    FROM mapping_table_test 
    WHERE field_value = num1;
  $$;
  

• 多行匹配时主动抛出明确错误
  如果多行匹配属于异常情况，可以判断行数并返回错误提示：

  CREATE OR REPLACE FUNCTION test (field_value STRING)
  RETURNS STRING
  AS
  $$
    SELECT CASE 
             WHEN COUNT(*) > 1 THEN '错误：匹配到多行数据' 
             ELSE MAX(value_x) 
           END
    FROM mapping_table_test 
    WHERE field_value = num1;
  $$;
  

二、正确使用表函数（返回多行结果） #

表函数的调用方式和标量函数不同，必须用TABLE()包裹才能正常调用：

创建表函数 #

CREATE OR REPLACE FUNCTION test (field_value STRING)
RETURNS TABLE(mapped_value STRING)
AS
$$
  SELECT value_x AS mapped_value
  FROM mapping_table_test 
  WHERE field_value = num1;
$$;

正确调用方式 #

不能直接用SELECT test(10);，需要这样调用：

SELECT * FROM TABLE(test('10')); -- 注意参数为字符串类型，需加引号

如果要和其他表关联使用，示例如下：

SELECT t.*, m.mapped_value
FROM your_target_table t
JOIN TABLE(test(t.your_field)) m ON 1=1;

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