How to pass input from user to a java script function inside a snowflake UDF?

user_13331269 0
Viewed 37

I have to convert some JS functions to snowflake udfs. It works successfully with hardcoded sample data but I can't make it accept user input as arguments. I know the UDF's create statement is supposed to take the input but I cannot make it work.

CREATE OR REPLACE FUNCTION findAndReplace()
  RETURNS variant 
  LANGUAGE JAVASCRIPT
AS
$$
  return execute(map,value); 
  $$
 ;
 
 select findAndReplace();

Thanks for the help.

snowflake-cloud-data-platform javascript
1 Answers

Assuming that arguments to provide are inside map.set("SKU","Common Measurement (L x W)^Manufacturer Color/Finish"); the function signature has to be changed to findAndReplace(ARG1 TEXT, ARG2 TEXT):

CREATE OR REPLACE FUNCTION findAndReplace(ARG1 TEXT, ARG2 TEXT)
  RETURNS variant 
  LANGUAGE JAVASCRIPT
AS
$$
const execute = (productMap, values) => {
  const split = values.split(",");
  const value = productMap.get(split[0]);
  try {
      if (split.length > 2) {
          if (value != null) {
              const from = split[1].replaceAll("\'", "");
              const to = split[2].replaceAll("\'", "");
              return value.replaceAll(from,to);
          }
      }
  }catch{
      console.log("Error in executing FindAndReplace Rule ");
  }
  return "n/a";
}

//Example
  var map = new Map()
  map.set(ARG1, ARG2);   // <-- HERE
  const value = "SKU,'Color/','#'";
  return execute(map,value); 
  $$
 ;

Function all:

select findAndReplace('SKU', 'Common Measurement (L x W)^Manufacturer Color/Finish');
user_50708790
Related