Not able to use query results inside Javascript UDF in Snowflake

Question

Thankyou for your time.

I am working on a problem in Snowflake database. I am basically trying to create a Javascript UDF like following -

CREATE OR REPLACE FUNCTION getACity()   
RETURNS STRING   
LANGUAGE JAVASCRIPT   
AS 
'function getCity() {
var str;
$$  str = select * from CITY where name="Laflin" //This is the problem 
$$
return str;   
}';

As you can see I am trying to use data from another table in my function, but its not happening and I get following error -

JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in getACity at ' str = select * from CITY where name="Laflin"' position 21

I understand the syntax I am using to execute a query inside a javascript function is not correct, but I am not even sure if it is possible in Snowflake.

I have following questions -

Can I execute a query inside a javascript UDF and use the results in ongoing function ?
Can I call a Javascript UDF inside another Javascript UDF ? If this is possible it will work for me as well.

I have gone through Snowflake documentation but could not find any help specific to this case.

Thankyou again for your time. Much appreciated !

Marcin Zukowski Marcin Zukowski · Accepted Answer · 2018-10-11T18:20:24

To answer your questions:

Snowflake JavaScript UDFs do not allow executing queries inside them in any form today.
you can not use JS UDFs in ther JS UDFs

You CAN use SQL UDFs for that, but that can only produce one column, e.g.

create or replace function getcityid() 
returns string as '
  select id from CITY where name='Laflin'
';

Or use VIEWs, e.g.

create or replace view cities as select * from CITY where name='Laflin'

If you explain exactly what you try to achieve, this should help.

Also, note that "Laflin" in SQL refers to a column named Laflin, not to a string with a value Laflin. Use 'Laflin' for that.

Not able to use query results inside Javascript UDF in Snowflake

2 Answers