The CREATE TRANSFORM statement associates a transform group with a UDT. You do not need to create a transform group for any Period or Geospatial data types.
There must be a transform group for each UDT you develop. Note that the system defines a transform group for you when you create a new distinct UDT by default, but you must explicitly define a transform group for all new structured UDTs.
A UDT must have its transform functionality completely defined to be used as the column type of any table, otherwise an attempt to create or alter a table to include a column having the UDT type aborts and the system returns an error to the requestor.
- From a particular database UDT to the server form of a particular predefined client data type.
A routine that performs this transform is referred to as a fromsql transform routine.
- From a predefined client data type to a particular UDT in the database.
A routine that performs this transform is referred to as a tosql transform routine.
Such a pair of transform mappings is called a transform group. A transform group defines a single tosql/fromsql pair. There must be one, and only one, transform group per UDT.
You can perform a valid CREATE TRANSFORM request that associates only a tosql routine or only a fromsql routine with a UDT, but you must define complete transform functionality for a UDT before you can specify it as the column data type of any table. If you do not, the system aborts the ALTER TABLE or CREATE TABLE request and returns an error to the requestor.
| FOR this type of UDT … | THE transform group functionality … |
|---|---|
| one-dimensional and multidimensional ARRAY and VARRAY | is generated automatically by the database. The format of the transformed output in a VARCHAR string is a string of each array element value, called the transformed value string, separated by a comma and delimited by parentheses as shown. Assuming the array has n elements: (<element_1>,<element_2>, … <element_n>) The following rules apply:
|
|
|
| distinct | is generated automatically by the database. If the system-generated transform group semantics is adequate, you need not define explicit tosql and fromsql transform functionality using the CREATE TRANSFORM statement. If your applications require different or richer transform semantics, then you can specify explicit transform group functionality using CREATE TRANSFORM. |
| structured | must be defined explicitly using the CREATE TRANSFORM statement. |
The system invokes transforms implicitly whenever UDT data is imported to or exported from the database.
The system does not permit you to specify a transform group for the UDT parameter set of external routines. Instead, when a process invokes an external routine, the system passes a UDT handle to the external routine argument rather than passing the UDT value to it directly. The external routine can use this UDT handle to get or set the value of a UDT argument by means of a set of library functions provided by Teradata. For more information, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.
The predefined type that is specified in the transform group routines of a UDT is referred to as the external type of the UDT. The external type is always in Teradata server format, meaning that the normal client format-to-database format transformations continue to take place in addition to UDT-specific tosql and fromsql operations.
- The system transforms the external type of the UDT from its client format to its database format.
- The system performs the tosql routine.
- The system performs the fromsql routine to transform the UDT to its external type database form.
- The system transforms the external type from its database format to its client format.