SELSimple Expression Language
GitHub

Extending SEL

There are three ways to extend SEL, and they differ in who does it and what it costs:

Who Where it lives Cost
Host functions an application the application's own code, at start-up one call per function, in one language
Extending the SQL layer an application the application's own code, at start-up one call per difference from a shipped dialect
A builtin for everyone a contributor the specification, the suite and all five hosts a change to the language

The first two change nothing about SEL and need no one's agreement. The third is how SEL itself grows — and why it grows slowly.

Host functions

An application adds functions of its own — a stock lookup, a formatter, a message queue — with one registration call. A registered function is called like a builtin and is held to the same rules: strict, arity checked when a rule is compiled, arguments read through the same typed readers, errors with positions.

python
def stock(args):
    return Value.int(inventory.get(args.text(0), 0))


def reserve(args):
    sku, qty = args.text(0), args.non_neg_int(1)
    if inventory.get(sku, 0) < qty:
        return Value.bool(False)
    inventory[sku] -= qty
    return Value.bool(True)


def weight(args):
    return Value.text(weights.get(args.text(0), '0'))


def notify(args):
    outbox.append(f'{args.text(0)}: {args.text(1)}')
    return Value.bool(True)


register_function('STOCK', 1, 1, stock)
register_function('RESERVE', 2, 2, reserve)
register_function('WEIGHT', 1, 1, weight)
register_function('NOTIFY', 2, 2, notify)

The rules, the same in every host (spec §8.1):

  • Register before compiling. An unknown name is a compile-time error, so a rule that calls your function must be compiled after the registration.
  • Add, never change. The name of a builtin, or a reserved word, is refused. Registering your own name again replaces the function for rules compiled afterwards; rules already compiled keep the one they were compiled with.
  • Strict. Arguments are evaluated once, left to right, before your function runs; read them through the accessor (text, bool, int, nonNegInt, val and their spellings), which raises the usual error at the argument's own position.
  • Return a new value; do not modify the arguments.
  • Fail with a code. Raise the host's SelError with a code from the catalogue — E_BAD_ARG is usually right — and the argument's position; any other exception is the host's and passes through untouched.
  • SQL only if you spell it. Until it has a SQL spelling for a dialect, a rule that calls it is refused by the translator and kept in memory by the planner — below is how to give it one.

Scripting with host functions is a complete program built this way.

Giving a host function a SQL spelling

When the database can compute what your function computes, give the function a SQL spelling for that dialect, with the same define that respells a builtin — after registering the function. Here, from Your own functions, in SQL, four functions are spelled as PostgreSQL functions the application created, and one as an inline expression over a list:

python
map.define('postgresql', 'funcs', 'SLUG',
           {'tpl': 'slug({0})', 'ret': 'TEXT', 'args': ['TEXT']})
map.define('postgresql', 'funcs', 'MARGIN_PCT',
           {'tpl': 'margin_pct({0}, {1})', 'ret': 'NUM', 'args': ['NUM', 'NUM']})
map.define('postgresql', 'funcs', 'VAT_RATE',
           {'tpl': 'vat_rate({0}, {1})', 'ret': 'NUM', 'args': ['TEXT', 'TEXT']})
map.define('postgresql', 'funcs', 'SHIPPING_COST',
           {'tpl': 'shipping_cost({0}, {1})', 'ret': 'NUM', 'args': ['NUM', 'TEXT']})
map.define('postgresql', 'funcs', 'HAS_TAG',
           {'tpl': '({1} = ANY(ARRAY[{0}]))', 'ret': 'BOOL', 'args': ['LIST', 'TEXT']})
# WORDS returns a list: no spelling can say that, so it has none.

args declares what each argument must be (ANY, TEXT, NUM, BOOL, BIN, LIST), and so how it renders: a NUM goes through the numeric guard unless it is a declared number, and a LIST expands into its elements for the template to bracket. The spelling is your promise that the database computes what your code computes — SEL checks the shape, marks every such fragment with the caveat host-function, and refuses it under strict. A function that returns a list has no spelling. The rules in full are in the SQL reference.

Extending the SQL layer

A deployment is rarely exactly one of the shipped dialects: a driver wants numbered placeholders, a server lacks a function, an extension adds one. A dialect is registered, not forked — the new one names its parent and states only its differences, and everything else is inherited key by key.

A dialect of your own

python
map.define_dialect('pg-libpq', {
    'extends': 'postgresql',
    'version': '15',
    'target': True,                        # a base is not a target; this is a server
    'lexical': {'placeholder': '${n}'},    # libpq numbers its parameters
})
print('   targets      =>', ' '.join(Sql.dialects()))
print('   chain        =>', ' -> '.join(map.chain('pg-libpq')))
print('   base         =>', sql_in('postgresql'))
print('   pg-libpq     =>', sql_in('pg-libpq'))

Spelling a function differently

A template's {0}, {1}, … are the arguments (zero-based: these are template holes, not SEL positions) and {*} all of them. An entry says what it returns, because the translator infers kinds and will not guess.

python
map.define('pg-libpq', 'funcs', 'UPPER', {'tpl': 'UPPER({0} COLLATE "C")', 'ret': 'TEXT'})
print('   upper        =>',
      Sql.translate(compile('UPPER(NAME)'), 'pg-libpq', bindings).as_value())

Withdrawing what a server does not have

An entry of nothing withdraws the function: a rule using it is refused on this dialect, rather than emitted against a function the server lacks.

python
map.define('pg-libpq', 'funcs', 'RMATCH', None)
re = compile('RMATCH(\'^a\', NAME)')
print('   postgresql   =>', 'refused' if Sql.try_translate(re, 'postgresql', bindings)
      is None else 'translated')
print('   pg-libpq     =>', 'refused' if Sql.try_translate(re, 'pg-libpq', bindings)
      is None else 'translated')

A builder, for what a template cannot say

A builder receives the emitter and the arguments already rendered, and returns a fragment. Splice the arguments' parts rather than their text, so a bound value stays bound.

python
map.define_builder('pg-libpq', 'funcs', 'LEN', lambda emit, args, _at:
                   Fragment(['length(', *args[0].parts, ')'], 'NUM', emit.dialect()))
print('   len          =>',
      Sql.translate(compile('LEN(NAME)'), 'pg-libpq', bindings).as_value())

Registrations are checked against the same rules the shipped map is held to (sql/MAP.md), and a malformed one is a start-up error of the host, never an SQL refusal. map.reset() and its spellings drop every registration — worth calling between tests.

A builtin for everyone

A function in SEL itself is a change to the language, and it arrives in all five hosts at once or not at all — a function in one host is a function nobody has compared with anything. The order of work:

spec/SPEC.md §7 and spec/builtins.json    say what it does, and its arity
conformance/*.selt                        cases that fail
js/ php/ python/ cpp/ lisp/               implement, in that order or any other
node tools/gen-builtins.mjs               render the manifest into every host
sql/dialects/*.json + sql/cases/*.sqlt    a SQL spelling, if it has an exact one
tools/check.sh                            ALL GREEN, or it isn't done

Most functions are strict — they receive values, and the argument accessor does the arity, type and position work, so a function is a few lines per host. A function that must not evaluate something — a branch, a body per element — is lazy and receives syntax. Both are worked end to end, in all five hosts, in Contributing, with the reference fragments in examples/fn-simple and examples/fn-complex; giving a builtin a SQL spelling is examples/fn-sql.

Adding an operator, and adding a sixth host, are in Contributing too.

View this page's Markdown on GitHub