Dependency checks before you drop UDXs

Dependencies on UDXs are tracked. That is, if a table, view, or other UDX references a UDX that you want to drop, you cannot drop it until the dependency is removed.

The dependency check prevents any problems with future queries. You can view the dependency by using the _v_depend view. An example follows, but reference object information and other details have been omitted from the sample output for brevity:

SELECT * FROM _v_depend;
REFERENCING                  | REFERENCED                   | ...| SCHEMA | SCHEMAID
-----------------------------+------------------------------+----+--------+--------
table PROD..CUSTOMERS col(1) | function FILEUPDATE(INTEGER) |    | TEST1  | 581383
view TOTAL_VW                | aggregate MYSUM(INTEGER)     |    | TEST1  | 581383
function MYFUNC(INTEGER)     | library MYMATHLIB            |    | MATH2  | 581356
(3 rows)

For example, if you attempt to drop a UDF named fileupdate that is used in a table named CUSTOMERS, an error similar to the following is returned:

DEV(USER1)=> DROP FUNCTION fileupdate(int4);
ERROR:  Can't delete function FILEUPDATE - table CUSTOMERS (col 1) 
depends on it

The error reports the table and specific column that refers to the function that you wanted to drop. For objects in different databases and schemas, the error message provides a fully qualified name to identify the object.

Similarly, if you try to drop a UDX that is used in a view, the command returns an error. For example, if you attempt to drop a UDA named mysum that is used in a view named TOTAL_VW, an error similar to the following is returned:

DEV(MYUSER)=> DROP AGGREGATE mysum(int4);
ERROR:  Can't delete aggregate MYSUM - view TOTAL_VW depends on it

To resolve these error messages and drop the UDX, you must change the default value of each table row that references the UDFs by modifying the default value clause by using the ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT } commands. For views, you need to use the CREATE OR REPLACE VIEW command to remove the UDX from the view definition.

If you try to drop a user-defined shared library that is a dependency of any existing UDX, you must resolve those dependencies before you can drop the library. For example:

DEV(MYUSER)=> DROP LIBRARY mymathlib;
ERROR:  Can't delete library mymathlib - function MYFUNC(integer) 
depends on it

If you try to drop a database that contains objects that are referenced by objects in other databases, the DROP DATABASE command displays errors and exits. This check and error can also occur if you attempt to drop a schema on a system that supports multiple schemas. The error messages display up to 5 object dependencies, plus the total number of dependencies which must be resolved. You must resolve all the dependency issues before you can drop the database or schema.