CREATE AGGREGATE

Description

Creates an aggregate function.

Syntax

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

or the old syntax

CREATE AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    -- SFUNC1 = sfunc, // Outdated format, equivalent to SFUNC = sfunc.
    STYPE = state_data_type
    -- STYPE1 = state_data_type, // Outdated format, equivalent to STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    -- [ , INITCOND1 = initial_condition ] // Outdated 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 this aggregate function.)

• base_type

  In the CREATE AGGREGATE syntax, the input data type is specified by the basetype parameter instead of following name. Note that the previous 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 more than one parameter. 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 of an A-compatible database is strict, that is, the NULL input value is skipped. For GaussDB, you need to define the strict attribute of the conversion function.

• 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.

• 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

  Currently, this parameter does not take effect in centralized mode.

• initial_collection_condition

  Collects the initial setting (value) of the function status 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.

• 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.

• 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 pushdown of built-in aggregate functions. It affects only the behavior of user-defined aggregate functions. The default value is false. Currently, this parameter does not take effect in centralized mode.

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;

Helpful Links

ALTER AGGREGATE and DROP AGGREGATE