Implement a setup argument

You can define a UDA so that the user can supply a setup argument to the aggregate. The setup argument can customize the aggregate for a particular invocation.

For example, the PERCENT_GTR user-defined aggregate (see The PERCENT_GTR user-defined aggregate) determines the percentage of numbers greater than a particular value. The UDA could have been implemented so that the value to compare against is hardcoded into the UDA. However, this would mean a separate user-defined aggregate that checks for values greater than 10, another that checks for values greater than 15, and so on.

Instead, the PERCENT_GTR aggregate accepts the value to compare against as a setup argument. In this way, the end user can determine what values are needed, as follows: 
SELECT PERCENT_GTR(col1, 10) FROM tab1; -- values > 10;
SELECT PERCENT_GTR(col1, 15) FROM tab1; -- values > 15;

The database server passes in the setup argument as the second argument to the INIT function. Therefore, the INIT support function must handle the setup argument. Usually, this handling involves performing any initial processing required for the value and then saving this value in the aggregate state. It might also check for a possible SQL NULL value as a setup argument.

This setup argument is optional, in the sense that you can define a UDA with one or without one. However, if you define your UDA to include a setup argument, the end user must provide a value for this argument. When the UDA is invoked with two arguments (aggregate argument and setup argument), the database server looks for an INIT function with two arguments. If you omit the setup argument when you invoke the UDA, the database server looks for an INIT function with just one argument.

To indicate no setup argument, the end user can provide the SQL NULL value as a setup value. However, if you really want to make the setup argument truly optional for the end user, you must create and register two INIT functions:
  • One that takes two arguments
  • One that takes only one argument

    In this case, you can assign the setup argument some known default value.

As the writer of the UDA, you need to decide whether this feature is useful.