Drop Statement

The objects created can be removed provided the authorization permissions are set.

drop_aggregate:
     DROP AGGREGATE [ FUNCTION ] [ IF EXISTS ] qname [ arg_types_list ] [ drop_action ]
   | DROP ALL AGGREGATE [ FUNCTION ] qname [ drop_action ]

drop_filter_function:
     DROP FILTER [ FUNCTION ] [ IF EXISTS ] qname [ arg_types_list ] [ drop_action ]
   | DROP ALL FILTER [ FUNCTION ] qname [ drop_action ]

drop_function:
     DROP FUNCTION [ IF EXISTS ] qname [ arg_types_list ] [ drop_action ]
   | DROP ALL FUNCTION qname [ drop_action ]

drop_index:
     DROP INDEX qname

drop_loader_function:
     DROP LOADER [ FUNCTION ] [ IF EXISTS ] qname [ arg_types_list ] [ drop_action ]
   | DROP ALL LOADER [ FUNCTION ] qname [ drop_action ]

drop_procedure:
     DROP PROCEDURE [ IF EXISTS ] qname [ arg_types_list ] [ drop_action ]
   | DROP ALL PROCEDURE qname [ drop_action ]

drop_role:
     DROP ROLE role_name

drop_schema:
     DROP SCHEMA [ IF EXISTS ] schema_name [ drop_action ]

drop_sequence:
     DROP SEQUENCE qname

drop_table:
     DROP TABLE [ IF EXISTS ] qname [ drop_action ]

drop_trigger:
     DROP TRIGGER [ IF EXISTS ] qname

drop_type:
     DROP TYPE qname [ drop_action ]

drop_user:
     DROP USER user_name

drop_view:
     DROP VIEW [ IF EXISTS ] qname [ drop_action ]

drop_window:
     DROP WINDOW [ FUNCTION ] [ IF EXISTS ] qname [ arg_types_list ] [ drop_action ]
   | DROP ALL WINDOW [ FUNCTION ] qname [ drop_action ]

qname:
     [ schema_name '.' ] object_name

arg_types_list:
     '(' [ data_type [',' data_type [...] ] ] ')'

drop_action:
     RESTRICT | CASCADE

The default drop action behavior is RESTRICT. So when an object (e.g. a table) is referenced from another object (e.g. a view) the drop statement will raise an error stating the object can not be dropped due to dependencies.

Use drop action CASCADE to specify to also drop any referencing objects cascading. Be careful with specifying CASCADE.

To find out which dependencies exist for an object query the system view: sys.dependencies_vw.

If you do not specify the function argument types list of the aggregate, filter, function, loader, procedure or window the DROP statement will successfully execute if there is only one function with this name, if not the statement is aborted.
The DROP ALL variants are used to drop all the overloaded functions with the same name. Be careful as you may drop more functions than anticipated.

Examples

DROP TABLE IF EXISTS "projectM"."MonthlyStats";

DROP PROCEDURE "projectM".proc7(int, varchar(30), string, date);

DROP ALL FUNCTION "projectM".udf8 CASCADE;

DROP VIEW last_orders_vw RESTRICT;

DROP VIEW IF EXISTS "projectM"."MonthlyStatView" CASCADE;