Javascript Array in snowflake procedure-CodePudding

I have a snowflake procedure that creates a table using the parameters passed to a snowflake procedure as below:

CREATE OR REPLACE PROCEDURE SP_test (c_names varchar)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS $$
  
  var results_array = [];
  var c_names_combined = "'"   C_NAMES.split(',').join("','")   "'"
  
  sql_command = `SELECT TABLE_CATALOG||'.'||TABLE_SCHEMA||'.'||TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='MAP_MEMBER_PLATFORM'`
  sql_command = sql_command   ` AND TABLE_SCHEMA IN (`   c_names_combined  `)`
  sqlStmt = snowflake.createStatement( {sqlText: sql_command} );
  result_sqlStmt  = sqlStmt.execute();
  
  while (result_sqlStmt.next()) {
    var full_table_names = result_sqlStmt.getColumnValue(1);
    results_array.push([full_table_names]);
  }
  var c_1 = results_array[0];
  var c_2 = results_array[1];
  var c_3 = results_array[2];

  
  sql_command = `CREATE OR REPLACE TABLE table_to_create
                          AS
                          SELECT * FROM `   c_1  `
                           UNION ALL
                          SELECT * FROM `   c_2  `
                           UNION ALL
                          SELECT * FROM `   c_3  ``
  sqlStmt = snowflake.createStatement( {sqlText: sql_command} );
  result_sqlStmt  = sqlStmt.execute();
  
  result = "done"
  return result
  
$$
; 
call SP_test('A,B,C');

The code is working fine if i pass three parameters A, B and C. But i want it to work for as many parameters i pass.

For example if i call the procedure with parameters ('A, B, C, D, E'). I want my table(table_to_create) create a table using the parameters i have passed.(in this case: A, B, C, D, E)

Please help !

CodePudding user response：

Something like this should work, might not be ideal, but should work:

var results_array = [1, 2, 3, 4, 5];
var sql_command="CREATE OR REPLACE TABLE table_to_create AS ";
for (let i = 0; i < results_array.length; i  ) {
   sql_command  = "SELECT * FROM "   results_array[i]   " UNION ALL ";
 }

var last_words = sql_command.split(" ")
last_words.pop();
last_words.pop();
last_words.pop();
var sql_command_new = last_words.join(" ");

Running this on node console I get:

> console.log(sql_command_new);
CREATE OR REPLACE TABLE table_to_create AS SELECT * FROM 1 UNION ALL SELECT * FROM 2 UNION ALL SELECT * FROM 3 UNION ALL SELECT * FROM 4 UNION ALL SELECT * FROM 5

The 3 pop's are needed to remove last space, and last UNION ALL. In this way you can add as many args as you need.