Chapter 18. Portable Named-Function Dialect

Every MobilityDB operator is also callable as a bare named function. A query written with named functions only, using no operator symbols, is portable: the same SQL runs unchanged across the MobilityDB ecosystem, the database engines MobilityDB (PostgreSQL), MobilityDuck (DuckDB) and MobilitySpark (Apache Spark), and the streaming engines, several of which cannot register PostgreSQL operator symbols. The named functions form one SQL dialect across these batch and stream SQL bindings, so each name must also be free in every engine: a quantified comparison therefore spells out its operation, as in eLessThan, since eLt would coincide with the function elt of Apache Spark and Apache Flink, and a position test carries the prefix of the class it compares, as in spanLeft, since left and right are functions of both engines. Each named alias reuses the operator's own implementation, so it returns exactly the same result as the operator.

This chapter is the canonical, ecosystem-wide reference for that mapping. An engine that does not accept a given operator can look it up here and use the function form instead. For example, DuckDB does not accept any operator containing # (it reserves # for positional column references), so on MobilityDuck the temporal comparisons (#=, …) are written tEqual, … and the time-position tests (<<#, #>>, &<#, #&>) are written under the prefix of the class compared, as spanBefore, spanAfter, spanOverbefore, spanOverafter between time spans, while the operators it does accept may be used directly.

The spatial-relationship functions (eIntersects, aIntersects, eTouches, eDwithin, …) and the restriction functions (atTime, atGeometry, atValue, …) are only ever named functions (they have no operator). The B-tree ordering comparator is likewise the named function cmp(a, b), which returns -1, 0 or 1 and has no operator. Every operator maps to a named function as follows; for the arithmetic and set-theoretic operators the function name carries the operand's type-family prefix (set, span or spanset, and t for temporal values), and for the position operators the prefix of the class compared: set, span or spanset for sets, spans and span sets, and for a box or a temporal value the type of its bounding box, tbox, stbox or tpcbox, a temporal Boolean, text or JSONB value taking span, the time span that bounds it.

For example, the bounding-box overlap t1.trip && t2.trip is written portably as overlaps(t1.trip, t2.trip), and the temporal-before test p1.period <<# p2.period as spanBefore(p1.period, p2.period).

Aggregate functions follow the same principle, and are listed in their own table below so that an engine can look up the machinery it needs without hunting through the prose. A SQL aggregate is assembled from up to three PostgreSQL machinery functions, each exposed under a canonical name built from the aggregate name and its role: a transition function <aggregate>Transition (always present), a combine function <aggregate>Combine used for parallel aggregation, and a final function <aggregate>Final. The combine and final roles are each optional: an aggregate whose transition state is already its result has no final function (for example extent), and an aggregate that does not support partial aggregation has no combine function (for example the append aggregates). The roles stay distinct and uniformly named so that every binding can reconstruct the aggregate from the catalog. The following table lists every aggregate and its machinery functions; a dash (—) marks a role the aggregate does not define.