CREATE/REPLACE FUNCTION Examples | Teradata Vantage - Example: Creating a UDF C Function - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

This request creates a user-defined C function on a Linux system. The UDF looks for the C source code file named find_text.c on the client system in the current directory of the logged on user. The function name must be find_text, which is its entry point name.

The example creates a function that searches for a user-specified test pattern within a user-specified character string. The maximum length search string and pattern string the function allows is 500 bytes. The result is a character value of either T or F, representing true (the test pattern is found in the searched string) or false (the test pattern is not found in the searched string).

    CREATE FUNCTION find_text (
     searched_string VARCHAR(500), 
     pattern         VARCHAR(500))
    RETURNS CHARACTER
    LANGUAGE C
    NO SQL
    EXTERNAL
    PARAMETER STYLE SQL;
    
    *** Function has been created. 
     *** Warning: 5607 Check output for possible warnings encountered in compiling XSP/UDF.
     *** Total elapsed time was 5 seconds.
    
    Check output for possible compilation warnings.
    ------------------------------------------------------------
     /usr/bin/cc  -Xc -I /tpasw/etc -c -o find_text.o find_text.c
    Teradata High Performance C Compiler R3.0c
    (c) Copyright 1994-98, Teradata Corporation
    (c) Copyright 1987-98, MetaWare Incorporated
     /usr/bin/cc  -Xc -I /tpasw/etc -c -o pre_another.o pre_another.c
    Teradata High Performance C Compiler R3.0c
    (c) Copyright 1994-98, Teradata Corporation
    (c) Copyright 1987-98, MetaWare Incorporated
     /usr/bin/cc -G  -Xc -I /tpasw/etc -o libudf_03ee_11.so 
    find_text.o pre_find_text.o pre_really_long_function_name.o
    long_name.o pre_udf_scalar_substr.o substr.o pre_Find_Text.o
    pattern.o pre_char2hexint.o char2hexint.o   -ludf -lmw -lm

The following SELECT request that searches for a specific text description in documents having a date not less than a specified age shows how the function find_text might be used.

    USING (age DATE, look_for VARCHAR(500))
    SELECT doc_number, text
    FROM documents
    WHERE (:age <= doc_copyright
    AND   (find_text(text, :look_for) = 'T');