Register the UDTF

To register a UDTF, 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 parseNames to the system, start an nzsql session to your database (which is named mydb in this example):

nzsql mydb myuser password

Next, use the CREATE FUNCTION Performance Server SQL command to register the UDTF:

MYDB.SCHEMA(MYUSER)=> CREATE FUNCTION parseNames(varchar(ANY))
RETURNS TABLE(product_id VARCHAR(200)) API VERSION 2 LANGUAGE CPP 
PARAMETER STYLE NPSGENERIC EXTERNAL CLASS NAME 'parseNames' 
EXTERNAL HOST OBJECT '/home/nz/udx_files/parseNames.o_x86' 
EXTERNAL SPU OBJECT '/home/nz/udx_files/parseNames.o_spu10';

If the command is successful, it returns the message CREATE FUNCTION. It creates the UDTF in the mydb database, and the UDTF 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.parseNames(varchar(ANY)) 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.