Cross-database access to stored procedures
When you create a stored procedure by using the CREATE [OR REPLACE] PROCEDURE command, the command adds the procedure to the database and schema to which you are connected. You can access the procedure while connected to the database where the stored procedure is defined, and you can also run procedures in other schemas in the same database, or in other databases using the following methods:
- Using fully-qualified object names when calling a procedure object
that resides within a different database, for example:
MYDB.SCHEMA(ADMIN)=> EXEC OTHERDB.SCHEMA.UPDATEACCT();
- Using a schema.object name when calling a procedure object that
resides within a different schema of the same database, for example:
MYDB.SCHEMA(ADMIN)=> EXEC SCH_TWO.UPDATEACCT();
- Using the PATH SQL session variable to specify the databases and/or
schemas to search to find a procedure name that is not fully qualified.
To use the PATH session variable, you enter a command similar to the
following:
MYDB.SCHEMA(ADMIN)=> SET PATH = <elem> [, <elem>];
The <elem> value can be a database, a schema, or the variables CURRENT_CATALOG, CURRENT_USER, CURRENT_SCHEMA, or CURRENT_PATH. Anything you specify as <elem> must resolve to a database name.
For example:
MYDB.SCHEMA(ADMIN)=> SET PATH = mydb, nzdb, customer; SET VARIABLE
To display the PATH value, use the following command:
MYDB.SCHEMA(ADMIN)=> SELECT CURRENT_PATH; CURRENT_PATH ------------------ MYDB,NZDB,CUSTOMER (1 row)
The Netezza Performance Server system uses the PATH variable during the lookup of any unqualified procedures. It searches the current database if PATH is not set; otherwise it searches the databases and schemas specified in PATH, in the order that they are specified. The Netezza Performance Server system uses the first match that 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 due to multiple potential matches. Also, the Performance Server system uses the PATH session variable to find only stored procedures, user-defined functions (UDFs), and user-defined aggregates (UDAs). Other object types such as tables, sequences, and so on, are not supported.
If a stored procedure is invoked from a different database, unqualified objects bind to objects in the invoking database, not in the database where the stored procedure is defined. Unqualified stored procedures, UDFs, and UDAs are exceptions to this rule because the Netezza Performance Server system first uses the PATH variable to search for those three object types before it searches within the invoking database.
If you plan to invoke cross-database calls to stored procedures that access data in other databases, make sure that the stored procedure uses fully qualified object names to refer to those data objects.
Write operations do not work across databases. For any procedures which contain insert, update, delete, create, truncate, alter, or drop commands, make sure that you are connected to the database where the stored procedure is defined.