Define additional SQL functions adding-additional-sql-functions

Adobe Campaign allows the user to define their own functions that can access SQL functions, both those offered by the database and those which are not already available in the console. This is useful for aggregate functions (average, maximum, sum) for example, which can only be calculated on the server or when the database provides an easier way to implement certain functions, rather than “manually” write the expression in the console (e.g. date management).

This mechanism can also be used if you wanted to use a recent or uncommon database engine SQL function, which is not yet offered by the Adobe Campaign console.

Once these functions have been added, they will appear in the expression editor just like other predefined functions.

Installation installation

The function(s) to add are in a “package” file in XML format, whose structure is detailed in the following paragraph.

To install it from the console, select the Tools/Advanced/Import package options from the menu, then the Install from file , and follow the instructions in the import assistant.

General structure of package to import general-structure-of-package-to-import

The function(s) to be added can be found in the “package” file in XML format. Here is an example:

<?xml version="1.0" encoding='ISO-8859-1' ?>
<!-- ===========================================================================
  Additional SQL functions for Adobe Campaign
  ========================================================================== -->
<package
  namespace   = "nms"
  name        = "package-additional-funclist"
  label       = "Additional functions"
  buildVersion= "7.1"
  buildNumber = "10000">

  <entities schema="xtk:funcList">
    <funcList name="myList" namespace="cus">
      <group name="date" label="Personalized date">
        <function name="relativeMaturity" type="long" args="(<Âge>)" help="Returns the difference between a date and 18 years"
                  minArgs="1" maxArgs="1" display="Relative maturity of the person born on the date $1">
          <providerPart provider="MSSQL,Sybase,PostgreSQL" body="extract(year from age($1))-18"/>
        </function>
      </group>
    </funcList>
  </entities>
</package>

If you wish to add functions to several different groups, you can make several <group> elements become tracked in the same list.

Finally, a <group> element can contain the definition of one or several functions, that is the purpose of the package file. The <function> element is detailed in the following paragraph.

Function descriptor <function></function> function-descriptor--function-

The case presented here is a general case whereby we wish to provide the function implementation.

Below is an example of a “relative maturity” function which, using an age, indicates for how many years the person has been considered mature.

 <function name="relativeMaturity" type="long" args="(<Âge>)" help="Returns the difference between a date and 18 years"
              minArgs="1" maxArgs="1" display="Relative maturity of the person born on the date $1">
       <providerPart provider="PostgreSQL" body="extract(year from age($1))-18"/>
       <providerPart provider="MSSQL,Sybase,Teradata" body="[Other implementation]"/>
    </function>

The @name field refers the name of the function, and “args” is the list of parameters that will be displayed in the description. In this case, the function will appear as “relativeMaturity ( <age> )” in the function selection window.

‘Pass-through’ function descriptor pass-through--function-descriptor

A special function descriptor is the “pass-through” block, with an unspecified “provider” database system. In this case, “body” implementation can only contain a single function call with a syntax that is not dependent on the database used. Meanwhile, the “ProviderPart” block is unique.

    <function name="CountAll" args="()" help="Counts the values returned (all fields together)"
              type="long" minArgs="0" maxArgs="0">
      <providerPart body="Count(*)"/>
    </function>

In this case, adding a function only serves to make a database function that would not have been available by default, now visible to the client.

Examples examples

Further function examples can be found in the predefined package “xtkdatakitfuncList.xml”.