How to execute output of SQL UDF using Javascript stored procedure in Snowflake?

Question

I created a UDF that takes 4 parameters and outputs a DDL statement

select * from table(my_udf('some_text',some_integer,some_integer,'some_text''));

Here is one sample of a DDL statement the SQL UDF outputs as a table with one row

create or replace temporary view some_view as

select * from some_table1 union all
select * from some_table2;

How can I create a Javascript based procedure that basically takes the same parameters as my SQL UDF and executes the DDL statement. I am not so familiar with Javascript, which is why I left all the heavy lifting to SQL so I could keep the stored procedure as simple as possible in case I need to replicate it for a different use case.

This is my attempt so far based on the examples I came across. There is probably a lot wrong my code, but if someone could point me in the right direction, that'd be huge

create or replace temporary procedure jvs_udf(PAR_1 varchar, PAR_2 number, PAR_3 number, ARG_4 varchar)
returns string not null
language Javascript
as

$$
sql_command = 'select * from table(my_udf(' + PAR_1 + ',' + PAR_2 + ',' + PAR_3 + ',' + PAR_4 + '))' ;
var stmt = snowflake.createStatement({sqlText: sql_command}).execute();
return --how do I also get it to return the message I would have gotten had I run DDL in UI?
$$;

Lukasz Szozda · Accepted Answer · 2021-06-25 07:33:16Z

1

You could reuse your table UDF and pass parameters using binds:

create or replace temporary procedure jvs_udf(PAR_1 varchar, PAR_2 varchar, 
                                              PAR_3 varchar, ARG_4 varchar)
returns string not null
language Javascript
as
$$
sql_command = 'select * from table(my_udf(:1, :2, :3, :4))';
var stmt = snowflake.createStatement({sqlText: sql_command
                                     ,binds: [PAR1, PAR2, PAR3, ARG4]}).execute();

stmt.next();
var ddl_statement = stmt.getColumnValue(1);

snowflake.createStatement({sqlText: ddl_statement}).execute();

return 'View created';
$$;

edited Jun 25, 2021 at 7:33

answered Jun 24, 2021 at 15:47

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Rajat Over a year ago

The thing is create or replace view view_name is included in the output of the SQL UDF. View name changes per the parameters. I am also getting Language JAVASCRIPT does not support type 'NUMBER(38,0)' for argument or return type error

Lukasz Szozda Over a year ago

@Rajat Then you need to get the output of first call and execute it. Please see my update

Rajat Over a year ago

Any way around that number data type error?

Lukasz Szozda Over a year ago

@Rajat You could use FLOAT instead of NUMBER.

Rajat Over a year ago

Figured it out. Had to do with data types. Set everything to varchar and handled data conversion in the SQL UDF. Working as expected now

|

Rajat · Accepted Answer · 2021-08-08 03:19:34Z

0

It later occurred to me that I could have simply passed my select statement as parameter to the stored proc.

create or replace procedure jvs_udf(SQL_COMMAND varchar)
returns string not null
language Javascript
execute as caller
as
$$
var stmt = snowflake.createStatement({sqlText: SQL_COMMAND}).execute();
stmt.next();
var ddl = stmt.getColumnValue(1);
return ddl;
$$;

Usage would then look like

call jvs_udf(select * from table(my_udf('some_text',some_integer,some_integer,'some_text')));

edited Aug 8, 2021 at 3:19

answered Jul 6, 2021 at 19:48

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

How to execute output of SQL UDF using Javascript stored procedure in Snowflake?

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related