Use UDFs in JavaScript
This article provides a step-by-step guide for defining JavaScript functions in RisingWave.
JavaScript code is inlined in CREATE FUNCTION
statement and then run on the embedded QuickJS virtual machine in RisingWave. It does not support access to external networks and is limited to computational tasks only. Compared to other languages, JavaScript UDFs offer the easiest way to define UDFs in RisingWave.
Define your functions
You can use the CREATE FUNCTION command to create JavaScript UDFs. See the syntax as follows:
The argument names you defined can be used in the function body. For example:
See the correspondence between SQL types and JavaScript types in the Data type mapping. You need to ensure that the type of the return value is either null
or consistent with the type in the RETURNS
clause.
If the function you defined returns a table, you need to use the yield
statement to return the data of each row. For example:
Use your functions
Once the UDFs are created in RisingWave, you can use them in SQL queries just like any built-in functions. For example:
Define your aggregate functions
You can create aggregate functions using the CREATE AGGREGATE command. Refer to the syntax below:
In the function_body, the code should define several exported functions to implement the aggregate function.
Required functions:
create_state() -> state
: Create a new state.accumulate(state, *args) -> state
: Accumulate a new value into the state, returning the updated state.
Optional functions:
finish(state) -> value
: Get the result of the aggregate function. If not defined, the state is returned as the result.retract(state, *args) -> state
: Retract a value from the state, returning the updated state. If not defined, the state can not be updated incrementally in materialized views and performance may be affected.
The following command creates an aggregate function named weighted_avg
to calculate the weighted average.
Data type mapping
The following table shows the data type mapping between SQL and JavaScript:
SQL Type | JavaScript Type | Note |
---|---|---|
boolean | boolean | |
smallint | number | |
int | number | |
bigint | number | |
real | number | |
double precision | number | |
decimal | BigDecimal | BigDecimal is in TC39 proposal stage, implemented by QuickJS |
date | not supported yet | |
time | not supported yet | |
timestamp | not supported yet | |
timestamptz | not supported yet | |
interval | not supported yet | |
varchar | string | |
bytea | Uint8Array | |
jsonb | null, boolean, number, string, array or object | JSON.parse(string) |
smallint[] | Int16Array | |
int[] | Int32Array | |
bigint[] | BigInt64Array | |
real[] | Float32Array | |
double precision[] | Float64Array | |
others[] | array | |
struct<..> | object |