Creates a custom (scalar) SQL function that can be used in queries.

Examples

const log = (value: unknown) => {
  console.log(value);
  return value;
};
db.createFunction(log);
db.query("SELECT name, log(updated_at) FROM users");

If a function is pure (i.e. always returns the same result given the same input), it can be marked as deterministic to enable additional optimizations.

const discount = (price: number, salePercent: number) => num * (1 - salePercent / 100);
db.createFunction(discount, { deterministic: true });
db.query("SELECT name, discount(price, :sale) FROM products", { sale: 15 });

The function name can be set explicitly.

db.createFunction(() => Math.random(), { name: "jsRandom" });
db.query("SELECT jsRandom()");

Functions can also take a variable number of arguments.

const sum = (...nums: number[]) => nums.reduce((sum, num) => sum + num, 0);
db.createFunction(sum, { deterministic: true });
db.query("SELECT sum(1, 2), sum(1,2,3,4)");