This topic describes how to create custom aggregate functions and enable parallel processing for the custom aggregate functions in PolarDB for Oracle.

Background information

In Oracle, parallel processing can be enabled for custom aggregate functions. PolarDB for Oracle also supports this feature. However, the syntax for enabling this feature in PolarDB for Oracle is different from that in Oracle.

Solutions

Syntax for creating a custom aggregate function in PolarDB for Oracle:
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

PolarDB for Oracle supports regular aggregate functions. If you want to implement special features, you can customize the required functions, such as sfunc, stype, and FINALFUNC.

For example, you can execute the following statement to create an aggregate function to aggregate data based on the specified delimiter. This function can be used in most scenarios.
create aggregate launch_concat(text,text) (  
  sfunc = pg_catalog.string_agg_transfn,  
  stype = internal,  
  FINALFUNC = pg_catalog.string_agg_finalfn  
);
The following result is generated after you execute the launch_concat function:
select launch_concat(id::text, ',') from generate_series(1,10) t(id);  
    launch_concat       
----------------------  
 1,2,3,4,5,6,7,8,9,10
Note If an error occurs when you call a custom function, you can submit a ticket for troubleshooting.