User-defined functions

User-Defined Functions (UDF) are custom functions defined through dedicated metadata objects and used in mappings to share and re-use expressions or operations. A user-defined function can have different implementations depending on the technology running it so that the same function can be used seamlessly for different technologies.

Create user-defined functions

User-defined functions are declared and described using specific metadata resources named User-Defined Functions Libraries.

Create a user-defined functions library

User-defined functions libraries are metadata resources that can contain one or several user-defined functions.

To create a user-defined functions library:

  1. Create a new metadata. Select the UDF icon User-Defined Functions type.

    metadata creation wizard

  2. The User-Defined Functions wizard opens.

    1. Enter a Prefix for the functions. The prefix is used when calling the functions and must be unique.

      The user prefix is reserved and cannot be used as a prefix for user-defined functions.
    2. Optionally, add function signatures. For each function you want to add, proceed as follows:

      1. Enter a Function signature using the following format

        <function_name>($<param_name_1>,…​,$<param_name_n) (example: func1($param1,$param2).

      2. Click the add button button. The function is added to the list of Defined Functions.

        UDF creation wizard

        You can add function signatures at any time from the user-defined functions editor.
  3. Click Finish to close the wizard.

  4. Save the metadata.

Add a function to a user-defined functions library

To add a function signature to an existing user-defined functions library:

  1. In the metadata editor of the user-defined functions library, right-click the Library root node and select New > Function in the contextual menu. The function is added to the tree view with a default name.

  2. Enter a Name for the function in the properties panel or in the Properties view. The function’s name is updated in the tree view.

  3. To add parameters to the signature:

    1. Right-click the new function in the tree view and select New > Parameter.

    2. Enter a Name for the parameter.

  4. Save the metadata.

Add a parameter to a user-defined function

To add a parameter to an existing user-defined function:

  1. Right-click the function in the metadata editor and select New > Parameter.

  2. Enter a Name for the parameter.

  3. Save the metadata.

Define functions implementation(s)

A user-defined function may have one unique or several implementations to use depending on the technology.

To add a function implementation:

  1. In the metadata editor of the user-defined functions library, right-click the Library root node and select New > Implementation in the contextual menu.

  2. In the Properties view or side panel, Enter a Name for the implementation.

  3. Select the Product(s) (technologies) this implementation should be used for:

    1. Click the browse button button. The product selection dialog opens.

    2. Select one or several products in the Choices and click the add button.

      Product selection dialog

      To select several products, press and hold Ctrl while selecting each product. To select a range of products, press and hold Shift and then select the first and last product.
      One product can be referenced by one function implementation only.
  4. Enter the Expression for the function. This expression may use any built-in function that is offered by all the selected products.

    To call another user-defined function from the same library, use the following syntax:

    <prefix>::<function_name>(<param value>,…​,<param value>)

    To call a user-defined function from another library, you must add it to the Required Libraries of the current library first:

    1. Select the root node of the metadata, which corresponds to the library itself.

    2. In the Properties view or side panel, click the browse button next to the Required Libraries property.

      mappings udf libraries

    3. Select the required libraries, add them using the add button, and then click OK. mappings udf libraries selection

    4. You can then call all functions from the added libraries using the same syntax as previously:

    <prefix>::<function_name>(<param value>,…​,<param value>)

  5. Optionally, enter a Description.

  6. Save the metadata.

Use user-defined functions

User-defined functions can be called from any mapping expression:

  • Either by using the User defined functions dialog:

    1. Click the libraries icon button on the Expression Editor view’s toolbar. The User defined functions dialog opens.

      Expression editor

    2. Double-click user-defined or built-in functions to add them to the current expression.

      function selection

    3. Close the dialog when finished.

  • Or by directly editing the expression using the following syntax:

    <prefix>::<function_name>(<param value>,...,<param value>)

    Example

    lib1::func1('param1','param2')

    Use auto-completion by pressing Ctrl+Space.

Once the user-defined function is added inside the mapping expression, either from the dialog or manually, you can edit it to fill the parameters' values, if applicable.

The comma character is used to separate the parameters values in the mapping expression, and is therefore a reserved character. If you have a comma character inside a value you want to pass into a parameter, you can escape it with a backslash.

Example: To pass the value col1,col2,col3 as the first parameter of the function lib1::func1(<param 1>,<param2>), escape the comma characters with a backslash: lib1::func1(col1\,col2\,col3,value2).

Impact analysis

The Impact Analysis feature shows the usages of a user-defined function.

To open the Impact Analysis for a user-defined function:

  1. Right-click the function from the corresponding metadata editor or the Project Explorer and then select Impact Analysis.

    Impact Analysis menu item

  2. The Impact Analysis editor opens and shows the usages of the selected function.

    Impact Analysis