SQL Expression Function

The SQL expression function type is used to create functions over an SQL Expression. A function of this type allows simple calculations on the function's input values and it is preferred over creating custom function types when possible.

Configuration

A new function of type sql.expression named <id> can be created as follows:

mldb.put("/v1/functions/"+<id>, {
    "type": "sql.expression",
    "params": {
        "expression": <SqlSelectExpression>,
        "prepared": <bool>,
        "raw": <bool>,
        "autoInput": <bool>
    }
})

with the following key-value definitions for params:

Field, Type, DefaultDescription

expression
SqlSelectExpression

SQL expression function to run. Takes the same syntax as a SELECT clause (but without the SELECT keyword); for example 'x, y + 1 AS z'

prepared
bool
false

Do we pre-prepare the expression to be run many times quickly? If this is true, it will only be bound once, for generic inputs, and so will allow for quick individual queries, possibly at the expense of batch queries being slower. In this case, the expression also cannot refer to variables outside of the arguments to the expression. If this is false, the default, then for every query the expression will be specialized (rebound) for that query's data type. This can lead to faster batch queries, at the expense of a possibly high per-query overhead for individual queries.

raw
bool
false

If true, then the output will be raw (just the result of the expression will be returned; it will not be turned into a row and the name of the output will be ignored). If false (default), then the output will be structured into a row. For example, the expression 1 AS z will return 1 if raw is true, but {z: 1} if raw is false.

autoInput
bool
false

If true, then a function that takes a single parameter will automatically pass that parameter without needing to put it within an object. For example, if expression is x + 1, then with autoInput as false the function must be called with {x: 2} but with autoInput as true the function can be called with 2 and the x will be added automatically.

Example

The following function configuration will output a value z on a that is the sum of the x and y input values:

{
    "id": "expr",
    "type": "sql.expression",
    "params": {
        "expression": "x + y AS z"
    }
}

See also