16.20 - Syntax for Aggregate Function Parameter Style SQL - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)
void  function_name ( FNC_Phase     aggregation_phase,
                     FNC_Context_t *function_context,
                     type_1        *input_parameter_1,
                      ...,
                     type_n        *input_parameter_n,
                     result_type   *result,
                     int           *indicator_parameter_1,
                      ...,
                     int           *indicator_parameter_n,
                     int           *indicator_result,
                     char           sqlstate[6],
                     SQL_TEXT       function_name[m],
                     SQL_TEXT       specific_function_name[l],
                     SQL_TEXT       error_message[p] )
{
     ...
}

where:

Parameter … Specifies … Input/Output
FNC_Phase aggregation_phase the current aggregation phase. The aggregation phase determines how the aggregate function processes the data passed in.

FNC_Phase is defined as:

typedef enum {
    AGR_INIT        = 1,
    AGR_DETAIL      = 2,
    AGR_COMBINE     = 3,
    AGR_FINAL       = 4,
    AGR_NODATA      = 5
} FNC_Phase;

If the value of aggregation_phase is …

  • AGR_INIT, then the function must allocate and initialize any intermediate storage for accumulating summary information.
  • AGR_DETAIL, then the function must accumulate the input data into the intermediate storage for the specific group.
  • AGR_COMBINE, then the function must combine all of the intermediate storage areas for each group that is being aggregated.
  • AGR_FINAL, then the function must produce the final aggregate result for the group.
  • AGR_NODATA, then the function must provide a result for a null aggregate set.
In
FNC_Context_t * function_context a pointer to the function context structure, which is used as an intermediate aggregate storage area for groups that the aggregate function operates on.

FNC_Context_t is defined as:

typedef struct FNC_Context_t {
    int         version;
    FNC_flags_t flags;
    void        *interim1;
    int         intrm1_length;
    void        *interim2;
    int         intrm2_length;
    long        group_count;
    long        window_size;
    long        pre_window;
    long        post_window;
} FNC_Context_t;
where:
  • version = the version of the context structure. The current version is 1.
  • interim1 = a pointer to the aggregate storage area that saves intermediate results.
  • intrm1_length = the length of interim1.
  • interim2 = a pointer to an intermediate storage area to combine with the interim1 storage area when the value of aggregation_phase is AGR_COMBINE.
  • intrm2_length = the length of interim2.

FNC_Context_t members not listed above are reserved for future use.

In
type_n * input_parameter_n the input parameters, where n is the number of parameters in the CREATE FUNCTION definition. If n = 0, no input parameters appear. The type is one of the C types in sqltypes_td.h that corresponds to the SQL data type of the input argument.

The maximum number of input parameters is 128.

In
result_type *result the result.

The result pointer points to a data area that is big enough to hold the result that the function returns, as defined by the RETURNS clause in the corresponding CREATE FUNCTION statement.

Out
int * indicator_parameter_n the indicator parameters (for functions where n > 0) corresponding to the input parameters, in the same order.

If the value of the indicator argument is...

  • -1, then the corresponding input argument is null.
  • 0, then the corresponding input argument is a value.
In
int * indicator_result the result indicator parameter corresponding to the result. Out
char sqlstate[6] the success, exception, or warning return. This is a pointer to a six-character C string, where the first five characters are ASCII and the last character is a C null character. The function can set sqlstate to an SQLSTATE exception or warning condition if the function detects an error.

The string is initialized to '00000', which corresponds to a success condition.

Out
SQL_TEXT function_name[m] the function name. This is a pointer to a C string. This is the same name as the function name specified by CREATE FUNCTION function_name.

The function can use this name to build error messages.

The ANSI SQL standard defines the maximum value for m as 128. Teradata Database allows a maximum of 30 characters for function names.

In
SQL_TEXT specific_function_name[l] the specific name of the external function being invoked, when more than one function has the same name. This is a pointer to a C string. This is the same name as the specific name specified by the SPECIFIC clause of the CREATE FUNCTION statement.

If the CREATE FUNCTION statement omits the SPECIFIC clause, this name is the same as the function name specified by CREATE FUNCTION function_name.

The function can use this name to build error messages.

The ANSI SQL standard defines the maximum value for l as 128. Teradata Database allows a maximum of 30 characters for function names.

In
SQL_TEXT error_message[p] the error message text. This is a pointer to a C string where the maximum value for p is 256. Out