Are Snowflake's SQL UDFs limited just to SQL expressions, without the option of more advanced s-CodePudding

I tried to create a slightly more complex SQL UDF that initialises a counter variable used in a while loop and then returns it.

CREATE OR REPLACE FUNCTION test_func() RETURNS NUMBER 
AS
$$
begin
  let counter := 0;
  while (counter < 5) do
    counter := counter   1;
  end while;
  return counter;
end;
$$;

I kept it simple with an example loop from documentation. But I can't get it to compile despite trying out a few different variations, such as putting counter in declare:

declare 
  counter NUMBER;

I tried a JavaScript version and that compiles and runs successfully.

CREATE OR REPLACE FUNCTION test_func_js() 
  RETURNS VARCHAR 
  LANGUAGE JAVASCRIPT 
  AS $$
  let counter = 0;
  while (counter < 5) {
    counter  = 1;  
  }
  return counter;
  $$;

I think an answer to this already lies in documentation, but I just want to check if I'm missing something or someone has more experience with SQL UDFs in Snowflake.

Am I really just limited to SQL expressions? There is no support for Snowflake scripting inside UDFs in Snowflake?

CodePudding user response：

No, this is not possible. You need to go for JavaScript or for example Python.

CodePudding user response：

Yes, SQL UDFs in Snowflake can only be used with expression. You can check this official documentation link, stating the same clearly. https://docs.snowflake.com/en/developer-guide/udf/sql/udf-sql-introduction.html#general-usage