CREATE AGGREGATE

Description

Creates an aggregate function.

Syntax

CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
    SFUNC = sfunc,
    -- SFUNC1 = sfunc, // Earlier format, equivalent to SFUNC = sfunc.
    STYPE = state_data_type
    -- STYPE1 = state_data_type, // Earlier format, equivalent to STYPE = state_data_type.
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    -- [ , INITCOND1 = initial_condition ] // Earlier format, equivalent to INITCOND = initial_condition.
    [ , SORTOP = sort_operator ]
    [ , CFUNC = collection_func ]
    [ , INITCOLLECT = initial_collection_condition ]
    [ , IFUNC = init_func ]
    [ , SHIPPABLE = is_shippable ]
);

You can also use:

CREATE AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    -- SFUNC1 = sfunc, // Earlier format, equivalent to SFUNC = sfunc.
    STYPE = state_data_type
    -- STYPE1 = state_data_type, // Earlier format, equivalent to STYPE = state_data_type.
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    -- [ , INITCOND1 = initial_condition ] // Earlier format, equivalent to INITCOND = initial_condition.
    [ , SORTOP = sort_operator ]
    [ , CFUNC = collection_func ]
    [ , INITCOLLECT = initial_collection_condition ]
    [ , IFUNC = init_func ]
);

Parameters

name
Name (optionally schema-qualified) of the aggregate function to be created.
input_data_type
Data type of the input to be processed by the aggregate function. To create a zero-parameter aggregate function, you can use an asterisk (*) instead of a list of input data types. (count(*) is an instance of such aggregate function.)

base_type
In the CREATE AGGREGATE syntax, the input data type is specified by the basetype parameter instead of following the name of the aggregate function. Note that this syntax allows only one input parameter. To create a zero-parameter aggregate function, you can set basetype to ANY instead of *.

sfunc
Name of the state conversion function that will be called on each input line. For an aggregate function with N parameters, sfunc must have N+1 parameters. The first parameter is of the state_data_type type, and the other parameters match the declared input data types. The function must return a value of the state_data_type type. This function accepts the current state value and the current input data, and returns the next state value. The default behavior of the conversion function in an ORA-compatible database is strict, that is, the null input values are skipped. For GaussDB, you need to define the strict attribute of the conversion function. User-defined C functions are not supported.

state_data_type
Data type of the aggregation status value.

ffunc
Final processing function called after all the input lines have been converted, which calculates the result of aggregation. This function must accept a parameter of state_data_type. The output data type of the aggregation is defined as the return type of this function. If ffunc is not specified, the state value of the aggregation result is used as the aggregation result, and the output type is state_data_type. User-defined C functions are not supported.

initial_condition
Initial setting (value) of a state value. It must be a text constant value acceptable to state_data_type. If the parameter is not specified, the initial state value is null.

sort_operator
Sort operator used for MIN or MAX aggregation. This is just an operator name (optionally schema-qualified). This operator assumes that the input data type is the same as that of aggregation.
collection_func
Allows users to perform distributed aggregation so as to improve performance. collection_func is essentially the status conversion result generated by combining DNs. If there is no final function, the result generated by collection_func is the aggregation result. If you want collection_func to skip null values, specify collection_func as a strict function. If it is not specified as a strict function, ensure that null input values are processed properly. collection_func must have two input parameters of the type specified by state_data_type. The first input parameter state_data_type is transferred to the first parameter of the next execution of collection_func. If is_shippable is false, collection_func will not be executed. User-defined C functions are not supported.
initial_collection_condition
Initial setting (value) of the state value of collection_func. It must be a text constant value acceptable to state_data_type. If it is not specified, the initial state value is null.
init_func
Initial setting function of the sfunc status value. It cannot return NULL. It must return the state_data_type type and have no input parameter. If both init_func and initial_condition are set, the return value of init_func is preferentially used as the initial value. User-defined C functions are not supported.
is_shippable
Specifies whether the aggregate function can be pushed down. The value can only be true or false. Currently, this parameter does not affect the promotion of built-in aggregate functions. It affects only the behavior of user-defined aggregate functions. The default value is false. User-defined aggregations can be pushed down only when collection_func is executed and is_shippable is set to true.

Examples

-- 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 sum_add(int)
(
    sfunc = int_add,
    
    stype = int,
    
    initcond = '0'
    
);

-- Create a test table and add data.
gaussdb=# CREATE TABLE test_sum(a int,b int,c int);
gaussdb=# INSERT INTO test_sum VALUES(1,2),(2,3),(3,4),(4,5);

-- Execute the aggregate function.
gaussdb=# SELECT sum_add(a) FROM test_sum;
   sum_add
-----------
   10

-- Delete the aggregate function.
gaussdb=# DROP AGGREGATE sum_add(int);

-- Delete the user-defined function.
gaussdb=# DROP FUNCTION int_add(int,int);

-- Delete the test table.
gaussdb=# DROP TABLE test_sum;