Extensions

Why use Extensions

Extensions offer a streamlined deployment model for your extensibility content. To build your (PSQL or Wasm) functions and aggregates using Extensions:

• Store the code in a repository,

• Integrate (add) your code into an Extension, and

• Deploy it using the CREATE EXTENSION command.

You can define both Wasm-based and PSQL functions (UDFs or UDAFs) within the same Extension. Without Extensions, each function (PSQL or Wasm) will have to be created separately. And for Wasm functions, the Wasm modules (.wasm and .wit files) will have to be passed separately to each function definition, even if multiple functions share the same Wasm modules.

You can also create an Extension from a pre-packaged archive that contains an install script and the required resource files, from any of the supported sources.

Define Extensions

An Extension is an archive that contains an install script and, optionally, other resource files such as Wasm binaries and WIT interfaces.

Syntax

CREATE EXTENSION <extension_name> FROM <contentSrc> [GROW WASM TO <numBytes>]

<contentSrc> :
    BASE64 "<base64>"                 |
    HTTP "<url>" <cred> <config>      |
    LINK <linkName> <connStr>         |
    S3 <connStr> <cred> [<config>]    |
    AZURE <connStr> <cred> [<config>] |
    GCS <connStr> <cred> [<config>]

linkName: <link connection name>
connStr: <provider-specific path to a resource>
path: <standard filesystem path>
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>']

DROP EXTENSION <extension_name>

Remarks

• Each DDL statement in the install script must either be a CREATE FUNCTION or CREATE AGGREGATE statement.

• A PSQL CREATE FUNCTION statement in the install script must not refer to session variables. It can refer to other functions defined in the install script or functions provided by the database.

• If a function in the install script depends on another function in the script, the dependent function must be defined after the function it depends on.

• To delete all the modules in an Extension object and all the associated metadata, run the DROP EXTENSION command.

Extension Sources

SingleStore supports following sources for creating Extensions:

• Base-64 encoded string: You can pass the contents of an Extension module directly to the CREATE EXTENSION statement as an inline base-64 encoded string. The maximum size of the CREATE EXTENSION statement can be modified using the max_allowed_packet global variable.

• Remote URL: To download the content of an Extension module from a remote endpoint, you can specify the URL of the endpoint using the HTTP clause.

  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 EXTENSION command.

• Cloud Provider: You can download the content of an Extension module from the following cloud providers: S3, Azure, and GCS. You also can specify the URL and connection credentials using the LINK clause.

Examples

The following examples uses the String to TS9 GitHub repository to build or import the files required to create an Extension. The Extension creates two functions str_to_ts9() and ts9_to_str() that convert date-time values from string to TIMESTAMP(9) and vice-versa, respectively.

CREATE EXTENSION ts9 FROM HTTP <link_to_ts9.tar>;

SHOW FUNCTIONS;

+---------------------+-----------------------+----------+-------------+--------------+------+
| Functions_in_dbTest | Function Type         | Definer  | Data Format | Runtime Type | Link |
+---------------------+-----------------------+----------+-------------+--------------+------+
| str_to_ts9          | User Defined Function | s2user@% |             | Wasm         |      |
| ts9_to_str          | User Defined Function | s2user@% |             | Wasm         |      |
+---------------------+-----------------------+----------+-------------+--------------+------+

DROP EXTENSION ts9;

SELECT str_to_ts9("2024-01-16 04:38:25.941891999") AS 'Result';

+---------------------+
| Result              |
+---------------------+
| 1705379905941891999 |
+---------------------+

SELECT ts9_to_str("1705379905941891999") AS 'Result';

+-------------------------------+
| Result                        |
+-------------------------------+
| 2024-01-16 04:38:25.941891999 |
+-------------------------------+

CREATE LINK accessLink AS S3
    CREDENTIALS '{
            "aws_access_key_id": "ASIAZPIKLSJ3HM7FK...", 
            "aws_secret_access_key": FwoGZXIvYXdzEL3fv [...]"
    }' 
    CONFIG '{"region": "us-east-1"}';
    
CREATE EXTENSION extMod FROM LINK accessLink 'modules/extMod.tar';