优化Snowflake响应转换器：通过UDF动态获取表行数

-- 原始获取行数的存储过程示例
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;
  $$
  ;

-- 原始响应转换器示例，其中迭代次数是硬编码的
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++){ -- 这里的 '6' 需要动态替换
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 };
}
';

CREATE OR REPLACE FUNCTION get_table_row_count_udf(table_name VARCHAR)
  RETURNS FLOAT NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var row_count = 0;
  // 注意：在实际生产环境中，拼接SQL语句可能存在SQL注入风险，
  // 建议对输入参数进行严格校验或使用更安全的参数化方式。
  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); // 获取第一列的值（即COUNT(*)）
  return row_count;
  $$
  ;

CREATE OR REPLACE FUNCTION response_translator_dynamic(EVENT OBJECT, total_rows FLOAT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT AS
'
var responses =[];
if (EVENT.body.error != null){
for(let i = 0; i < total_rows; i++){ // 使用传入的 total_rows 进行迭代
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 };
}
';

-- 示例：假设 'my_table' 是需要获取行数的表名
-- 假设 'my_event_object' 是实际的事件对象
-- 注意：在实际使用中，EVENT OBJECT通常由外部函数自动传递
CALL response_translator_dynamic(
    '{"body": {"error": null, "data": "some_data"}}'::OBJECT, -- 示例 EVENT 对象
    get_table_row_count_udf('my_table')
);

-- 另一个示例，如果 EVENT.body.error 不为空
CALL response_translator_dynamic(
    '{"body": {"error": "An error occurred", "details": "Error details"}}'::OBJECT, -- 示例 EVENT 对象
    get_table_row_count_udf('my_table')
);

-- 1. 创建获取表行数的UDF
CREATE OR REPLACE FUNCTION get_table_row_count_udf(table_name VARCHAR)
  RETURNS FLOAT NOT NULL
  LANGUAGE JAVASCRIPT
  AS
  $$
  var row_count = 0;
  // 建议：在生产环境中，对 table_name 进行验证或使用更安全的SQL生成方式
  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;
  $$
  ;

-- 2. 创建支持动态行数迭代的响应转换器
CREATE OR REPLACE FUNCTION response_translator_dynamic(EVENT OBJECT, total_rows FLOAT)
RETURNS OBJECT
LANGUAGE JAVASCRIPT AS
'
var responses = [];
if (EVENT.body.error != null){
    // 如果存在错误，根据 total_rows 填充响应数组
    for(let i = 0; i < total_rows; i++){
        if (i == 0){
            let result = [i, EVENT.body]; // 第一个元素包含原始错误信息
            responses[i] = result;
        }
        else{
            let result = [i, null]; // 其他元素为 null
            responses[i] = result;
        }
    }
    return { "body": { "data" : responses } };
}
else{
    // 如果没有错误，直接返回原始事件体
    return { "body": EVENT.body };
}
';

-- 3. 示例调用
-- 假设存在一个名为 'my_table' 的表，并且其中有数据
-- 可以先创建一个测试表并插入数据：
-- CREATE OR REPLACE TABLE my_table (id INT, name VARCHAR);
-- INSERT INTO my_table VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');

-- 调用示例1：模拟一个无错误的事件
SELECT response_translator_dynamic(
    '{"body": {"error": null, "message": "Success"}}'::OBJECT,
    get_table_row_count_udf('my_table')
);

-- 调用示例2：模拟一个有错误的事件
SELECT response_translator_dynamic(
    '{"body": {"error": "true", "details": "Some processing error occurred"}}'::OBJECT,
    get_table_row_count_udf('my_table')
);

-- 验证 get_table_row_count_udf 的输出
SELECT get_table_row_count_udf('my_table');