17.10 - SQL Definition - 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)

Here is the SQL definition of a color_t distinct UDT and a circle_t structured UDT.

CREATE TYPE color_t
   AS VARCHAR(30)
   FINAL;

CREATE TYPE circle_t
   AS (x INTEGER, y INTEGER, radius INTEGER, color color_t)
   NOT FINAL;

The following CREATE FUNCTION statements install the functions:

CREATE FUNCTION circle_t_ToSql( p1 VARCHAR(80) )
   RETURNS circle_t
   NO SQL
   PARAMETER STYLE TD_GENERAL
   DETERMINISTIC
   LANGUAGE C
   EXTERNAL NAME 'CS!c_tosql!udfsrc/c_tosql.c!F!circle_t_ToSql';

CREATE FUNCTION circle_t_FromSql( p1 circle_t )
   RETURNS VARCHAR(80)
   NO SQL
   PARAMETER STYLE TD_GENERAL
   DETERMINISTIC
   LANGUAGE C
   EXTERNAL NAME 'CS!c_fromsql!udfsrc/c_fromsql.c!F!circle_t_FromSql';

CREATE FUNCTION circle_t_Ordering( p1 circle_t )
   RETURNS FLOAT
   SPECIFIC circle_t_Ordering
   NO SQL
   PARAMETER STYLE SQL
   DETERMINISTIC
   LANGUAGE C
   EXTERNAL NAME 'CS!c_order!udfsrc/c_order.c!F!circle_t_Ordering';

The following statement registers the circle_t_ToSql and circle_t_FromSql functions as transform routines for the circle_t UDT:

CREATE TRANSFORM FOR circle_t
   circle_t_IO (TO SQL WITH SPECIFIC FUNCTION circle_t_ToSql,
      FROM SQL WITH SPECIFIC FUNCTION circle_t_FromSql);

The following statement registers the circle_t_Ordering function as an ordering routine for the circle_t UDT:

CREATE ORDERING FOR circle_t
   ORDER FULL BY MAP WITH SPECIFIC FUNCTION circle_t_Ordering;