GoogleCloudPlatform · danieldeleo · May 18, 2023 · May 17, 2023 · May 18, 2023 · May 18, 2023
diff --git a/udfs/community/csv_to_struct.sqlx b/udfs/community/csv_to_struct.sqlx
@@ -20,6 +20,9 @@ config { hasOutput: true }
 -- strList: string that has map in the format a:b,c:d....
 -- Output: struct for the above map.
 CREATE OR REPLACE FUNCTION ${self()}(strList STRING)
+OPTIONS (
+  description="Prepare struct for the csv with string that has map in the format a:b,c:d,..."
+)
 AS (
   ${ref("string_to_struct")}(strList, ',', ':')
 );
diff --git a/udfs/community/cw_array_compact.sqlx b/udfs/community/cw_array_compact.sqlx
@@ -16,6 +16,10 @@ config { hasOutput: true }
  */
 
 /* Similar to Snowflake ARRAY_COMPACT */
-CREATE OR REPLACE FUNCTION ${self()}(a ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(a ANY TYPE) 
+OPTIONS (
+  description="Similar to Snowflake ARRAY_COMPACT"
+)
+AS (
     ARRAY(SELECT v FROM UNNEST(a) v WHERE v IS NOT NULL)
 );
diff --git a/udfs/community/cw_array_distinct.sqlx b/udfs/community/cw_array_distinct.sqlx
@@ -16,6 +16,10 @@ config { hasOutput: true }
  */
 
 /* Similar to presto ARRAY_DISTINCT */
-CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) 
+OPTIONS (
+  description="Similar to presto ARRAY_DISTINCT"
+)
+AS (
    ARRAY( SELECT DISTINCT x FROM UNNEST(arr) AS x )
 );
diff --git a/udfs/community/cw_array_max.sqlx b/udfs/community/cw_array_max.sqlx
@@ -16,6 +16,10 @@ config { hasOutput: true }
  */
 
 /* Similar to presto ARRAY_MAX */
-CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) 
+OPTIONS (
+  description="Similar to presto ARRAY_MAX"
+)
+AS (
    ( SELECT MAX(x) FROM UNNEST(arr) AS x )
 );
diff --git a/udfs/community/cw_array_median.sqlx b/udfs/community/cw_array_median.sqlx
@@ -16,6 +16,10 @@ config { hasOutput: true }
  */
 
 /* Similar to MEDIAN in Teradata */
-CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) 
+OPTIONS (
+  description="Similar to MEDIAN in Teradata"
+)
+AS (
    ( SELECT PERCENTILE_CONT(x, 0.5) OVER() FROM UNNEST(arr) AS x LIMIT 1 )
 );
diff --git a/udfs/community/cw_array_min.sqlx b/udfs/community/cw_array_min.sqlx
@@ -16,6 +16,10 @@ config { hasOutput: true }
  */
 
 /* Similar to presto ARRAY_MIN */
-CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(arr ANY TYPE) 
+OPTIONS (
+  description="Similar to presto ARRAY_MIN"
+)
+AS (
    ( SELECT MIN(x) FROM UNNEST(arr) AS x )
 );
diff --git a/udfs/community/cw_array_overlap.sqlx b/udfs/community/cw_array_overlap.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /* Similar to Presto ARRAYS_OVERLAP */
-CREATE OR REPLACE FUNCTION ${self()}(x ANY TYPE, y ANY TYPE) RETURNS BOOL AS(
+CREATE OR REPLACE FUNCTION ${self()}(x ANY TYPE, y ANY TYPE) RETURNS BOOL 
+OPTIONS (
+  description="Similar to Presto ARRAYS_OVERLAP"
+)
+AS(
     CASE WHEN EXISTS(SELECT 1 FROM UNNEST(ARRAY_CONCAT(x,y)) as z WHERE z IS NULL) THEN NULL
          ELSE EXISTS(SELECT 1 FROM UNNEST(x) as u JOIN UNNEST(y) as v ON u=v)
     END

diff --git a/udfs/community/cw_comparable_format_bigint_t.sqlx b/udfs/community/cw_comparable_format_bigint_t.sqlx
@@ -17,6 +17,9 @@ config { hasOutput: true }
 
 /* Lexicographically '+' comes before '-' so we replace p(lus) and m(inus) and subtract LONG_MIN on negative values */
 CREATE OR REPLACE FUNCTION ${self()}(part INT64) RETURNS STRING
+OPTIONS (
+  description="Lexicographically '+' comes before '-' so we replace p(lus) and m(inus) and subtract LONG_MIN on negative values"
+)
 AS (
     FORMAT(
       CASE WHEN part < 0 THEN 'm' ELSE 'p' END || '%19d', -- 'm' < 'p' lexicographically

diff --git a/udfs/community/cw_comparable_format_varchar_t.sqlx b/udfs/community/cw_comparable_format_varchar_t.sqlx
@@ -17,6 +17,9 @@ config { hasOutput: true }
 
 /* Use hex to work around the separator problem (e.g. if separator = '-' then ['-', ''] and ['', '-'] both produce '--') */
 CREATE OR REPLACE FUNCTION ${self()}(part STRING) RETURNS STRING
+OPTIONS (
+  description="Use hex to work around the separator problem (e.g. if separator = '-' then ['-', ''] and ['', '-'] both produce '--')"
+)
 AS (
     TO_HEX(CAST(part as BYTES))
 );
diff --git a/udfs/community/cw_editdistance.sqlx b/udfs/community/cw_editdistance.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Similar to teradata's editdistance without weightages */
 CREATE OR REPLACE FUNCTION ${self()}( a STRING, b STRING ) RETURNS INT64
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Similar to teradata's editdistance without weightages"
+)
+AS """
 
     if ( a == null || b == null ) {
         return null;

diff --git a/udfs/community/cw_find_in_list.sqlx b/udfs/community/cw_find_in_list.sqlx
@@ -17,6 +17,9 @@ config { hasOutput: true }
 
 /* Similar to Hive "find_in_set" */
 CREATE OR REPLACE FUNCTION ${self()}(needle STRING, list STRING) RETURNS INT64
+OPTIONS (
+    description="Similar to Hive find_in_set"
+)
 AS (
   CASE WHEN needle IS NULL OR list IS NULL THEN NULL
   ELSE COALESCE((SELECT o + 1 FROM unnest(split(list, ',')) straw WITH OFFSET o WHERE straw = needle LIMIT 1), 0) END

diff --git a/udfs/community/cw_from_base.sqlx b/udfs/community/cw_from_base.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /* Similar to Presto from_base function - convert string from given base to decimal */
-CREATE OR REPLACE FUNCTION ${self()}(number STRING, base INT64) RETURNS INT64 AS (
+CREATE OR REPLACE FUNCTION ${self()}(number STRING, base INT64) RETURNS INT64 
+OPTIONS (
+    description="Similar to Presto from_base function - convert string from given base to decimal"
+)
+AS (
     (WITH chars AS (
         SELECT IF(ch >= 48 AND ch <= 57, ch - 48, IF(ch >= 65 AND ch <= 90, ch - 65 + 10, ch - 97 + 10)) pos, offset + 1 AS idx
         FROM UNNEST(TO_CODE_POINTS(number)) AS ch WITH OFFSET

diff --git a/udfs/community/cw_json_array_contains_bool.sqlx b/udfs/community/cw_json_array_contains_bool.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Same as cw_json_array_contains_str(STRING, STRING) UDF but with needle = boolean */
 CREATE OR REPLACE FUNCTION ${self()}(json STRING, needle BOOL) RETURNS BOOL
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Same as cw_json_array_contains_str(STRING, STRING) UDF but with needle = boolean"
+)
+AS """
   if (json == null || needle == null)
     return null;
   var parsedJson = JSON.parse(json);

diff --git a/udfs/community/cw_json_array_contains_num.sqlx b/udfs/community/cw_json_array_contains_num.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Same as cw_json_array_contains_str(STRING, STRING) UDF but with needle = number */
 CREATE OR REPLACE FUNCTION ${self()}(json STRING, needle FLOAT64) RETURNS BOOL
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Same as cw_json_array_contains_str(STRING, STRING) UDF but with needle = number"
+)
+AS """
   if (json == null || needle == null)
     return null;
   var parsedJson = JSON.parse(json);

diff --git a/udfs/community/cw_json_array_contains_str.sqlx b/udfs/community/cw_json_array_contains_str.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Determine if value exists in json (a string containing a JSON array). */
 CREATE OR REPLACE FUNCTION ${self()}(json STRING, needle STRING) RETURNS BOOL
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Determine if value exists in json (a string containing a JSON array)."
+)
+AS """
   if (json == null || needle == null)
     return null;
   var parsedJson = JSON.parse(json);

diff --git a/udfs/community/cw_json_array_get.sqlx b/udfs/community/cw_json_array_get.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Returns the element at the specified index into the json_array. The index is zero-based */
 CREATE OR REPLACE FUNCTION ${self()}(json STRING, loc FLOAT64) RETURNS STRING
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Returns the element at the specified index into the json_array. The index is zero-based"
+)
+AS """
   if (json == null || loc == null)
     return null;
   var parsedJson = JSON.parse(json);

diff --git a/udfs/community/cw_json_array_length.sqlx b/udfs/community/cw_json_array_length.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Returns the array length of json (a string containing a JSON array) */
 CREATE OR REPLACE FUNCTION ${self()}(json STRING) RETURNS INT64
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Returns the array length of json (a string containing a JSON array)"
+)
+AS """
   if (json == null)
     return null;
   var parsedJson = JSON.parse(json);

diff --git a/udfs/community/cw_lower_case_ascii_only.sqlx b/udfs/community/cw_lower_case_ascii_only.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /** Similar to Vertica LOWERB function, which lowercases only ASCII characters within a given string. */
-CREATE OR REPLACE FUNCTION ${self()}(str STRING) RETURNS STRING AS (
+CREATE OR REPLACE FUNCTION ${self()}(str STRING) RETURNS STRING 
+OPTIONS (
+    description="Similar to Vertica LOWERB function, which lowercases only ASCII characters within a given string."
+)
+AS (
     (WITH chars AS (
         SELECT ch FROM UNNEST(TO_CODE_POINTS(str)) AS ch
     )

diff --git a/udfs/community/cw_map_create.sqlx b/udfs/community/cw_map_create.sqlx
@@ -20,7 +20,11 @@ config { hasOutput: true }
   matched <key,value> from each array. Number of elements in each array should
   be equal otherwise remaining values will be ignored.
 */
-CREATE OR REPLACE FUNCTION ${self()}(keys ANY TYPE, vals ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(keys ANY TYPE, vals ANY TYPE) 
+OPTIONS (
+    description="Given an array of keys and values, creates an array of struct containing matched <key,value> from each array. Number of elements in each array should be equal otherwise remaining values will be ignored."
+)
+AS (
   (SELECT
     ARRAY(
       SELECT AS STRUCT keys[SAFE_OFFSET(index)] AS key, vals[SAFE_OFFSET(index)] AS value

diff --git a/udfs/community/cw_map_get.sqlx b/udfs/community/cw_map_get.sqlx
@@ -19,6 +19,10 @@ config { hasOutput: true }
   Given an array of struct and needle, searches an array to find struct whose
   key-field matches needle, then it returns the value-field in the given struct.
 */
-CREATE OR REPLACE FUNCTION ${self()}(maparray ANY TYPE, inkey ANY TYPE) AS (
+CREATE OR REPLACE FUNCTION ${self()}(maparray ANY TYPE, inkey ANY TYPE) 
+OPTIONS (
+    description="Given an array of struct and needle, searches an array to find struct whose key-field matches needle, then it returns the value-field in the given struct."
+)
+AS (
   (SELECT ARRAY_AGG(kv.value)[SAFE_OFFSET(0)] FROM UNNEST(maparray) AS kv WHERE kv.key = inkey)
 );
diff --git a/udfs/community/cw_map_parse.sqlx b/udfs/community/cw_map_parse.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Similar to hive "str_to_map" */
 CREATE OR REPLACE FUNCTION ${self()}(m string, pd string, kvd string)
-   RETURNS ARRAY<STRUCT<key STRING, value STRING>> AS (
+   RETURNS ARRAY<STRUCT<key STRING, value STRING>> 
+   OPTIONS (
+    description="Similar to hive str_to_map"
+   )
+   AS (
   ARRAY(SELECT AS STRUCT kv[SAFE_OFFSET(0)] AS key, kv[SAFE_OFFSET(1)] AS value
       FROM (SELECT SPLIT(kv, kvd) AS kv FROM UNNEST(SPLIT(m, pd)) AS kv) r
 ));
diff --git a/udfs/community/cw_next_day.sqlx b/udfs/community/cw_next_day.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /* Returns the date of the first weekday (second arugment) that is later than the date specified by the first argument */
-CREATE OR REPLACE FUNCTION ${self()}(date_value DATE, day_name STRING) RETURNS DATE AS (
+CREATE OR REPLACE FUNCTION ${self()}(date_value DATE, day_name STRING) RETURNS DATE 
+OPTIONS (
+    description="Returns the date of the first weekday (second arugment) that is later than the date specified by the first argument"
+)
+AS (
     (WITH t AS (SELECT
        CASE lower(substr(day_name, 1, 2))
        WHEN 'su' THEN 1

diff --git a/udfs/community/cw_regexp_extract.sqlx b/udfs/community/cw_regexp_extract.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Returns the first substring matched by the regular expression `regexp` in `str`. */
 CREATE OR REPLACE FUNCTION ${self()}(str STRING, regexp STRING) RETURNS STRING
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Returns the first substring matched by the regular expression `regexp` in `str`."
+)
+AS """
   var r = new RegExp(regexp);
   var a = str.match(r);
   return a[0];

diff --git a/udfs/community/cw_regexp_extract_all.sqlx b/udfs/community/cw_regexp_extract_all.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Returns the substring(s) matched by the regular expression `regexp` in `str`. */
 CREATE OR REPLACE FUNCTION ${self()}(str STRING, regexp STRING) RETURNS ARRAY<STRING>
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Returns the substring(s) matched by the regular expression `regexp` in `str`."
+)
+AS """
   var r = new RegExp(regexp, "g");
   return str.match(r);
 """;
diff --git a/udfs/community/cw_regexp_extract_all_n.sqlx b/udfs/community/cw_regexp_extract_all_n.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Finds all occurrences of the regular expression `regexp` in `str` and returns the capturing group number `groupn`. */
 CREATE OR REPLACE FUNCTION ${self()}(str STRING, regexp STRING, groupn INT64) RETURNS ARRAY<STRING>
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Finds all occurrences of the regular expression `regexp` in `str` and returns the capturing group number `groupn`."
+)
+AS """
   var r = new RegExp(regexp, 'g');
   var o = [];
   while ((a = r.exec(str)) !== null) {

diff --git a/udfs/community/cw_regexp_extract_n.sqlx b/udfs/community/cw_regexp_extract_n.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Finds the first occurrence of the regular expression `regexp` in `str` and returns the capturing group number `groupn` */
 CREATE OR REPLACE FUNCTION ${self()}(str STRING, regexp STRING, groupn INT64) RETURNS STRING
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Finds the first occurrence of the regular expression `regexp` in `str` and returns the capturing group number `groupn`"
+)
+AS """
   var r = new RegExp(regexp);
   var a = str.match(r);
   if (!a) return null;

diff --git a/udfs/community/cw_runtime_parse_interval_seconds.sqlx b/udfs/community/cw_runtime_parse_interval_seconds.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /* Kludge for interval translation - for now day->sec only! */
-CREATE OR REPLACE FUNCTION ${self()}(ival STRING) RETURNS INT64 AS (
+CREATE OR REPLACE FUNCTION ${self()}(ival STRING) RETURNS INT64 
+OPTIONS (
+    description="Kludge for interval translation - for now day->sec only!"
+)
+AS (
     CASE WHEN ival IS NULL THEN NULL
          WHEN ARRAY_LENGTH(SPLIT(ival,' ')) <> 2 THEN NULL
          WHEN SPLIT(ival,' ')[OFFSET(1)] NOT IN ('day','DAY') THEN NULL

diff --git a/udfs/community/cw_stringify_interval.sqlx b/udfs/community/cw_stringify_interval.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
  /* Formats the interval as 'day hour:minute:second */
-CREATE OR REPLACE FUNCTION ${self()}(x INT64) RETURNS STRING AS
+CREATE OR REPLACE FUNCTION ${self()}(x INT64) RETURNS STRING 
+OPTIONS (
+    description="Formats the interval as 'day hour:minute:second"
+)
+AS
 (
     concat(
        CASE WHEN x >= 0 THEN '+' ELSE '-' END,

diff --git a/udfs/community/cw_substrb.sqlx b/udfs/community/cw_substrb.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /** Similar to Vertica SUBSTRB function, which treats the multibyte character string as a string of octets (bytes). */
-CREATE OR REPLACE FUNCTION ${self()}(str STRING, startpos INT64 /* 1-based */, extent INT64 /* 1-based */) RETURNS STRING AS (
+CREATE OR REPLACE FUNCTION ${self()}(str STRING, startpos INT64 /* 1-based */, extent INT64 /* 1-based */) RETURNS STRING 
+OPTIONS (
+    description="Similar to Vertica SUBSTRB function, which treats the multibyte character string as a string of octets (bytes)."
+)
+AS (
     (WITH octets AS (
         SELECT oct FROM UNNEST(TO_CODE_POINTS(CAST(str as bytes))) AS oct WITH OFFSET off WHERE (off+1) >= startpos and (off+1) < startpos+extent ORDER BY off
     )

diff --git a/udfs/community/cw_substring_index.sqlx b/udfs/community/cw_substring_index.sqlx
@@ -17,7 +17,11 @@ config { hasOutput: true }
 
 /* Similar to MySQL SUBSTRING_INDEX */
 CREATE OR REPLACE FUNCTION ${self()}(str STRING, sep STRING, idx INT64) RETURNS STRING
-LANGUAGE js AS """
+LANGUAGE js 
+OPTIONS (
+    description="Similar to MySQL SUBSTRING_INDEX"
+)
+AS """
   if (str === null || sep === null || idx === null) return null;
   if (sep == "") return "";
   var arr = str.split(sep);

diff --git a/udfs/community/cw_td_nvp.sqlx b/udfs/community/cw_td_nvp.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /* Similar to teradata NVP function - extract a value from a key-value separated string */
-CREATE OR REPLACE FUNCTION ${self()}(haystack STRING, needle STRING, pairsep STRING, valuesep STRING, occurence INT64) RETURNS STRING AS (
+CREATE OR REPLACE FUNCTION ${self()}(haystack STRING, needle STRING, pairsep STRING, valuesep STRING, occurence INT64) RETURNS STRING 
+OPTIONS (
+    description="Similar to teradata NVP function - extract a value from a key-value separated string"
+)
+AS (
    NULLIF(ARRAY(SELECT ARRAY_TO_STRING((select ARRAY_AGG(v) from UNNEST(kv) v WITH OFFSET o WHERE o > 0), valuesep) FROM (
         SELECT SPLIT(pairs, valuesep) AS kv, o FROM UNNEST(SPLIT(haystack, pairsep)) AS pairs WITH OFFSET o
    ) t WHERE kv[SAFE_OFFSET(0)] = needle ORDER BY o ASC)[SAFE_ORDINAL(occurence)], '')

diff --git a/udfs/community/cw_to_base.sqlx b/udfs/community/cw_to_base.sqlx
@@ -16,7 +16,11 @@ config { hasOutput: true }
  */
 
 /* Similar to Presto to_base function - convert decimal number to number with given base */
-CREATE OR REPLACE FUNCTION ${self()}(number INT64, base INT64) RETURNS STRING AS (
+CREATE OR REPLACE FUNCTION ${self()}(number INT64, base INT64) RETURNS STRING 
+OPTIONS (
+    description="Similar to Presto to_base function - convert decimal number to number with given base"
+)
+AS (
     (WITH chars AS (
         SELECT MOD(CAST(FLOOR(ABS(number)/POW(base, (FLOOR(LOG(ABS(number))/LOG(base)) + 1) - idx)) AS INT64), base) ch, idx
             from UNNEST(GENERATE_ARRAY(1, CAST(FLOOR(LOG(ABS(number))/LOG(base)) AS INT64) + 1)) idx