Contract Function | Table Operators | Teradata Vantage - 17.10 - Contract Function - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

You can write a user-defined contract function which determines the output table row format of your table operator. The contract function is invoked at runtime when the table operator is being parsed.

The contract function is similar to a standard scalar UDF. The function takes no input parameters and returns an integer SUCCESS or FAILURE value.

The contract function can access table operator input information using FNC functions and set the return row format using an FNC function. The contract function context is accessible by the table operator.

The contract function can use FNC functions to access the following metadata:
  • Input parameter metadata such as the number of columns, data types and name information.
  • Explicitly defined output row metadata such as the number of columns, data types and name information.
  • The name of the function being called.
  • Metadata associated with the optional Custom clauses.
  • Metadata for the optional HASH BY clause.
  • Metadata for the optional LOCAL ORDER BY clause.
The contract function can use FNC functions to set the following metadata associated with the table operator:
  • Output parameter metadata such as the number of columns, data types and name information.
  • Optional structure that is created at contract time and passed to the table operator at runtime.
  • Optional specification of the row and field format.
  • Optional specification of the HASH BY clause.
  • Optional specification of the LOCAL ORDER BY clause.

For details about the FNC functions that the contract function can use, see Table Operator Interface.

You associate a contract function with a table operator by using the RETURNS TABLE clause of the CREATE FUNCTION statement.

For example, if you have a table operator defined as:

void udaggregation ()
{
     ...
}

And you have a contract function defined as:

void udaggregation_contract (
   INTEGER *result,
   int *indicator_Result,
   char sqlstate[6],
   SQL_TEXT extname[129],
   SQL_TEXT specific_name[129],
   SQL_TEXT error_message[257])
{
     ...
}

Then you associate the contract function with the table operator by specifying the name of the contract function in the RETURNS TABLE clause in the SQL definition of the table operator as follows:

REPLACE FUNCTION udaggregation ()
   RETURNS TABLE VARYING USING FUNCTION udaggregation_contract
   LANGUAGE C
   NO SQL
   PARAMETER STYLE SQLTABLE
   EXTERNAL NAME 'CS!udaggregation!udaggregation.c!F!udaggregation';
If the code for the table operator and the code for the contract function are in separate source files, the CREATE FUNCTION statement must reference both source files.

The maximum length of the contract function name is CHAR(30) CHARACTER SET LATIN. The CREATE FUNCTION statement fails if the contract function name exceeds this limit after the string is converted into LATIN for storage in the Data Dictionary.

Once created, you cannot drop a contract function directly. When the table operator is dropped, the contract function is also dropped. Similarly, you cannot alter a contract function directly. When the table operator is altered to run in a different mode, the contract function is also altered. When the table operator is recompiled, the contract function is also recompiled.

You cannot rename the table operator or the contract function.

For a C code example of a table operator and contract function, see C Table Operator.