Cross-database access to UDXs

When you create a UDX using the CREATE [OR REPLACE] [FUNCTION | AGGREGATE | LIBRARY] command, the command adds the UDX to the database and schema to which you are connected. You can access the UDX while connected to the database where the UDX is defined, and you can also access UDXs in other schemas in the same database, or in other databases using the following methods:

Typically, most users are logged in to the database that contains the UDX that they plan to run. However, the system allows for cross-database access of UDXs by using two methods:

Using fully-qualified object names when referencing a UDX object that resides within a different database, for example:
```
MYDB.SCHEMA(MYUSER)=> SELECT * FROM customers WHERE
OTHERDB.SCHEMA.CustomerName(b) = 1;
```
Using a schema.object name when referencing a UDX that resides within a different schema of the same database, for example:
```
MYDB.SCHEMA(MYUSER)=> SELECT * FROM customers WHERE
SCH_TWO.CustomerName(b) = 1;
```
Using the PATH SQL session variable to specify the databases and/or schemas to search to find a UDX name that is not fully qualified. To use the PATH session variable, you enter a command similar to the following at the nzsql command prompt:
```
MYDB.SCHEMA(MYUSER)=> SET PATH = <elem> {, <elem>];
```
Using the PATH SQL session variable to specify the databases to search to find the UDX. To use the PATH session variable, you enter a command similar to the following at the nzsql command prompt:
```
MYDB.SCHEMA(MYUSER)=> SET PATH = <elem> [, <elem>];
```
The <elem> value can be a database, a schema, name or the variables CURRENT_CATALOG, CURRENT_USER, CURRENT_SCHEMA or CURRENT_PATH. For example:
```
MYDB.SCHEMA(MYUSER)=> SET PATH = mydb, nzdb, customer;
SET VARIABLE
```
To display the PATH value, use the following command:
```
MYDB.SCHEMA(MYUSER)=> SELECT CURRENT_PATH;
   CURRENT_PATH
------------------
MYDB,NZDB,CUSTOMER
(1 row)
```
The Netezza Performance Server system uses this variable during the lookup of any “unqualified” UDXs. It searches the current database if PATH is not set; otherwise it searches the databases and schemas that are specified in PATH, in the order that they are specified. The Netezza Performance Server system uses the first match (or potential match) it finds, even if a better match might exist in a subsequent database. A poorer match is one that might require implicit casting of arguments or that causes an error because of multiple potential matches. PATH searches databases, not schemas, as there is no schema support for this capability. Also, the PATH session variable supports only UDFs, UDAs, and stored procedures (which are described in the IBM® Netezza® Stored Procedures Developer's Guide). Other object types are not supported.