Tip: how to best maintain a BQ JS UDF... in includes

[yan-hic]

If you have the need to define a BigQuery UDF in JS in SQLX, you cannot leverage the UI code typing/correction/formatting. In SQLX, the UI understand "regular" SQL UDF since these are ... SQL but for JS, the code would be considered as a large multiline string.

Unless you leverage interpolation of the source code like so:

1. Copy and maintain your function code in an include.js e.g.

function udf(schema, compressed) {
  // my code here e.g.
  return schema;
};​

2. add `udf` to modules.exports{}

3. in SQLX, define you BQ UDF like this:

create temp function selectList(schema JSON, compressed STRING)
RETURNS JSON
LANGUAGE js AS r"""${myinclude.udf};return udf(schema, compressed)""";​

Note that I don't pass any args to the function, just the function name, which will render its source code. You then need to add a wrapper to call the inner function and return the result.

Voila ! Much better in my mind that lengthy JS code hard to format, and to allow easier testing.