CREATE AGGREGATE

Description

Defines a new aggregate function.

Syntax

CREATE AGGREGATE name ( input_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    [ , SORTOP = sort_operator ]
)

or the old syntax

CREATE AGGREGATE name (
    BASETYPE = base_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
    [ , SORTOP = sort_operator ]
)

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

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

Examples

-- Create a user-defined function.
openGauss=# 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.
openGauss=# CREATE AGGREGATE sum_add(int)
(
    sfunc = int_add,
     
    stype = int,
    
    initcond = '0'
     
);

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

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

-- Drop the aggregate function.
openGauss=# DROP AGGREGATE sum_add(int);

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

-- Drop the test table.
openGauss=# DROP TABLE test_sum;