# Create Wasm TVFs

You can create Wasm TVFs using the `CREATE FUNCTION` statement.

> **📝 Note**: SingleStore supports only the [Canonical ABI](https://docs.singlestore.com/cloud/reference/code-engine-powered-by-wasm.md) with Wasm TVFs because the return type is `TABLE`.

For information on creating PSQL user-defined table-valued functions, refer to [CREATE FUNCTION (TVF)](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/create-function-tvf.md).

## Syntax

```sql
CREATE [OR REPLACE] FUNCTION function_name 
    { <explicitTypedPred> | <implicitTypedPred> }
    (USING EXPORT '"' <wasmExportName> '"')

explicitTypedPred: 
        <typeSpec> 
        AS WASM 
        (ABI CANONICAL )
        (GROW TO <numBytes>) 
        FROM <contentSrc>

implicitTypedPred: 
        RETURNS TABLE
        AS WASM 
        (ABI CANONICAL )
        (GROW TO <numBytes>)
        FROM <contentSrc> 
        (WITH WIT FROM <contentSrc>)


function_name: <standard function name>
typeSpec: '(' <paramList>? ')' RETURNS TABLE '(' <paramList> ')'
numBytes: [0-9]+
paramList: <param> (',' <param>)*
param: <varName> <dataType>
varName: <standard column name>

contentSrc: BASE64 '"' <base64> '"'               |
            HTTP '"' <url> '"' <cred>? <config>?  |
            LINK <linkName> <connStr>             |
            S3 <s3_config>?                       |
            AZURE <azure_config>?                 |
            GCS <gcs_config>?

linkName: <link connection name>
connStr: <provider-specific path to a resource>
base64: <base64-encoded string>
url: <standard URL form>
cred: CREDENTIALS '"' <credSpec> '"'
credSpec: <json with provider-specific credentials>
config: CONFIG '"' <configSpec> '"'
configSpec: <json with provider-specific configuration>

s3_config:
    S3 { '<bucket-name>' | '<bucket-name/path>' }
      [CONFIG '<configuration_json>']
      CREDENTIALS '<credentials_json>'

azure_config:
    AZURE { '<container-name>' | '<container-name/object-name>' | '<container-name/prefix/object-name>' }
      CREDENTIALS '<credentials_json>'
      [CONFIG '<configuration_json>']

gcs_config:
   GCS { '<bucket-name>' | '<bucket-name/path>' }
     CREDENTIALS '<credentials_json>'
     [CONFIG '<configuration_json>']
```

Here's an expansion of the syntax for an explicitly-typed Wasm TVF:

```sql
CREATE FUNCTION <name> '(' <paramList> ')'  
    RETURNS TABLE '(' <paramList> ')'
    AS WASM
    (ABI CANONICAL)
    (GROW TO <numBytes>)
    <contentSrc> 
    (USING EXPORT ‘"’ <wasmExportName> ‘"’)
```

Here's an expansion of the syntax for an implicitly-typed Wasm TVF using Canonical ABI:

```sql
CREATE FUNCTION <name> 
    RETURNS TABLE
    AS WASM 
    (ABI CANONICAL)
    (GROW TO <numBytes>)
    <contentSrc> 
    (WITH WIT <contentSrc>)
    (USING EXPORT ‘"’ <wasmExportName> ‘"’)
```

## Remarks

* The Wasm function must return an `ARRAY` of `RECORDS`, specified as `list<record-type>` in the WIT specification.
* You can call a Wasm TVF in the `FROM` clause of a `SELECT` statement.
* The implicitly-typed and explicitly-typed Wasm TVFs default to the Canonical ABI if the `ABI` clause is omitted in the `CREATE FUNCTION` statement.
* The WIT document specification must be specified.
* The `GROW TO` clause is optional for the Canonical ABI. The default size is `16 MB` (256 Wasm pages).

## Wasm TVF Sources

SingleStore supports Wasm TVFs created from the following sources in the `CREATE FUNCTION` statement:

* **Base-64 encoded string**: You can pass the contents of a Wasm module directly to the `CREATE FUNCTION` statement as an inline base-64 encoded string, see Examples. The maximum size of the `CREATE FUNCTION` statement can be modified using the `max_allowed_packet` global variable.
* **Remote URL**: To download the content of a Wasm module from a remote endpoint, you can specify the URL of the endpoint using the `HTTP` clause. Here's an example:
  ```sql
  CREATE FUNCTION wasm_udf AS WASM FROM HTTP 'https://url/to/udf.wasm' ...
  ```
  Optionally, you can specify the `CONFIG` and `CREDENTIALS` clauses for the URL. The `LINK` object is not supported with `HTTP`, and the credential configuration must be provided inline in the `CREATE FUNCTION` command.
* **Cloud Provider**: You can download the content of a Wasm module from the following cloud providers: S3, Azure, and GCS. You also can specify the URL and connection credentials using the `LINK` clause, see Examples.

For examples on using the `CONFIG <configSpec>` clause, see [BACKUP DATABASE](https://docs.singlestore.com/cloud/reference/sql-reference/operational-commands/backup-database.md).

> **📝 Note**: While not officially supported by SingleStore, you may use the `pushwasm` tool to upload the `.wit` and `.wasm` files to your workspace and create Wasm UDFs, UDAFs, or TVFs. For more information, refer to [pushwasm](https://github.com/singlestore-labs/pushwasm).

## Wasm UDF/TVF Naming Convention

You can specify a Wasm UDF/TVF name that is different from the WIT specification in the `CREATE FUNCTION` statement. When specifying a custom name for the Wasm UDF/TVF, you may use any name that is valid for a database UDF/TVF. This will be the Wasm function's alias in the database. Additionally, specify the real name of the Wasm function, as declared in the WIT file, in the `USING EXPORT` clause. This name is case-sensitive and can contain hyphens.

For example, consider the following WIT specification:

```wit
run: func(pass1: f32, pass2: f32, pass3: f32) -> f32
```

The following example specifies a custom name calc\_score:

```sql
CREATE FUNCTION calc_score AS WASM
FROM LINK rec 'module/run.wasm'
WITH WIT FROM LINK rec 'module/run.wit'
USING EXPORT "run";
```

You can use distinct aliases to create multiple UDFs/TVFs from the same Wasm function.

If the `USING EXPORT` clause is omitted from the `CREATE FUNCTION` statement:

* When using a WIT file, the name of the Wasm UDF/TVF **must** match the name of the corresponding Wasm function as declared in the WIT IDL, after substituting hyphens with underscores.
* When you are not using a WIT file, the name of the Wasm UDF/TVF **must** match the name of the function exported from the module. This is usually the same function name declared in the source language.

If the `USING EXPORT` clause is omitted and the Wasm module has functions with the same name but different case, the `CREATE FUNCTION` statement returns an error. You cannot import functions with matching case-insensitive names from a Wasm module.

## Examples

## Example 1

The following example creates a Wasm TVF (in Rust) that splits a string and returns the two sub-strings.

## Setup VS Code

1. Install the **Dev Containers** extension in VS Code.

2. Open the **Command Palette**, and select **Dev Containers: Open Folder in Container**.

3. Navigate to the directory where you want to build your project, and select **Open**.

4. On the **Select Features** dialog, from the list, select the **Rust** container.

   ![](https://images.contentstack.io/v3/assets/bltac01ee6daa3a1e14/blta1d0529adb1d5481/6a2c42332fabfd18a9d78002/wasm-rust-container-UkuJsz.png)

5. Use defaults for other configuration items.

6. After VS Code completes building the project, open a terminal. Install the `cargo-wasi` subcommand to build the code for `wasm32-wasi` target. Refer to [cargo wasi](https://github.com/bytecodealliance/cargo-wasi) for more information.
   ```shell
   cargo install cargo-wasi
   ```

Proceed with the example.

## Create a Wasm TVF

1. Initialize a Cargo package in this directory using the following command:
   ```shell
   cargo init --vcs none --lib
   ```

2. Update the **Cargo.toml** file with the following configuration:
   ```TOML
   [package]
   name = "split"
   version = "0.1.0"
   edition = "2018"

   [dependencies]
   wit-bindgen-rust = { git = "https://github.com/bytecodealliance/wit-bindgen.git", rev = "60e3c5b41e616fee239304d92128e117dd9be0a7" }

   [lib]
   crate-type = ["cdylib"]
   ```

3. Create a **.wit** file with the WIT specification, for example **split.wit**.
   ```wit
   record subphrase {
     str: string,
     idx: s32
   }
   split-str: func(phrase: string, delim: string) -> list<subphrase>
   ```

4. Add the following code to the **src/lib.rs** file:
   ```Rust
   wit_bindgen_rust::export!("split.wit");
   struct Split;
   use crate::split::Subphrase;

   impl split::Split for Split {

       fn split_str(phrase: String, delim: String) -> Vec<Subphrase> {
           phrase
               .split(&delim)
               .scan(0, |idx, s| {
                   let current = Subphrase {
                       str: s.to_string(),
                       idx: *idx as i32
                   };
                   *idx += (s.len() + delim.len()) as i32;
                   Some(current)
               })
               .collect()
       }
   }
   ```

5. Compile the program into a Wasm module using the following command:
   ```shell
   cargo wasi build --lib
   ```
   The **split.wasm** file is created in the **target/wasm32-wasi/debug/** directory.
   > **📝 Note**: When your code is debugged and ready for production, add the `--release` flag to the `cargo build` command instead to enable further optimizations and remove the debug info. For example,```shell
   > cargo build --lib --release
   > ```The resulting **split.wasm** file is created in the **target/wasm32-wasi/release/** directory.

6. Upload the **split.wasm** and **split.wit** files to your cloud storage account, say an S3 bucket named **wasm-modules**.

## Load the Wasm TVF

Execute the following command in the SingleStore command line:

```sql
CREATE DATABASE wasm_tutorial;
USE wasm_tutorial;
CREATE FUNCTION split_str RETURNS TABLE AS WASM FROM S3 'wasm-modules/split.wasm' 
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}'
WITH WIT FROM S3 'wasm-modules/split.wit'
        CREDENTIALS '{
                "aws_access_key_id": "ASIAZPIKLSJ3HM7FKAUB", 
                "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
        }' 
        CONFIG '{"region": "us-east-1"}';
```

This command imports the **split.wit** and **split.wasm** files created earlier in this example. The Wasm TVF is now ready to use. For example,

```sql
SELECT * FROM split_str('wasm_rocks_the_house', '_');

```

```output

+-------+-----+
| str   | idx |
+-------+-----+
| wasm  |   0 |
| rocks |   5 |
| the   |  11 |
| house |  15 |
+-------+-----+
```

## Example 2

You can use a Wasm TVF in the `FROM` clause of a query, i.e., you can specify the values from a table on the left of the Wasm TVF in the `FROM` clause as arguments to the Wasm TVF. Use a comma or the `JOIN` keyword before the Wasm TVF name in the `FROM` clause. This feature performs a an implicit lateral join between the expression on the left of the Wasm TVF and the results of the Wasm TVF.

The following example code uses the `split_str()` Wasm TVF from Example 1 and the **strg** table:

```sql
SELECT * FROM strg;

```

```output

+------------+
| Name       |
+------------+
| anna_moore | 
| adam_west  |
| sam_duke   |
+------------+
```

```sql
SELECT Name, str, idx FROM strg JOIN split_str(Name, '_');

```

```output

+------------+-------+-----+
| Name       | str   | idx |
+------------+-------+-----+
| anna_moore | anna  | 0   |
| anna_moore | moore | 5   |
| sam_duke   | sam   | 0   |
| sam_duke   | duke  | 4   |
| adam_west  | adam  | 0   |
| adam_west  | west  | 5   |
+------------+-------+-----+
```

For more examples on using Wasm TVFs, refer to [SingleStore Wasm Toolkit](https://github.com/singlestore-labs/singlestore-wasm-toolkit).

***

Modified at: January 25, 2024

Source: [/cloud/reference/code-engine-powered-by-wasm/create-wasm-tvfs/](https://docs.singlestore.com/cloud/reference/code-engine-powered-by-wasm/create-wasm-tvfs/)

(An index of the documentation is available at /llms.txt)