Archives of the TeradataForum

Message Posted: Wed, 03 Dec 2003 @ 10:24:14 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

There was a short lived thread in November about UDF's ( user defined functions ) coming out in 5.1. With the release date of 5.1 fast approaching (and the manuals magically appearing on the NCR website today -- see the links below) I thought I would open up the discussion of what 5.1 UDFs look like, how to code them, install them, and what can they do.

Please feel free to post question about How UDF's work or where they can be used here, if that isn't against this Boards policy.

I am including an example UDF below. I actually had posted my version of this function and then I realized that would only lead to questions about

why I put or don't put {} on the line with the IF or the ELSE

why not use atoi()

what about hexadecimal numbers

I don't want this to degrade to a programming technique discussion. Please

DO NOT DO NOT DO NOT

DO NOT post your 'elegant' solution to this UDF here. That isn't what this post is about.

-----------------------------------------------------------

We support 2 types of functions.

Scalar

Aggregate

We will support Moving Aggregates in a future release.

Each of these operate on one row at a time, but the aggregate provide carry over space from one row to the next.

Functions which accept full tables as input or return full tables as output will be addressed in a future release.

These are written in C and as such can do anything you can do from the C run time library. C Runtime calls like FOPEN, FCLOSE, FREAD, FWRITE although not expressly forbidden are currently unsupported.

If you use these unsupported calls in your functions and your box crashes or HANGS you are on you own.

Basically any C runtime service that interacts with the OS is unsupported.

We have provided a Wrapper function around MALLOC and FREE (if you include our header file) which should prevent Memory leaks in your UDF.

Again if you circumvent our wrapper functions and call the real MALLOC and FREE you are on you own.

There is a high level overview available at (click Save after the window opens up)

www.teradata.com...

Here are the manuals... I encourage people to download them and read through it. ( you might want to rename these documents to names that make sense as you download them )

the 5.1 UDF programmers guide is available at

www.info.ncr.com...

The new 5.1 SQL Reference : Data Definition Language Manual (for the new Create/replace function syntax) is available at

www.info.ncr.com...

the new 5.1 Database Administration manual

www.info.ncr.com...

---------------------------------------------

Here is my sample UDF for ISINTEGER which operates on VARCHAR data.

The reason VARCHAR was chosen was because internally the database server will automatically convert CHAR(x) data to VARCHAR data before pass it into the function.

If I had chosen to parameters to be CHAR(X), and a someone tried to call it with a VARCHAR field

*** Failure 5589 Function 'isinteger' does not exist.

would be returned even though it does exist, but not for this particular data type because there is no implicit conversion from VARCHAR to CHAR inside the database.

I should point out that the names of the parameters to my function are arbitrary.

The Types of the parameters however must match the CREATE/REPLACE FUNCTION call. In my case my function accepts 1 VARCHAR(64000) and return 1 INTEGER.

you first put your code in a file called...

isinteger.c

The file name should (must) match the name of the SPECIFIC entry. There are ways around this but it is just simpler to make them match.

If you don't use the SPECIFIC line than you should (must) match the Function name. SPECIFIC allows you to OVERLOAD a UDF.

See the 5.1 UDF Programmers guide for an example of overloading.

-----------------begin isinteger.c  ----------------------------

#define SQL_TEXT Latin_Text
#include "sqltypes_td.h"
#include 
#define IsNull -1
#define IsNotNull 0

void isinteger( VARCHAR_LATIN *inputString,
                        int *result,
                        int *inputStringIsNull,
                        int *ResultIsNull,
                        char sqlstate[6],
                        SQL_TEXT extname[129],
                        SQL_TEXT specific_name[129],
                        SQL_TEXT error_message[257] )
{
    char *ch;

    *error_message = '\0';
    *ResultIsNull = 0;

     /* assume a  FALSE return */
     *result = 0;

    /* Return FALSE  on Null Input */
    if ((*inputStringIsNull) == IsNull)    return;

    /*  Code deleted.   I didn't want the discussion to be
        about my programming habits or a more efficient
        way to code this function.  Most programmers can figure
        out how to parse a STRING to see if it is contains only
        digits ( and maybe a SIGN ) while ignoring white space.*/

    /* Also please don't post your solution here.   Again this
       discussion should be about the functionality of UDF's as
       a new feature not who can code this particular function
       the best.
     */

      *result = /* result of above code */

    return;
}

------------------  End  isinteger.c --------------------------

Then from the TUF 7.1 version of BTEQ started from the same directory you source file resides in...

you logon to your user which has been granted all the correct

permissions to ...

create, delete and execute a function

then enter....

Replace FUNCTION isinteger
(myval VARCHAR(64000))
RETURNS INTEGER
SPECIFIC isinteger
LANGUAGE C
NO SQL
DETERMINISTIC
PARAMETER STYLE SQL
EXTERNAL ;

this actually causes the Server read the isinteger.c off the client (VIA the users CLI connection so there isn't a Security hole here. If the user doesn't have access to the file neither will CLI running on their behalf.)

If you don't have the TUF 7.1 version of BTEQ, or you are using an ODBC based application (like Teradata SQL Assistant), you must First copy your source file(s) down to the Lowest numbered TPA node (the PDN) in the directory before issuing the above statement. ODBC as a protocol doesn't allow the server to READ files off the client machine.

For MPRAS:

/Teradata/tdbs_udf/usr

For WINDOWS:

x:\Program Files\NCR\TDAT\tdconfig\Teradata\tdbs_udf\usr
( where 'x' is the drive you have Teradata installed )

If you copy your source down to the TPA NODE then the

EXTERNAL

line of the CREATE/REPLACE FUNCTION gets a little more complicated.

Please refer the syntax diagram and discussion of EXTERNAL in the Data Definition Manual under

CREATE/REPLACE FUNCTION

as it is too broad to discuss here.

---------------------------------------------

By choosing

PARAMETER STYLE SQL

I have allowed my UDF to accept NULL INPUT and also allow NULL OUTPUT...Although, in this implementation I will never return NULL even though I am allowed to.

As you can see from the prototype of the function there is an additional ...ISNULL parameter passed in for each real parameter which can (MUST) be interrogated to determine if the pointer is valid before using it.

In my case I decided to return 'FALSE' (NULL is NOT an INTEGER) if the input is NULL.

If you choose

PARAMETER STYLE TD_GENERAL

then NULLs are not allowed and the prototype would look like

void isinteger( VARCHAR_LATIN *inputString,
                        int *result,
                        char sqlstate[6],
              )

If your function is ever called with a NULL you will get

7699 NULL input argument for UDF dbname.udfname.

unless you specify

RETURNS NULL ON NULL INPUT SQL

on the CREATE/REPLACE function, in which case you function is never called and NULL is automatically returned to the caller.

	<Prev	Next>		<<First	<Prev	Next>	Last>>