UDF in a SQL query

After you register a UDF with your system, you and other permitted users can call the function in the same manner as the Netezza Performance Server SQL built-in functions. To use a UDF, users must have Execute privilege for the FUNCTION object or for the specific UDF.

Note: By default, the admin user account has execute access to all user-defined functions and aggregates. The user account that registered a UDF also has execute access to that UDF. The database owner has privileges to run the UDFs in the database, and for systems that support multiple schemas, the schema owner has privileges to run all UDFs in the schema. Other users can be given privilege to run specific or all UDFs.

For the sample customername function, first create a sample table that contains the data to be processed by the function. For example:

CREATE TABLE customers (a INT, b VARCHAR(200));
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer CBA');
INSERT INTO customers VALUES (4, 'Customer ABC');

Then, you can run the sample customername function, as follows:

MYDB.SCHEMA(MYUSER)=> SELECT * FROM customers WHERE CustomerName(b) = 1;

Sample output follows:

 A | B
---+------------------
 1 | Customer A
 4 | Customer ABC
(2 rows)

You can use fully qualified names to access functions in other databases and schemas. The user who runs the query must have access privileges to the UDX in the other database. For example:

DEV.USER2(USER2)=> SELECT * FROM mydb.schema.customers WHERE
mydb.schema.CustomerName(b) = 1;