16.20 - Rules for Writing UDFs that Compress Character and Byte Data - 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)
  • The scalar UDF must declare a single BYTE(n), VARBYTE(n), CHAR(n), or VARCHAR(n) input parameter that has a data type that is compatible with the column data type. The return parameter type must be VARBYTE(n). For details on compatible types, see Compatible Types.

    The length of the column must be less than or equal to the length of the input parameter.

    For CHAR and VARCHAR columns, you must use the CHARACTER SET phrase in the parameter declaration to specify a server character set that matches the server character set of the column.

    Recommendation: Declare the input parameter with a data type of VARBYTE(n) or VARCHAR(n). This is best for most situations.

  • When the input cannot be compressed, the UDF can indicate it is unable to compress the column data by setting the length field of the output VARBYTE argument to less than 0.
  • If the column data type is VARCHAR or CHAR and you declare the UDF input parameter as CHAR or VARCHAR, then the UDF must call FNC_GetCharLength to get the actual length of the input string.

    If the UDF input parameter is a CHAR, Teradata Database appends the correct number of pad characters or trims the correct number of pad characters so that the input string has the same number of characters as the column definition.

    If the UDF input parameter is a VARCHAR and the column being compressed is a CHAR column, Teradata Database trims any pad characters.

  • For VARCHAR or VARBYTE columns, Teradata Database invokes the UDF as if the input parameter was defined with the length specified in the column definition.

    Consider the following table definition:

    CREATE TABLE Descriptions
       (d_ID INTEGER
       ,d_Data VARCHAR(2400) COMPRESS USING Compress_Data
                             DECOMPRESS USING Decompress_Data
       );

    You can write a C function that declares a VARCHAR(64000) input parameter and Teradata Database calls the function as if the function declared a VARCHAR(2400) input parameter.