Register the UDF with the Netezza Performance Server system

To register a UDF, you use the CREATE FUNCTION command.

When you issue a CREATE FUNCTION command, the database processes the HOST OBJECT and the SPU OBJECT files as the nz user. The nz user must have read access to the object files and read and execute access to every directory in the path from the root to the object file.

For example, to register the sample function customername to the Netezza Performance Server system, start an nzsql session to your database (which is named mydb in this example):
nzsql mydb myuser password
Next, use the CREATE FUNCTION Netezza Performance Server SQL command to register the function:
MYDB.SCHEMA(MYUSER)=> CREATE FUNCTION CustomerName(varchar(64000))
RETURNS int4 LANGUAGE CPP PARAMETER STYLE NPSGENERIC API VERSION 2 
EXTERNAL CLASS NAME 'CCustomerName' 
EXTERNAL HOST OBJECT '/home/nz/udx_files/customername.o_x86' 
EXTERNAL SPU OBJECT '/home/nz/udx_files/customername.o_spu10';

If the command is successful, it returns the message CREATE FUNCTION and adds the function to the current database and schema. It creates the function in the mydb database, and the function is owned by myuser. To create a function, your user account must have Create Function privilege or you must be logged in as the admin user, the owner of the database, or the owner of the schema. For the function name, you can specify a name such as schema.functionname (for example, schema.CustomerName(varchar(64000)) to create the function in a specific schema in the current database. You can specify a database name, but the name must be the same as the current database. Cross-database writes are not supported.

When you register a UDF with the Netezza Performance Server system, the specified object files are copied into the Netezza Performance Server database directories. This allows the functions to be used in queries by all permitted users, and it also ensures that the UDFs are backed up and restored with the user data in the database. If you change the C++ program for any reason (such as adding debug messages or changing the operation of the function), you must recompile the program and rerun the CREATE OR REPLACE FUNCTION command to copy the updated object files into the Netezza Performance Server database.

Note the following characteristics of the CREATE FUNCTION command:

  • If you use the command CREATE FUNCTION, instead of CREATE OR REPLACE FUNCTION, the command fails if a user-defined function with the same name and signature exists in the database, or in the same schema if your system supports multiple schemas.
  • You can create multiple UDFs that use the same name, but they must have different signatures if they are in the same database. The name must meet the character restrictions for a legal Netezza Performance Server SQL keyword or identifier, and it does not need to match or relate to anything defined in the C++ file (that is, the name is not used for binding).
  • The value that you specify for EXTERNAL CLASS NAME must match the class in the C++ file exactly, as this is how the runtime engine creates and calls the UDF object method.
  • The command fails if the DEPENDENCIES argument references the name of a user-defined shared library that is not defined in the current database.
  • For string arguments, use caution in choosing a string size. In general, follow these guidelines for strings:
    • If the string input is naturally bounded, specify a string size that matches the largest string needed. For the customername example, varchar(10) is sufficient.
    • If the string input length can vary widely, use generic size arguments.
    • There can be a performance penalty for specifying a large string when the input passed is a CHAR/NCHAR type and the argument is specified as VARCHAR/NVARCHAR. In this case, the argument is implicitly converted to the variable-sized argument, including all of the trailing spaces.