16.10 - Creating UDFs with Teradata PT - Parallel Transporter

Teradata Parallel Transporter Reference

Product
Parallel Transporter
Release Number
16.10
Published
July 2017
Content Type
Programming Reference
Publication ID
B035-2436-077K
Language
English (United States)

Teradata PT provides an option to Create an UDF or an XSP in the Teradata Database by using the DDL operator.

Teradata PT supports the creation of both Client Side and Server Side UDFs/XSPs

The following apply statement creates a server side UDF named 'yourudf' by using the C file, /root/yourCudf.c residing in the /root directory of the database machine:

 APPLY
  (
'
  CREATE FUNCTION yourudf(
  parameter_1 CHAR(1))
  RETURNS CHAR
  LANGUAGE C
  NO SQL
  EXTERNAL NAME ''SS!yourudf!/root/yourCudf.c''
  PARAMETER STYLE SQL;
'
  )
  TO OPERATOR ( $DDL );

If the above C file exists on the client side in the current working directory, the above example can be modified to support the client side UDF as follows:

EXTERNAL NAME ''CS!yourudf!yourCudf.c''

The above example can also be written as:

EXTERNAL NAME ''CS!yourudf!yourCudf''

In this Case, the Teradata PT first looks for a file named 'yourCudf' in the current directory and if not found, it also looks for the filename with a .c extension 'yourCudf.c'.

The C, C++, or Java files can reside on the server or the client. The server calls for the transfer of client-resident files as needed for input requirements for the CREATE/REPLACE FUNCTION request. Source files must be encoded as ASCII (workstation) or EBCDIC (mainframe), regardless of the current session character set. Note, the same transfer protocol is employed when using client-resident files to create external stored procedures (XSPs).

The full specification of the syntax, format, and rules for both creating and invoking UDFs and XSPs is beyond the scope of this document. See SQL External Routine Programming (B035-1147) for details on how to create and invoke user-defined functions.