如何在Snowflake响应转换器中调用存储过程获取表行数？

现有一个Snowflake响应转换器函数，其中循环上限为硬编码的6，需要替换为调用该函数的外部关联表的行数：

CREATE OR REPLACE FUNCTION response_translator(EVENT OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT AS
'
var responses =[];
if (EVENT.body.error!=null){
for(i=0; i<6;i++){
if (i==0){
let result=[i, EVENT.body]
responses[i] = result
}
else{
let result = [i,null]
responses[i] = result
}
}
return { "body": { "data" :responses } };
}
else{
return { "body": EVENT.body };
}
';

已编写存储过程get_row_count用于获取表行数：

create or replace procedure get_row_count(table_name VARCHAR)
  returns float not null
  language javascript
  as
  $$
  var row_count = 0;
  var sql_command = "select count(*) from " + TABLE_NAME;
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command
         }
      );
  var res = stmt.execute();
  res.next();
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;

需要实现：在响应转换器函数中调用该存储过程，获取返回的行数替换循环中的6。

Snowflake的UDF无法直接调用存储过程，但可以通过执行CALL语句后，用RESULT_SCAN获取存储过程的返回结果。具体修改步骤如下：

1. 确保表名可传入：需将目标表名通过EVENT对象传入（比如EVENT.table_name），或者调整函数参数新增表名字段。
2. 修改响应转换器函数：添加调用存储过程并获取行数的逻辑，替换硬编码的6。

修改后的函数代码：

CREATE OR REPLACE FUNCTION response_translator(EVENT OBJECT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT AS
'
var responses = [];
if (EVENT.body.error != null) {
    // 从EVENT中获取目标表名，调用时需确保传入该字段
    var tableName = EVENT.table_name;
    // 调用存储过程
    var callStmt = snowflake.createStatement({
        sqlText: "CALL get_row_count(?);",
        binds: [tableName]
    });
    callStmt.execute();
    
    // 通过RESULT_SCAN获取存储过程的返回值
    var scanStmt = snowflake.createStatement({
        sqlText: "SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));"
    });
    var scanRes = scanStmt.execute();
    scanRes.next();
    var rowCount = scanRes.getColumnValue(1);
    
    // 使用获取到的行数作为循环上限
    for (var i = 0; i < rowCount; i++) {
        let result = i === 0 ? [i, EVENT.body] : [i, null];
        responses.push(result);
    }
    
    return { "body": { "data": responses } };
} else {
    return { "body": EVENT.body };
}
';

关键说明 #

• 使用参数绑定（binds: [tableName]）避免SQL注入风险，替代直接字符串拼接。
• LAST_QUERY_ID()获取刚执行的CALL语句ID，通过RESULT_SCAN读取存储过程的返回结果。
• 简化循环逻辑为三元表达式，同时用push方法向数组添加元素，代码更简洁。

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