sql - 在 Snowflake 中创建存储过程时，有没有办法返回与在其中执行的查询相同的输出？

Question

我创建了一个存储过程来设置特定的变量值并使用该变量执行合并。目前，它只返回一条硬编码消息“成功执行”。是否有可能返回与原始合并查询返回相同的结果，例如

number of rows inserted

number of rows updated

?

    CREATE OR REPLACE PROCEDURE ALERTS_MERGE_PROCEDURE ()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
    $$
    var sql_command = '
    MERGE INTO tablename
..
WHEN MATCHED THEN
UPDATE SET ...
WHEN NOT MATCHED THEN
INSERT ...
);
    '
    snowflake.execute(
        {
        sqlText: sql_command
        });
    return "Successfully executed.";
    $$;

Answer 1

您可以迭代执行返回对象的第一行的列:

create or replace temp table tablename as
select 1::int  id, 'a'::string tx;

create or replace temp table tablesource as
select 1::int  id, 'b'::string tx 
union select 2, 'c';

CREATE OR REPLACE PROCEDURE ALERTS_MERGE_PROCEDURE ()
RETURNS STRING NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
    var sql_command = `
    merge into tablename a
    using tablesource b
    on a.id = b.id 
    when matched then update set tx=b.tx
    when not matched then insert (id, tx) values (b.id, b.tx);
    `;
    var x = snowflake.execute({sqlText: sql_command});
    x.next();
    var result = '';
    for (i=1; i<=x.getColumnCount(); i++) {
        result += x.getColumnName(i) + ': ' + x.getColumnValue(i) + '\n'; 
    }
    return result;
$$;
    
call alerts_merge_procedure();

返回:

number of rows inserted: 1
number of rows updated: 1