This example creates a function named udfSubStr, which extracts a substring from an input string based on the start position. The function has the following input parameters:
- inputString VARCHAR(512)
- start INTEGER
The function returns the extracted substring, which is a character string with a maximum of 512 characters. The function is written in C and the source file is substr.c.
The example uses the following organization, environment, and database user:
- Organization whose org_key is examplecorp
- Environment whose account_name is ExampleCorp-Tenant-001
- Database user whose username is bobby
The example creates the UDF in the MarketingDB database. The UDF manifest.json file and all the source files for the UDF are located in the ./udf_src directory.
To create the UDF, run tdextroutine as follows:
tdextroutine -k examplecorp -n ExampleCorp-Tenant-001 -u bobby udf create -s ./udf_src -d MarketingDB udfSubStr
Using Environment Variables and the Config YAML File
You can create a config YAML file to define the defaults for the tdextroutine options. The default location of the config file is ~/.tdextroutine/config.yml. For example:
# This is the file ~/.tdextroutine/config.yml org_key: examplecorp account_name: ExampleCorp-Tenant-001
Important:
For security reasons, you should not set the username in the config YAML file. Use an environment variable to pass the value instead.
export TDEXTROUTINE_USERNAME=bobby
The example call to tdextroutine to create the UDF can be shortened if using the config file and the environment variables to define options:
tdextroutine udf create -s ./udf_src -d MarketingDB udfSubStr
For the names of all the environment variables you can define and all the available options to tdextroutine, see Specifying tdextroutine Options and Environment Variables and tdextroutine UDF Subcommands and Options.
Sample manifest.json File
{
"manifest_version": "v1",
"parameter_spec": " inputString VARCHAR(512), start integer",
"return_spec": "VARCHAR(512)",
"parameter_style": "TD_GENERAL",
"execute_on": "all"
"execute_map": "map name"
"interim_size": 0,
"source_language": "C",
"files": [
{
"location": "client",
"type": "source",
"name_on_server": "substr",
"filepath": "substr.c"
}
]
}
Example Query: Calling the udfSubStr Function
SELECT udfSubStr(string,2);