Functions, Operators, and Extensibility

The engine learns new words

A database becomes much more powerful when its behavior is not sealed at compile time. Postgre-like systems treat functions, operators, types, casts, aggregates, and index support routines as cataloged objects. That choice turns the database from a fixed query engine into a platform for data behavior. Users can teach it new values and operations while the planner and executor still work through common interfaces.

At the surface, a function call is simple. `lower(name)` or `calculate_tax(amount)` reads as a single step. Internally, the analyzer resolves the function name against argument types, chooses an implementation, inserts casts if needed, and records the result type. The executor later evaluates the function for each relevant tuple or group. The function may be built in, loaded from an extension, written in SQL, or implemented in a trusted procedural language. The call site stays uniform while the implementation varies.

Operators are syntax with metadata

An operator is a cataloged function with syntax. The expression `a + b` resolves to an operator entry, which points to an underlying function and declares input and output types. This is why the same symbol can mean integer addition, numeric addition, interval addition, or a custom operation for a new type. The parser recognizes the operator shape, and the analyzer chooses the meaning.

Properties matter as much as code

A database that hard-codes every operator in the parser or executor may be fast to build at first, but every new type then requires engine changes. The planner cannot reason uniformly about user-defined behavior, and extensions become second-class patches. Cataloged behavior adds indirection, but it gives the engine a shared way to discover and use new semantics.

Function metadata matters as much as function code. Volatility tells the planner whether repeated calls with the same inputs can be assumed to return the same result. An immutable function can be folded or used in index expressions more safely than a volatile function that reads the clock or database state. Strictness says whether null inputs imply a null result. Cost estimates help the planner decide when evaluation is expensive. Parallel-safety properties decide whether calls can run in parallel workers.

Extensibility reaches storage

Types make the rest of extensibility possible. A type needs storage representation, input and output functions, comparison behavior, and sometimes binary send and receive routines. Once a type is cataloged, columns can use it, functions can accept it, operators can compare it, and indexes can support it through operator classes. The database does not need to understand every domain-specific value deeply. It needs reliable hooks that describe how values behave.

Aggregates are another compositional extension point. An aggregate is more than a function over a list. It has transition state, transition functions, optional combine functions, final functions, and sometimes serialization behavior. This lets the executor compute sums, averages, arrays, statistical measures, or custom summaries through a common aggregation framework. Parallel aggregation becomes possible when the aggregate declares how partial states combine.

Index extensibility is especially demanding. A B-tree operator class tells the index how to order values. Other access methods need different support functions, such as distance, containment, token extraction, page summarization, or consistency checks. The planner must know which predicates an index can support, and the executor must call the access method correctly. Extensibility here reaches across catalogs, planning, storage, and execution.

The danger is that extensibility can weaken guarantees if metadata lies. A function marked immutable but reading a table can let the planner make invalid assumptions. An operator class with inconsistent ordering can corrupt index semantics. A type whose comparison is not transitive can make sorted structures unreliable. The database can enforce interfaces, but it cannot always infer truth. Extension authors become participants in the engine's correctness.

Security boundaries matter too. Functions may run with the caller's privileges or the definer's privileges. Procedural languages may be trusted or untrusted. Search paths can affect which function name resolves. A system that allows executable behavior inside the database must be careful about who can create it and under what identity it runs.