如何在Snowflake中定义数组变量？解决Snowflake Worksheet中非常量源表达式赋值数组变量的报错问题

Hey there, let's tackle your two Snowflake questions one by one!

In Snowflake, you have a couple of practical ways to define array variables, depending on whether you need a session-wide variable or a temporary one for a specific script:

• Session-level Array Variable: Use the SET command with Snowflake's built-in array_construct() function to assign a constant array directly:

  -- Define a string array as a session variable
  set user_roles = array_construct('admin', 'editor', 'viewer');
  
  -- Use the variable in a query
  select $user_roles;
  

  You can pass any constant values (strings, numbers, booleans, etc.) into array_construct() to build your desired array.

• Local Variable in a Script Block: If you only need the array for a piece of inline logic, define it inside a script block with DECLARE:

  DECLARE
      product_categories ARRAY;
  BEGIN
      -- Assign a constant array to the local variable
      product_categories := array_construct('electronics', 'clothing', 'home');
      -- Use the variable in your logic
      SELECT product_categories;
  END;
  

That error happens because Snowflake's SET command only accepts constant expressions as the assignment source. You can't directly assign the result of a dynamic SELECT query to a session variable using SET—since query results are generated at runtime, not static constants.

Here are two reliable solutions:

Solution 1: Use a Script Block for Local Variable Assignment #

If you just need to use the array within the current script (no session-wide availability), use the INTO clause in a script block to capture the query result:

DECLARE
    columns ARRAY;
BEGIN
    -- Assign the query result to the local array variable
    SELECT array_agg(COLUMN_NAME) INTO columns 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name = 'MEMBERS';
    
    -- Use the variable, e.g., to view the column list
    SELECT columns AS member_table_columns;
END;

Solution 2: Set a Session Variable via Stored Procedure #

If you need the array to be available across your entire session, use a stored procedure to handle the dynamic assignment (stored procedures support runtime logic to modify session variables):

-- Create a stored procedure to populate the session variable
CREATE OR REPLACE PROCEDURE populate_columns_session_var()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    col_array ARRAY;
BEGIN
    -- Fetch the array of column names from the table
    SELECT array_agg(COLUMN_NAME) INTO col_array 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE table_name = 'MEMBERS';
    
    -- Set the array as a session variable
    ALTER SESSION SET columns = :col_array;
    
    RETURN 'Session variable "columns" has been set with MEMBERS table columns';
END;
$$;

-- Execute the stored procedure to set the variable
CALL populate_columns_session_var();

-- Now you can use the session variable anywhere in your session
SELECT $columns;

Why Your Original Statement Failed #

Snowflake's SET command is designed for static, constant assignments because session variables are initialized at session start. Dynamic query results are generated on-the-fly, which doesn't fit the SET command's constraints. Script blocks and stored procedures, however, support runtime logic, making them the right tools for this dynamic assignment scenario.

内容的提问来源于stack exchange，提问作者Roxana xerces