Functions & Aggregates

The most common way to extend DortDB is to add functions, operators, aggregates, and castables, grouped into an extension that you register on the engine.

Add a function

A function needs a unique name and an implementation. Mark it pure when it has no side effects and returns the same output for the same input, which lets the optimizer fold it at plan time:

const myExtension = {
  functions: [{ name: 'double', impl: (v: number) => v * 2, pure: true }],
};

const db = new DortDB({
  mainLang: SQL(),
  optimizer: { rules: defaultRules },
  extensions: [myExtension],
});

db.query('SELECT double(t.x) AS d FROM t');
// with t = [{ x: 5 }, { x: 7 }] → [{ d: 10 }, { d: 14 }]

Add an aggregate

An aggregate is three functions: init creates the starting state, step folds in each value, and result extracts the final value.

const myExtension = {
  aggregates: [
    {
      name: 'product',
      init: () => 1,
      step: (state: number, v: number) => state * v,
      result: (state: number) => state,
    },
  ],
};

db.query('SELECT product(t.x) AS p FROM t');
// with t = [{ x: 5 }, { x: 7 }] → [{ p: 35 }]

By default aggregates ignore nulls; set includeNulls: true to fold them in. An optional stepInverse, which undoes a step, speeds up window-style use.

Add an operator or a cast

An operator has the same shape as a function and backs query syntax such as + or like. Only languages that support user-defined operators, SQL among them, pick it up:

const myExtension = {
  operators: [{ name: '<->', impl: (a: number, b: number) => Math.abs(a - b) }],
};

db.query('SELECT t.x <-> 6 AS dist FROM t');
// with t = [{ x: 5 }, { x: 7 }] → [{ dist: 1 }, { dist: 1 }]

A castable backs a cast keyword, as in SQL's CAST(val AS mytype):

const myExtension = {
  castables: [
    { name: 'celsius', convert: (v: string) => parseFloat(v) - 273.15 },
  ],
};

db.query('SELECT CAST(t.kelvin AS celsius) AS c FROM t');
// with t = [{ kelvin: '373.15' }] → [{ c: 100 }]

Restrict it to one language, or namespace it

scope limits an extension to specific languages, and schema prefixes all of its members:

const xqueryOnly = {
  scope: ['xquery'],
  schema: 'my',
  aggregates: [/* ... visible to XQuery only, as my:name ... */],
};

A packaged example: datetime

The provided @dortdb/datetime extension bundles date/time helpers under the date schema, and is a good reference for how an extension is shipped as its own package:

import { datetime } from '@dortdb/datetime';

const db = new DortDB({
  mainLang: SQL(),
  optimizer: { rules: defaultRules },
  extensions: [datetime],
});

db.query(
  "SELECT date.extract(date.sub(now(), interval('3 years')), 'year') AS y FROM t",
);