Background information

In Oracle, parallel processing can be enabled for custom aggregate functions. In PolarDB-O, this is also supported, but with some differences in syntax. This section describes how to create custom aggregate functions and enable parallelism in the next PolarDB-O.

Solutions

Syntax for creating a custom aggregate function in PolarDB-O:
CREATE AGGREGATE name ( [ argmode ] [ argname ] arg_data_type [ , ... ] ) (
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , SSPACE = state_data_size ]
    [ , FINALFUNC = ffunc ]
    [ , FINALFUNC_EXTRA ]
    [ , FINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , COMBINEFUNC = combinefunc ]
    [ , SERIALFUNC = serialfunc ]
    [ , DESERIALFUNC = deserialfunc ]
    [ , INITCOND = initial_condition ]
    [ , MSFUNC = msfunc ]
    [ , MINVFUNC = minvfunc ]
    [ , MSTYPE = mstate_data_type ]
    [ , MSSPACE = mstate_data_size ]
    [ , MFINALFUNC = mffunc ]
    [ , MFINALFUNC_EXTRA ]
    [ , MFINALFUNC_MODIFY = { READ_ONLY | SHAREABLE | READ_WRITE } ]
    [ , MINITCOND = minitial_condition ]
    [ , SORTOP = sort_operator ]
    [ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
)

Examples

Regular aggregate functions are supported in PolarDB-O. If you want to implement special features, you can customize the relevant functions as needed (sfunc, stype, FINALFUNC).

For example, specify symbolic join aggregations, which are applicable to most scenarios at the same time:
create aggregate launch_concat(text,text) (  
  sfunc = pg_catalog.string_agg_transfn,  
  stype = internal,  
  FINALFUNC = pg_catalog.string_agg_finalfn  
);  
The result is:
select launch_concat(id::text, ',') from generate_series(1,10) t(id);  
    launch_concat       
----------------------  
 1,2,3,4,5,6,7,8,9,10