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.
Advertisement
Answer
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');