---
title: "ALTER AGGREGATE"
url: "https://docs.yugabyte.com/stable/api/ysql/the-sql-language/statements/ddl_alter_aggregate/"
---

# ALTER AGGREGATE

Use the ALTER AGGREGATE statement to change the definition of an aggregate function.

## Synopsis

Use the `ALTER AGGREGATE` statement to change the schema of an existing aggregate function.

## Syntax

- [![Grammar Icon](/icons/file-lines.svg)Grammar](#grammar-1)
- [![Diagram Icon](/icons/diagram.svg)Diagram](#diagram-1)

```output.ebnf
alter_aggregate ::= ALTER AGGREGATE aggregate_name ( 
                    aggregate_signature ) alter_aggregate_action

alter_aggregate_action ::= RENAME TO new_name
                           | OWNER TO { new_owner
                                        | CURRENT_ROLE
                                        | CURRENT_USER
                                        | SESSION_USER }
                           | SET SCHEMA schema_name

aggregate_signature ::= * | aggregate_arg [ , ... ]
                        | [ aggregate_arg [ , ... ] ] ORDER BY 
                          aggregate_arg [ , ... ]

arg_mode ::= IN | OUT | INOUT | VARIADIC

arg_type ::= type_name
```

#### alter\_aggregate

ALTERAGGREGATE[aggregate\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-name)([aggregate\_signature](#aggregate-signature))[alter\_aggregate\_action](#alter-aggregate-action)

#### alter\_aggregate\_action

RENAMETO[new\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#new-name)OWNERTO[new\_owner](/stable/api/ysql/syntax_resources/grammar_diagrams#new-owner)CURRENT\_ROLECURRENT\_USERSESSION\_USERSETSCHEMA[schema\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#schema-name)

#### aggregate\_signature

\*,[aggregate\_arg](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-arg),[aggregate\_arg](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-arg)ORDERBY,[aggregate\_arg](/stable/api/ysql/syntax_resources/grammar_diagrams#aggregate-arg)

#### arg\_mode

INOUTINOUTVARIADIC

#### arg\_type

[type\_name](/stable/api/ysql/syntax_resources/grammar_diagrams#type-name)

You must identify the aggregate to be altered by its name, schema, and signature. The signature must include the data types of the function's arguments.

## Semantics

### SET SCHEMA *schema\_name*

Move the aggregate function to a different schema. The function is renamed within its original schema, but all dependent objects are maintained correctly.

For more details, see the semantics in the [PostgreSQL documentation](https://www.postgresql.org/docs/15/sql-alteraggregate.html "PostgreSQL documentation").

## Examples

- Create an aggregate function and move it to a different schema:
  
  ```plpgsql
  create schema s1;
  create schema s2;    
  
  -- Create a simple aggregate
  create aggregate s1.my_agg(int) (
    stype = int,
    sfunc = int4pl,
    initcond = '0'
  );    
  
  -- Move the aggregate to s2
  alter aggregate s1.my_agg(int) set schema s2;    
  
  -- Verify the change
  select n.nspname as schema, p.proname as aggregate
  from pg_proc p
  join pg_namespace n on p.pronamespace = n.oid
  where p.proname = 'my_agg';
  ```
  
  You should see an output similar to the following:
  
  ```output
   schema | aggregate
  --------+-----------
   s2     | my_agg
  ```
- Rename an aggregate.
  
  ```plpgsql
  ALTER AGGREGATE sumdouble (float8) RENAME TO other_sumdouble;
  ```
- Change the owner.
  
  ```plpgsql
  ALTER AGGREGATE sumdouble (float8) OWNER TO yugabyte;
  ALTER AGGREGATE sumdouble (float8) OWNER TO CURRENT_ROLE;
  ALTER AGGREGATE sumdouble (float8) OWNER TO CURRENT_USER;
  ALTER AGGREGATE sumdouble (float8) OWNER TO SESSION_USER;
  ```
- Change the schema.
  
  ```plpgsql
  ALTER AGGREGATE sumdouble (float8) SET SCHEMA public;
  ```

## See also

- [CREATE AGGREGATE](/stable/api/ysql/the-sql-language/statements/ddl_create_aggregate "CREATE AGGREGATE")
- [DROP AGGREGATE](/stable/api/ysql/the-sql-language/statements/ddl_drop_aggregate "DROP AGGREGATE")