Similar to the SQL construct of table-valued functions, a Cinchy User Defined Function can be SELECTED or CROSS JOINED from -- as if it is a table.
The SELECT and FROM clause work the same for a table-valued UDF as they would for a regular Cinchy table.
To generate a table within a UDF for use in CQL, a dataTable will need to be created in the same format as the default Cinchy JSON Saved Query response (Image 1).
Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.
Select a scalar value UDF as a column (Image 1).
Scalar-valued functions have to be invoked with a parameter, even if the definition of the function does not require a parameter. You can pass a string:
SELECT my_scalar('a') FROM [Cinchy].[Tables] WHERE [Deleted] IS NULL AND [Cinchy Id]=1
Once it is confirmed that the UDF returns the expected result though a query, a calculated column can be include.
Depending on how intensive or live you want the calculation to be, choose whether to make it a live or cached calculated column.
Simply add your UDF to the calculated column (Image 2).
To use the UDF to trigger an action (ex. create a row in another table), it should be a cached calculated column. Here are a few scenarios to watch out for:
Do not trigger when you do not have all the necessary fields
Do not trigger when non-relevant data on the row changes
Make sure to appropriately insert and/or update in another table
User Defined Functions provide customers with a way to specify and use more particular logic in your solutions than plain CQL may allow. It can be used to simplify calculations and orchestrate automations to accommodate your business requirements.
UDFs are written in Javascript.
There are two (2) groups of Cinchy User Defined Functions (UDF's):
Table-Valued Functions - Similar to the SQL construct of table-valued functions, a Cinchy User Defined Function can be SELECTED or CROSS JOINED from as if it is a table.
Scalar-Valued Functions - Similar to the SQL construct of scalar-valued functions. A Scalar-valued function in Cinchy is used to return a single value of any CQL data type. The function body can execute any JavaScript logic.
Cinchy UDFs run https://github.com/sebastienros/jint, which uses ECMAScript 5.1.
If you are having issues with your script, we suggest pasting it into https://jshint.com/, a tool that helps to detect errors and potential problems in your JavaScript code, as it also runs ECMAScript 5.1.
User Defined Functions (UDFs) are registered in the Cinchy User Defined Functions table (Image 1).
Column Name
Description
Name
The Name column contains the name of the User Defined Function. WARNING: Do not name UDFs the same names as SQL or CQL functions (for example, do not name your UDF "CONCAT"). Doing so may cause your platform to break.
Script
The Script column can contain any number of JavaScript functions that are necessary and referenced by the single function that is registered in the Name column
A user defined function in Cinchy is written in Javascript, and comes in the form of:
It can perform external API calls and execute Cinchy Queries. Generally, at least something should be returned as an indication of success or failure even if you do not want to return any values.
Helper functions can be created within a UDF, however you cannot reference other UDFs in your UDF.
To use advanced functions in UDFs, import the following.
The following functions can be used in a Cinchy User Defined Function (but not in CQL directly).
An XMLHttpRequest() helper can be used to help POST or GET data from an external API. Note that Cinchy basicAuthAPIs can also be accessed this way.
Open
This creates the HttpClient()
setRequestHeader
This adds the header to the client
Send
This uses the client to call Get()
Send (postdata)
This uses the client to call POST, PUT, etc.
Status
This is attributed to show the status of a client response
responseText
This is attributed to show the response text after the client is called
A Cinchy query or a non query (not expecting a result back) can be executed in a UDF as well.
executeNonQuery
This is used for INSERTS, DELETES, and UPDATES. It returns a Long value.
executeQuery
This is used for SELECT statement. It returns system.data values.
executeBatchUpsert
This performs a batch upsert into Cinchy. It returns int values.