16.20 - Table Operator Data Structures - 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)

This section describes the data structures used by the FNC functions provided to table operator and contract function writers. These data structures are used to store metadata associated with the entire table operator or a specific stream and to pass information to and from the FNC functions.

For details about these data structures, see the sqltypes_td.h header file.

FNC_TblOpColumnDef_t
Column definitions are associated with streams. This metadata is represented in the data structure FNC_TblOpColumnDef_t as a sequence of column types, the length of this sequence, and the number of columns. The size of this data structure is variable.
parm_tx
Column types are represented by the data structure parm_tx.
The following lists some of the information included in this structure. See sqltypes_td.h for the complete definition.
  • The data type of the attribute.
  • The name of the column.
  • The name of the UDT.
  • The JSON storage format.
  • The character set.
  • The granularity of the period type.
  • The maximum size of the fixed-length fields.
  • The length in CHAR, VARCHAR, or BYTE types.
  • The range for interval types.
  • The precision for TIME/TIMESTAMP types.
  • n and m in DECIMAL(n, m)
Column names and UDT names are represented as a sequence of up to 128 characters in UNICODE, LATIN, or Kanji.
dtype_en
The dtype_en enum defines all the available data types which may be passed in as a column to a table operator.
The following lists some sample values. See sqltypes_td.h for the complete definition.
  • CHAR_DT=1
  • VARBYTE_DT=4
  • SMALLINT_DT=8
  • REAL_DT=10
  • DECIMAL8_DT=14
  • DATE_DT=15
  • INTERVAL_MONTH_DT=20
  • TIMESTAMP_WTZ_DT=32
  • CLOB_REFERENCE_DT=34
  • UDT_DT = 35
  • NUMBER_DT=38
  • PERIOD_DT = 39
  • JSON_DT = 40
  • DATASET_AVRO_DT = 41
  • ST_GEOMETRY_DT = 42
  • MBR_DT = 43
  • MBB_DT = 44
  • ARRAY_DT = 45
  • XML_DT = 46
  • DATASET_CSV_DT=47
json_storage_en
The json_storage_en enum defines the storage format for a JSON type.
The following lists some valid values. See sqltypes_td.h for the complete definition.
  • JSON_INVALID_EN=-1
  • JSON_TEXT_EN=0
  • JSON_BSON_EN=1
  • JSON_UBJSON_EN=2
period_en
The granularity of Period types is defined by the period_en enum.
The following lists some valid values. See sqltypes_td.h for the complete definition.
  • NOT_PERIOD = 0
  • PERIOD_DATE = 1
  • PERIOD_TIME = 2
  • PERIOD_TIME_WTZ = 3
  • PERIOD_TIMESTAMP = 4,
  • PERIOD_TIMESTAMP_WTZ = 5
FNC_TblOpHandle_t
Handles are structures used to access streams and can be used to pass information to and from FNC functions. Functions that access metadata associated with streams require handles as one of their input parameters. However, functions that access metadata associated with the operator do not require a handle.
Handles are represented by the data structure FNC_TblOpHandle_t.
The following lists some of the information included in this structure. See sqltypes_td.h for the complete definition.
  • The stream number.
  • A pointer to information about the current row in the stream.

    If attributes in the current row can be directly accessed using information stored in this field, then the Options field is set to 0.

  • The direction of the stream (input or output).
  • Options (whether attributes in a row can be accessed directly or not).
  • State (whether the stream has been opened or closed).
Stream_Direction_en
The direction of a stream is defined by the Stream_Direction_en enum.
Valid values include the following. See sqltypes_td.h for the complete definition.
  • ISOUTPUT = 'W'
  • ISINPUT = 'R'
Stream_State_en
The state of a stream is defined by the Stream_State_en enum.
Valid values include the following. See sqltypes_td.h for the complete definition.
  • ISINIT = 1
  • ISOPEN = 2
  • ISCLOSE = 3
current_row_t
Information about the current row in a stream is stored in the data structure current_row_t. This includes locations of individual attributes (columnptr) where you can have direct access to them.
The following lists some of the information included in this structure. See sqltypes_td.h for the complete definition.
  • The length of the row data in bytes.
  • The record type of the body (matches parcel flavors).
  • The location of the indicators. A pointer to NULL indicators for fields in the current record.
  • The location of the column data.
  • Lengths for column data.
  • A pointer to the raw row record body.
FNC_Names_t and FNC_Names_Ord_t
FNC functions that handle HASH BY and LOCAL ORDER BY metadata have sequences of column names as input or output parameters. The data structure FNC_Names_t stores these sequences and FNC_Names_Ord_t stores sequences of names with an order (ascending or descending).
See sqltypes_td.h for the definitions of these structures.
Key_info_t
Functions that retrieve values associated with a key in a custom clause use the data structure Key_info_t to store these values.
The following lists some of the information included in this structure. See sqltypes_td.h for the complete definition.
  • The number of values.
  • The total size of the values in bytes.
  • The size of the key.
  • The data type of the values.
  • An array of values.
  • The key.
UDT_Baseinfo_t
The UDT_BaseInfo_t structure provides metadata about a UDT/CDT input or output column. This structure is used only for table operators.
The following lists some of the information included in this structure. See sqltypes_td.h for the complete definition.
  • The type of the UDT.
  • If it is an ARRAY UDT, the dimensions of the array, the number of elements, and information about each element.
  • Information about the base type of the UDT or CDT, such as the character set, the data type, and so forth.
  • For JSON types, the JSON storage format.
  • For DATASET types, the DATASET storage format.
  • The number of attributes for a structured UDT.
  • The name of the UDT.
  • The predefined data type which the UDT is mapped from and to for transforms.
The information about the base type of the UDT or CDT is specified in the base_* fields of the UDT_BaseInfo_t structure. The following table shows the base data type mapping for a UDT or CDT.
SQL UDT or CDT Attribute/Element/Base Type Code
Distinct UDT

Predefined data type the UDT is based on.

For example, if you have CREATE TYPE myint as INTEGER FINAL;

Then the Base type code is INTEGER_DT.

Structured UDT

Since a structured UDT may have many attributes and may be nested, the attributes are not saved in the metadata but are accessed by calling the FNC_TblOpGetStructuredAttributeInfo function.

Period types

Date/Time base type of BEGIN and END elements. Valid values are DATE_DT, TIME_DT, TIMESTAMP_DT, TIME_WTZ_DT, TIMESTAMP_WTZ_DT.

XML

CLOB_REFERENCE_DT

Geospatial – ST_Geometry

CLOB_REFERENCE_DT

Geospatial – MBR

VARCHAR_DT

Geospatial – MBB

VARCHAR_DT

ARRAY/VARRAY

The data type of the element of the array.

For example, if you have CREATE TYPE intary as INTEGER ARRAY[10];

Then the type code of the element is INTEGER_DT.

JSON

CLOB_REFERENCE_DT

BSON

BLOB_REFERENCE_DT or CLOB_REFERENCE_DT

DATASET

BLOB_REFERENCE_DT or CLOB_REFERENCE_DT

Note that the base_* fields of the UDT_BaseInfo_t structure are not filled in for structured UDTs. Since structured UDTs may have many attributes and may also contain an arbitrary level of nesting, metadata about the attributes of a structured UDT is retrieved using the FNC_TblOpGetStructuredAttributeInfo function. FNC_TblOpGetStructuredAttributeInfo returns an array of attribute_info_t structures corresponding to all of the attributes in the structured UDT.
attribute_info_t and attribute_info_eon_t
The attribute_info_t and attribute_info_eon_t data structures describe an attribute of a structured UDT.
The following lists some of the information included in these structures. See sqltypes_td.h for the complete definition.
  • The attribute positional index.
  • The attribute data type.
  • The attribute name.
  • The UDT type indicator.
  • The UDT type name.
  • For JSON types, the JSON storage format.
  • The maximum length for this data type.
  • The LOB length for LOB data types.
  • The character set.
SMALLINT udt_indicator
Indicates the type of UDT or CDT.
The following lists some sample values. See sqltypes_td.h for the complete definition.
  • 0=Not a UDT or CDT
  • 1=Array
  • 2=Structured
  • 3=JSON ENCODE AS TEXT
  • 4=Distinct
  • 5=Period types
  • 6=XML
  • 7=ST_Geometry
  • 8=MBR
  • 9=MBB
  • 10=DATASET STORAGE FORMAT AVRO
  • 11=DATASET STORAGE FORMAT CSV