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.
using your sp, i would remove the first result from the loop so it is:
create or replace 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});
var script = '';
results.next()
script += results.getColumnValue(1)
while (results.next())
{
script += ',' + results.getColumnValue(1) + '\n';
}
return script;
$$;
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);
| LISTAGG( DISTINCT KEY_NAME, ',\N')|| '\N' |
|---|
| apple,\ncat,\ndog\n |
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';
}