create procedure my_stored_procedure(TABLE VARCHAR)
  returns varchar
  language javascript
  as
    $$

var sql_col_list = `select distinct key_name from TABLE;`
results = snowflake.execute({sqlText: sql_col_list});

while (results.next())
{
     script  = ','   results.getColumnValue(1)   '\n';
}

Current output:

, column1
, column2
, column3

Desired output:

  column1
, column2
, column2

How can I do this in javascript within the sql stored procedure? I would like to not have to execute the query more than once because the actual query is computationally intensive.

CodePudding user response：

you can add boolean check if this first or not:

var sql_col_list = `select distinct key_name from TABLE;`
results = snowflake.execute({sqlText: sql_col_list});

var isFirst = true;

while (results.next())
{
      if(isFirst)
      {
        script  = results.getColumnValue(1)   '\n';
        isFirst = false;
        continue;
      }
     script  = ','   results.getColumnValue(1)   '\n';
}

CodePudding user response：

A poor solve it after the fact solution, remove it:

> ',a,b,c'.replace(/^,/,'');
'a,b,c'
>

another option is to build an array of the wanted parts, and then use join

> ['a','b','c'].join(',\n');
'a,\nb,\nc'
>

and if you want the trailing newline:

> ['a','b','c'].join(',\n') '\n';
'a,\nb,\nc\n'
>

OR you could get Snowflake to do it for you:

SELECT listagg( DISTINCT key_name, ',\\n') || '\\n'
FROM VALUES
   ('apple'),('cat'),('apple'),('cat'),('dog') t(key_name);