How to define an array variable in Snowflake

2022 update:

Now it's possible with Snowflake Scripting:

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;

• https://stackoverflow.com/a/71231621/132438

Previously:

Instead of storing an array, aggregate in a comma separated string:

set x = (SELECT listagg(COLUMN_NAME, ',') FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'TABLE_S%');

However: "The size of string or binary variables is limited to 256 bytes" according to https://docs.snowflake.com/en/sql-reference/session-variables.html.

Which means that even if you could store an array in a variable, it would probably exceed the limits. Instead store the result in [temp] tables or so.

I found if you encapsulate with to_json it will work:

set x =  (SELECT to_json(array_agg(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS 
            where table_name='CUSTOMER');
select $x;