Help Center> GaussDB> Distributed_8.x> SQL Reference> SQL Syntax> A> ALTER AGGREGATE

Updated on 2024-06-03 GMT+08:00

View PDF

ALTER AGGREGATE

Description

Modifies the definition of an aggregate function, including the name, owner, and schema.

Precautions

To use ALTER AGGREGATE, you must be the owner of the aggregate function. To change the schema of an aggregate function, you must have the CREATE permission on the new schema. To change the owner, you must be a direct or indirect member of the new role, and the role must have the CREATE permission on the aggregate function's schema. (These constraints restrict the owner from performing illegal operations by deleting and rebuilding aggregate functions.) However, a user with the SYSADMIN permission can change the ownership of an aggregate function in any way.

Syntax

ALTER AGGREGATE name ( argtype [ , ... ] ) RENAME TO new_name;
ALTER AGGREGATE name ( argtype [ , ... ] ) OWNER TO new_owner;
ALTER AGGREGATE name ( argtype [ , ... ] ) SET SCHEMA new_schema;

Parameters

name
Name (optionally schema-qualified) of an existing aggregate function.
argtype
Input data type of the aggregate function. To reference a zero-parameter aggregate function, you can write an asterisk (*) instead of a list of input data types.
new_name
New name of the aggregate function.
new_owner
New owner of the aggregate function.
new_schema
New schema of the aggregate function.

Examples

Change the name of the aggregate function.

-- Create a user-defined function.
gaussdb=# CREATE OR REPLACE FUNCTION int_add(int,int)
	returns int as $BODY$
declare
begin
	return $1 + $2;
end;
$BODY$ language plpgsql;

-- Create an aggregate function.
gaussdb=# CREATE AGGREGATE myavg (int)
(
    sfunc = int_add,
    stype = int,
    initcond = '0'
);

-- Rename the aggregate function myavg that accepts int-type parameters to my_average.
gaussdb=# ALTER AGGREGATE myavg(int) RENAME TO my_average;

Change the owner of the aggregate function.

-- Create a user joe.
gaussdb=# CREATE USER joe PASSWORD '********';

-- Change the owner of the aggregate function my_average that accepts int-type parameters to joe.
gaussdb=# ALTER AGGREGATE my_average(int) OWNER TO joe;

Change the schema of the aggregate function.

-- Create a schema myschema:
gaussdb=# CREATE SCHEMA myschema;

-- Move the aggregate function my_average that accepts int-type parameters to myschema.
gaussdb=# ALTER AGGREGATE my_average(int) SET SCHEMA myschema;

-- Delete the schema, user, and related function.
gaussdb=# DROP SCHEMA myschema CASCADE;
gaussdb=# DROP USER joe;
gaussdb=# DROP FUNCTION int_add(int,int);