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;
Result:
*** 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');