DROP AGGREGATE

Syntax

DROP AGGREGATE [IF EXISTS] {function_name | database_name.function_name} [FORCE]

Remarks

• Only one function can be removed using the DROP AGGREGATE command.

• If DROP AGGREGATE is executed for a function that does not exist, the following error will occur:

  SQL

  Copy

  DROP AGGREGATE myaggregate;

  ERROR 2219 (HY000): Function 'db1.myaggregate' doesn't exist

  However, if the IF EXISTS clause is used and the function does not exist, no error will occur:

  SQL

  Copy

  DROP AGGREGATE IF EXISTS myaggregate;

  Query OK, 0 rows affected (0.00 sec)

• Specify the FORCE option to remove an aggregate function (UDAF) created by an Extension. You must have the DROP EXTENSION permission to use this option.

• This command causes implicit commits. Refer to COMMIT for more information.

Examples

Dropping a Function in the Current Database

The following example removes an existing function from the current database.

SHOW AGGREGATES;

+-------------------+
| Aggregates_in_db1 |
+-------------------+
| multiply_hundred  |
| myaggregate       |
+-------------------+
2 rows in set (0.00 sec)

DROP AGGREGATE myaggregate;

Query OK, 0 rows affected (0.05 sec)

SHOW AGGREGATES;

+-------------------+
| Aggregates_in_db1 |
+-------------------+
| multiply_hundred  |
+-------------------+
1 row in set (0.00 sec)

Dropping a Function in Another Database

The following example removes an existing function while connected to another database in your SingleStore cluster.

USE db1;

SHOW AGGREGATES;

+-------------------+
| Aggregates_in_db1 |
+-------------------+
| multiply_hundred  |
| myaggregate       |
+-------------------+
2 rows in set (0.00 sec)

USE db2;

DROP AGGREGATE db1.myaggregate;

Query OK, 0 rows affected (0.05 sec)

USE db1;

SHOW AGGREGATES;

+-------------------+
| Aggregates_in_db1 |
+-------------------+
| multiply_hundred  |
+-------------------+
1 row in set (0.00 sec)