DROP AGGREGATE

Use the DROP AGGREGATE command to remove an existing user-defined aggregate from a database. When you drop an aggregate, the aggregate object files are also removed from the user code object repository.

Synopsis

Syntax for dropping a user-defined aggregate:
DROP AGGREGATE aggregate_name(argument_types)

Inputs

The DROP AGGREGATE command takes the following inputs:
Table 1. DROP AGGREGATE inputs
Input Description
aggregate_name Specifies the name of an existing user-defined aggregate that you want to drop. For systems that support multiple schemas, you can specify a name in the format schema.aggregate to drop an aggregate in a different schema of the current database. You cannot drop an aggregate in a different database.
argument_types Specifies a list of fully specified argument data types to uniquely identify the aggregate. All Netezza Performance Server data types are supported. Strings must include either a size or ANY for generic sizes. NUMERIC types must include precision and scale or ANY for generic sizes. You can also specify the VARARGS value to drop a variable argument aggregate.

Outputs

The DROP AGGREGATE command has the following outputs:
Table 2. DROP AGGREGATE outputs
Output Description
DROP AGGREGATE The message that the system returns if the command is successful.
ERROR: Name: No such aggregate The message that the system returns if the specified aggregate does not exist in the current database.
Error: DropAggregate: existing UDX name(argument_types) differs in size of string/numeric arguments This error indicates that a UDX exists with the name but has different sizes that are specified for string or numeric arguments. To drop the aggregate, make sure that you specify the exact argument type list with correct sizes.
ERROR: Can't delete aggregate name - view viewName depends on it The message that the system returns if a UDA is referenced in a view. You cannot drop the UDA until the dependency from the view is resolved.

Description

You cannot drop a user-defined aggregate that is in use in an active query. After the active query transaction completes, the Netezza Performance Server system processes the DROP AGGREGATE command to drop the aggregate. The aggregate must be defined in the current database.

You cannot drop a UDA that is referenced by an existing view. For more information about resolving dependencies to UDAs that you want to drop, see Dependency checks before you drop UDXs.

Privileges required

To drop a UDA, you must meet one of the following criteria:
  • You must have the Drop privilege on the AGGREGATE object.
  • You must have the Drop privilege on the specific UDA object.
  • You must own the UDA.
  • You must be the database admin user, own the current database, or own the current schema on systems that supports multiple schemas.

Common tasks

Use the DROP AGGREGATE command to drop an existing aggregate from a database.

Usage

The following is sample usage.
  • To drop a sample aggregate named mycalc, enter: 
    MYDB.SCHEMA(MYUSER)=> DROP AGGREGATE mycalc(int4);