How to define an array variable in Snowflake

Question

How to define an array variable in snowflake worksheet?

set columns =  (SELECT array_agg(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS 
                where table_name='MEMBERS');

I get this error:

Unsupported feature 'assignment from non-constant source expression'.

Answer 1

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.