Snowflake Scripting-UDFs¶

CREATE OR REPLACE FUNCTION calculate_profit(
  cost NUMBER(38, 2),
  revenue NUMBER(38, 2))
RETURNS number(38, 2)
LANGUAGE SQL
AS
DECLARE
  profit NUMBER(38, 2) DEFAULT 0.0;
BEGIN
  profit := revenue - cost;
  RETURN profit;
END;

SELECT calculate_profit(100, 110);

CREATE OR REPLACE TABLE snowflake_scripting_udf_profit(
  cost NUMBER(38, 2),
  revenue NUMBER(38, 2));

INSERT INTO snowflake_scripting_udf_profit VALUES
  (100, 200),
  (200, 190),
  (300, 500),
  (400, 401);

SELECT calculate_profit(cost, revenue)
  FROM snowflake_scripting_udf_profit;

CREATE OR REPLACE function check_dept(department_id INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  IF (department_id < 3) THEN
    RETURN 'Engineering';
  ELSEIF (department_id = 3) THEN
    RETURN 'Tool Design';
  ELSE
    RETURN 'Marketing';
  END IF;
END;

SELECT check_dept(2);

SET my_variable = 3;

SELECT check_dept($my_variable);

CREATE OR REPLACE function count_to(
  target_number INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  counter INTEGER DEFAULT 0;
  sum_total INTEGER DEFAULT 0;
BEGIN
  WHILE (counter < target_number) DO
    counter := counter + 1;
    sum_total := sum_total + counter;
  END WHILE;
  RETURN 'Counted to ' || counter || '. Sum of all numbers: ' || sum_total;
END;

SELECT count_to(10);

CREATE OR REPLACE FUNCTION raise_exception(input_value INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
DECLARE
  counter_val INTEGER DEFAULT 0;
  my_exception EXCEPTION (-20002, 'My exception text');
BEGIN
  WHILE (counter_val < 12) DO
    counter_val := counter_val + 1;
    IF (counter_val > 10) THEN
      RAISE my_exception;
    END IF;
  END WHILE;
  RETURN counter_val;
EXCEPTION
  WHEN my_exception THEN
    IF (input_value = 1) THEN
      RETURN 'My exception caught: ' || sqlcode;
    ELSEIF (input_value = 2) THEN
      RETURN 'My exception caught with different path: ' || sqlcode;
    END IF;
    RETURN 'Default exception handling path: ' || sqlcode;
END;

SELECT raise_exception(1);

SELECT raise_exception(2);

SELECT raise_exception(NULL);

CREATE OR REPLACE TABLE test_sql_udf_insert (num NUMBER);

CREATE OR REPLACE FUNCTION value_to_insert(l NUMBER, r NUMBER)
RETURNS number
LANGUAGE SQL
AS
BEGIN
  IF (r < 0) THEN
    RETURN l/r * -1;
  ELSEIF (r > 0) THEN
    RETURN l/r;
  ELSE
    RETURN 0;
END IF;
END;

INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, 2);
INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, -2);
INSERT INTO test_sql_udf_insert SELECT value_to_insert(10, 0);

SELECT * FROM test_sql_udf_insert;

CREATE OR REPLACE TABLE test_sql_udf_clauses (p1 INT, p2 INT);

INSERT INTO test_sql_udf_clauses VALUES
  (100, 7),
  (100, 3),
  (100, 4),
  (NULL, NULL);

CREATE OR REPLACE FUNCTION get_product(a INTEGER, b INTEGER)
RETURNS VARCHAR
LANGUAGE SQL
AS
BEGIN
  RETURN a * b;
END;

SELECT *
  FROM test_sql_udf_clauses
  WHERE get_product(p1, p2) > 350;

SELECT *
  FROM test_sql_udf_clauses
  ORDER BY get_product(p1, p2);