CODEWITHSUNDEEP
sqlscriptingsnowflake-cloud-data-platform
Felipe Hoffa
Felipe Hoffa

Reputation: 59315

SQL Snowflake Scripting - How can I return a table created in memory while looping?

While looping over results with a cursor or so within a SQL Snowflake Scripting, how can I return a table created in memory?

I could create a temp table and insert into to return the results at the end, but that's too slow.

(from a now deleted comment on Snowflake Scripting in SQL - how to iterate over the results of a SHOW command?)

Upvotes: 4

Views: 2606

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59315

A solution to create a table in memory is to create an array and then return the result of select * from(flatten(array):

declare
  tmp_array ARRAY default ARRAY_CONSTRUCT();
  rs_output RESULTSET;
begin
    for i in 1 to 20 do
        tmp_array := array_append(:tmp_array, OBJECT_CONSTRUCT('c1', 'a', 'c2', i));
    end for;
    rs_output := (select value:c1, value:c2 from table(flatten(:tmp_array)));
    return table(rs_output);
end;

enter image description here

On my initial tests the performance is a little worse than linear, but much better than using a temp table.

enter image description here

(h/t Darren Gardner)

Upvotes: 8

Related Questions