ALTER AGGREGATE EARLY ACCESS

Synopsis

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

Syntax

Grammar
Diagram

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

alter_aggregate_action

aggregate_signature

arg_mode

arg_type

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.

Examples

Create an aggregate function and move it to a different schema:

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:

 schema | aggregate
--------+-----------
 s2     | my_agg

Rename an aggregate.

ALTER AGGREGATE sumdouble (float8) RENAME TO other_sumdouble;

Change the owner.

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.

ALTER AGGREGATE sumdouble (float8) SET SCHEMA public;