> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CREATE FUNCTION

> The `CREATE FUNCTION` command can be used to create [user-defined functions](/sql/udfs/user-defined-functions) (UDFs).

There are three ways to create UDFs in RisingWave: UDFs as external functions, embedded UDFs and SQL UDFs. `CREATE FUNCTION` can be used for them with different syntax.

## UDFs as external functions

You can define your own functions (including table functions) by some programming languages, like Python and Java, and call these functions in RisingWave.

The `CREATE FUNCTION` command is used to declare these UDFs. After that, you can use them in SQL queries like any built-in functions.

### Syntax

```sql theme={null}
CREATE FUNCTION [ IF NOT EXISTS ] function_name ( argument_type [, ...] )
    [ RETURNS return_type | RETURNS TABLE ( column_name column_type [, ...] ) ]
    [ LANGUAGE language_name ]
    [ IMMUTABLE | STABLE | VOLATILE ]
    AS function_name_defined_in_server
    USING LINK 'udf_server_address'
    [ WITH ( function_option = boolean [, ...] ) ];
```

> [!NOTE]
>
> Added in v2.3.0: Support `IF NOT EXISTS`.
>
> Available in v3.1.0-rc.1 and later: `USING LINK` accepts standard URLs: `https://` enables TLS, an omitted port defaults to the scheme (80/443), and a missing scheme defaults to `http://`.

### Parameters

| Parameter or clause | Description |
| :- | :- |
| *function\_name* | The name of the UDF that you want to declare in RisingWave. |
| *argument\_type* | The data type of the input parameter(s) that the UDF expects to receive. |
| **RETURNS** *return\_type* | Use this if the function returns a single value (i.e., scalar). It specifies the data type of the return value from the UDF.The struct type, which can contain multiple values, is supported. But the field names must be consistent between the programming language and SQL definitions, or it will be considered a type mismatch. |
| **RETURNS TABLE** | Use this if the function is a table-valued function (TVF). It specifies the structure of the table that the UDF returns. |
| **LANGUAGE** | **Optional**. Specifies the programming language used to implement the UDF.  Currently, Python, Java,Rust, and JavaScript are supported. |
| **IMMUTABLE \| STABLE \| VOLATILE** | **Optional**. Specifies a volatility declaration. `IMMUTABLE` declares a scalar UDF deterministic and side-effect-free. RisingWave trusts the declaration and treats a call as pure when all of its arguments are pure, so the call is accepted where impure expressions are rejected on RETRACT inputs, such as `WHERE`, `HAVING`, and join conditions. `IMMUTABLE` is supported only for scalar UDFs; table functions and aggregate functions reject it. An `IMMUTABLE` external scalar UDF must set `always_retry_on_network_error = true`. `STABLE` and `VOLATILE` are accepted but have no effect. See [Skip result materialization for a UDF](#skip-result-materialization-for-a-udf). |
| **AS** *function\_name\_defined\_in\_server* | Specifies the function name defined in the UDF server. |
| **USING LINK** '*udf\_server\_address*' | Specifies the UDF server address as an `http://` / `https://` URL; a link without a scheme, such as `host:port`, defaults to `http://`. `https` connects over TLS and verifies the server certificate against the system trust store; an omitted port defaults to 80 (`http`) or 443 (`https`). |
| **WITH** | **Optional**. Specifies UDF execution options. For result materialization options, see [Skip result materialization for a UDF](#skip-result-materialization-for-a-udf). |

### Examples

Use `CREATE FUNCTION` to declare an external UDF defined by Python or Java. For more details, see [Use UDFs in Python](https://docs.risingwave.com/sql/udfs/use-udfs-in-python.md) or [Use UDFs in Java](https://docs.risingwave.com/sql/udfs/use-udfs-in-java.md).

```sql theme={null}
CREATE FUNCTION gcd(int, int) RETURNS int
AS gcd
USING LINK 'http://localhost:8815'; -- If you are running RisingWave using Docker, replace the address with 'http://host.docker.internal:8815'.
```

## Embedded UDFs

Embedded UDFs are functions that run in the language runtime embedded in RisingWave computation nodes. The `CREATE FUNCTION` command is used to create these UDFs.

Here are some examples of embedded UDFs, along with links to specific guides on creating them with different languages.

```sql Embedded UDFs theme={null}
-- Embedded Python UDF
CREATE FUNCTION gcd(a int, b int) RETURNS int LANGUAGE python AS $$
def gcd(a, b):
    while b != 0:
        a, b = b, a % b
    return a
$$;
```

For more details, see [Embedded Python UDFs](https://docs.risingwave.com/sql/udfs/embedded-python-udfs.md).

```sql Embedded UDFs theme={null}
-- Embedded JavaScript UDF
CREATE FUNCTION gcd(a int, b int) RETURNS int LANGUAGE javascript AS $$
    while (b != 0) {
        let t = b;
        b = a % b;
        a = t;
    }
    return a;
$$;
```

For more details, see [Use UDFs in JavaScript](https://docs.risingwave.com/sql/udfs/use-udfs-in-javascript.md).

```sql Embedded UDFs theme={null}
-- Embedded Rust UDF
CREATE FUNCTION gcd(int, int) RETURNS int LANGUAGE rust AS $$
    fn gcd(mut a: i32, mut b: i32) -> i32 {
        while b != 0 {
            let t = b;
            b = a % b;
            a = t;
        }
        a
    }
$$;
```

For more details, see [Use UDFs in Rust](https://docs.risingwave.com/sql/udfs/use-udfs-in-rust.md).

## Skip result materialization for a UDF

> [!NOTE]
>
> Added in v3.2.0.

By default, RisingWave treats calls to external and embedded UDFs as impure for streaming planning. Declaring a scalar UDF `IMMUTABLE` makes the planner treat its calls as pure when their arguments are pure, but `IMMUTABLE` alone does not disable result materialization: an `IMMUTABLE` UDF without `unsafe_skip_materializing_exprs` keeps its results materialized on RETRACT inputs. Result materialization depends on the input stream kind:

| Project input stream kind | Result materialization |
| - | - |
| Append-only | Always skipped. |
| UPSERT | Always skipped, regardless of the UDF option or session setting. |
| RETRACT | Controlled by expression purity and the per-UDF option. The session-level override skips planner-inserted materialization for all impure expressions. |

Skipping result materialization does not skip UDF evaluation or storage of a query's output. It means that RisingWave does not create an additional internal state table to preserve previously evaluated expression results.

On a RETRACT input, RisingWave stores the complete projected expression when result materialization is required so that delete and update-delete records can reuse the previously evaluated result.

For a deterministic, side-effect-free UDF, set `unsafe_skip_materializing_exprs` to `true` to prevent that UDF from making an otherwise pure expression require result materialization on a RETRACT input. This is an unsafe optimization, not a correctness guarantee: RisingWave cannot verify the implementation, and re-evaluation can still produce inconsistent results or fail stream processing. The option defaults to `false`.

> [!NOTE]
>
> This option is supported only for scalar external and embedded UDFs. Table functions reject both `IMMUTABLE` and `unsafe_skip_materializing_exprs`, aggregate functions reject `IMMUTABLE`, and `LANGUAGE sql` functions reject `unsafe_skip_materializing_exprs`. A SQL UDF accepts `IMMUTABLE` without reading it: SQL UDFs are inlined at bind time, so their purity is derived from the function body.

```sql Skip result materialization for a deterministic UDF theme={null}
CREATE FUNCTION identity_without_stored_result(v int)
RETURNS int
LANGUAGE javascript
IMMUTABLE
AS $$
export function identity_without_stored_result(v) {
    return v;
}
$$
WITH (
    unsafe_skip_materializing_exprs = true
);
```

RisingWave rejects `unsafe_skip_materializing_exprs = true` unless the function is declared `IMMUTABLE`.

An `IMMUTABLE` external scalar UDF must also set `always_retry_on_network_error = true`, whether or not it sets `unsafe_skip_materializing_exprs`: a pure expression can be re-evaluated on a RETRACT input, and the re-evaluation must tolerate transient network failures. This option retries supported remote transport errors indefinitely, so a persistent network failure can stall streaming progress. `always_retry_on_network_error` is accepted only when the UDF implementation supports retrying on network errors. Embedded UDFs and external table functions currently reject it.

> [!WARNING]
>
> Enable this option only when repeated evaluations with the same input always return the same result and have no side effects. RisingWave checks for the `IMMUTABLE` clause but cannot verify the UDF implementation, so it trusts your determinism declaration. On a RETRACT input, RisingWave can reevaluate the UDF for delete and update-delete records when no result is stored. Incorrectly marking a non-deterministic UDF can therefore produce silently incorrect results. An existing consistency check might detect an inconsistency and fail a stream actor or compute node, but detection is not guaranteed when result materialization is skipped.

On a RETRACT input with the session-level override disabled, RisingWave still materializes the complete top-level projected expression if an argument contains an unmarked UDF or another impure expression. The per-UDF option does not selectively materialize nested subexpressions.

> [!NOTE]
>
> UPSERT projects always skip result materialization because computed expressions cannot be part of the internal stream key. For example, a keyed projection can evaluate a UDF for each insert or keyed overwrite without storing the previous UDF result. This does not make raw UPSERT input acceptable to operators such as aggregations that require old row values; the UDF option changes expression-result materialization, not the stream kind or an operator's UPSERT support.
>
> By default, sink planning rejects a computed downstream primary key that does not match the key derived from the stream. If [`streaming_unsafe_allow_upsert_sink_pk_mismatch`](https://docs.risingwave.com/operate/view-configure-runtime-parameters) permits this mismatch, a downstream primary-key change whose old value is not emitted can leave stale rows in the downstream system.

### Interaction with the session-level override

Keep [`streaming_unsafe_allow_unmaterialized_impure_expr`](https://docs.risingwave.com/operate/view-configure-runtime-parameters) set to `false` to use the scoped per-function behavior on RETRACT inputs. In this mode, only UDFs marked with `unsafe_skip_materializing_exprs = true` stop making an otherwise pure expression require materialization; other impure expressions on RETRACT inputs retain the default protection.

If `streaming_unsafe_allow_unmaterialized_impure_expr` is `true` when RisingWave plans a streaming job, RisingWave skips planner-inserted result materialization for all impure expressions on RETRACT inputs in that job, regardless of each UDF's `unsafe_skip_materializing_exprs` setting. This session-level override is broader and carries the same risks described above: results can be silently incorrect, and consistency checks might fail stream processing but cannot detect every inconsistency. It does not change append-only or UPSERT behavior because those projects already skip result materialization.

## SQL UDFs

SQL UDFs in RisingWave are designed to expand directly into expressions at the frontend, resulting in minimal performance difference compared to manually calling multiple functions.

The `CREATE FUNCTION` command is used to define SQL UDFs. You can read our guide on [SQL UDFs](https://docs.risingwave.com/sql/udfs/sql-udfs.md) for more details.

```sql Syntax of SQL UDFs theme={null}
CREATE FUNCTION [ IF NOT EXISTS ] function_name ( argument_type [, ...] )
    RETURNS return_type
    LANGUAGE sql
    { AS as_definition | RETURN return_definition };
```

> [!NOTE]
>
> Added in v2.3.0: Support `IF NOT EXISTS`.

## See also

> [card] [SHOW FUNCTIONS](https://docs.risingwave.com/sql/commands/sql-show-functions.md)
>
> Show all user-defined functions

> [card] [DROP FUNCTION](https://docs.risingwave.com/sql/commands/sql-drop-function.md)
>
> Drop a user-defined function


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.