CREATE SCHEMA IF NOT EXISTS udf_demo;

USE udf_demo;

CREATE OR REPLACE FUNCTION bitand(arg1 BIGINT, arg2 BIGINT)

  RETURNS BIGINT

  RETURN arg1 & arg2;

CREATE OR REPLACE FUNCTION bitor(arg1 BIGINT, arg2 BIGINT)

  RETURNS BIGINT

  RETURN arg1 | arg2;

CREATE OR REPLACE FUNCTION bitxor(arg1 BIGINT, arg2 BIGINT)

  RETURNS BIGINT

  RETURN arg1 ^ arg2;

CREATE OR REPLACE FUNCTION base64_encode(arg BINARY)

  RETURNS STRING

  RETURN base64(arg);

CREATE OR REPLACE FUNCTION startwith(arg1 STRING, arg2 STRING)

  RETURNS BOOLEAN

  RETURN position(arg2, arg1) = 1;

CREATE OR REPLACE FUNCTION split_part(str STRING, regex STRING, part INT)

  RETURNS STRING

  COMMENT 'Splits str around occurrences that match the Java regex and return the requested part'

  RETURN element_at(split(str, regex), part);

CREATE OR REPLACE FUNCTION insert(base STRING, pos INT, len INT, ins STRING)

  RETURNS STRING

  RETURN overlay(base, ins, pos, len);

CREATE OR REPLACE FUNCTION len(arg STRING)

  RETURNS INT

  RETURN length(arg);

CREATE OR REPLACE FUNCTION date_from_parts(year INT, month INT, day INT)

  RETURNS DATE

  RETURN make_date(year, month, day);

CREATE OR REPLACE FUNCTION timestamp_from_parts(year INT, month INT, day INT, hour INT, minute INT, second DECIMAL(10, 6))

  RETURNS TIMESTAMP

  RETURN make_timestamp(year, month, day, hour, minute, second);

CREATE OR REPLACE FUNCTION dayname(arg TIMESTAMP)

  RETURNS STRING

  RETURN CASE datediff(CAST(arg AS DATE), DATE'1799-12-29') % 7

         WHEN 0 THEN 'Sun'

         WHEN 1 THEN 'Mon'

         WHEN 2 THEN 'Tue'

         WHEN 3 THEN 'Wed'

         WHEN 4 THEN 'Thu'

         WHEN 5 THEN 'Fri'

         ELSE 'Sat' END;

CREATE OR REPLACE FUNCTION monthname(arg TIMESTAMP)

  RETURNS STRING

  RETURN CASE EXTRACT(MONTH FROM arg)

         WHEN 1 THEN 'Jan'

         WHEN 2 THEN 'Feb'

         WHEN 3 THEN 'Mar'

         WHEN 4 THEN 'Apr'

         WHEN 5 THEN 'May'

         WHEN 6 THEN 'Jun'

         WHEN 7 THEN 'Jul'

         WHEN 8 THEN 'Aug'

         WHEN 9 THEN 'Sep'

         WHEN 10 THEN 'Oct'

         WHEN 11 THEN 'Nov'

         ELSE 'Dec' END;

CREATE OR REPLACE FUNCTION dateadd(unit STRING, measure DECIMAL(10, 6), base TIMESTAMP)

  RETURNS TIMESTAMP

  RETURN CASE lower(unit)

         WHEN 'year'   THEN base + make_interval(measure)

         WHEN 'month'  THEN base + make_interval(0, measure)

         WHEN 'day'    THEN base + make_interval(0, 0, measure)

         WHEN 'hour'   THEN base + make_interval(0, 0, 0, measure)

         WHEN 'minute' THEN base + make_interval(0, 0, 0, 0, measure)

         WHEN 'second' THEN base + make_interval(0, 0, 0, 0, 0, measure)

         END;

CREATE OR REPLACE FUNCTION div0(arg1 DOUBLE, arg2 DOUBLE)

  RETURNS DOUBLE

  RETURN CASE WHEN arg2 = 0 THEN 0 ELSE arg1 / arg2 END;

CREATE OR REPLACE FUNCTION square(arg DOUBLE)

  RETURNS DOUBLE

  RETURN arg * arg;

CREATE OR REPLACE FUNCTION uniform(min DOUBLE, max DOUBLE)

  RETURNS DOUBLE

  RETURN min + (max - min) * random();

CREATE OR REPLACE FUNCTION zeroifnull(arg DOUBLE)

  RETURNS DOUBLE

  RETURN COALESCE(arg, 0);

CREATE OR REPLACE FUNCTION nullifzero(arg DOUBLE)

  RETURNS DOUBLE

  RETURN nullif(arg, 0);

CREATE OR REPLACE FUNCTION equal_null(arg1 DOUBLE, arg2 DOUBLE)

  RETURNS BOOLEAN

  RETURN arg1 <=> arg2;

-- Create a generic validator schema to hold all the functions

CREATE SCHEMA IF NOT EXISTS validator;

-- Map an alpha-numeric digit in the VIN to numbers.  

CREATE OR REPLACE FUNCTION validator.vin_digit                           -- If the function already exists replace it, the signature must match

  (digit string COMMENT 'A single character 0..9, a..Z, A..Z')           -- 0 or more parameters, optional comments 

  RETURNS integer                                                        -- This is a scalar function.

  COMMENT 'Map alphanumeric digit to numeric digit'                      -- An optional decription of the function

  LANGUAGE SQL                                                           -- FYI: LANGUAGE SQL is also the default

  CONTAINS SQL                                                           -- FYI: The body contains a SQL expression, but does not READ SQL DATA

  DETERMINISTIC                                                          -- FYI: The function returns the same result for a given set of arguments. 

  RETURN decode(upper(digit),                                            -- A "simple" expression

                'A',1,'B',2,'C',3,'D',4,'E',5,'F',6,'G',7,'H',8,

                'J',1,'K',2,'L',3,'M',4,'N',5,      'P',7,      'R',9,

                      'S',2,'T',3,'U',4,'V',5,'W',6,'X',7,'Y',8,'Z',9, 

                digit::integer);

-- You can call a SQL function with a qualified name or unqualified if it lives in the curent schema

USE SCHEMA validator;

SELECT vin_digit('X'), validator.vin_digit('6');

-- Use the vin_digit() function above to compute the checksum.

-- The algorithm prescribes a weight to each position in the 17 digit VIN.

-- The weight is multiplied with the result of vin_digit() and then summed up.

CREATE OR REPLACE FUNCTION validator.vin_checksum

  (vin string COMMENT 'A VIN')

  RETURNS integer

  COMMENT 'Computes the VIN checksum'

  DETERMINISTIC

  CONTAINS SQL         

  SQL SECURITY DEFINER                                                       -- FYI: A SQL UDF always executes using the definer's authorization 

  RETURN coalesce(nullif(mod((SELECT sum(vin_digit(substr(vin,pos,1))        -- The function invokes another SQL UDF 

                                         * mult)                             -- The function uses a scalar subquery but does not READ SQL DATA. 

                                FROM VALUES( 1, 8), ( 2,  7), ( 3, 6),

                                           ( 4, 5), ( 5,  4), ( 6, 3),

                                           ( 7, 2), ( 8, 10), (10, 9),

                                           (11, 8), (12,  7), (13, 6),

                                           (14, 5), (15,  4), (16, 3),

                                           (17, 2) AS t(pos, mult)),

                              11), 10)::string, 'X');

USE SCHEMA validator;

SELECT vin_checksum('WA1LKCFP4BA063025');