Aggregate concatenation functions help creating a concatenated list out of a recordset. Useful for reports, hierarchical trees, etc.

MySQL supplies GROUP_CONCAT for this purpose. SYS_CONNECT_BY PATH and FOR XML can be used in Oracle and MS SQL.

In PostgreSQL, we cannot use these tricks, but we can create our own aggregate function. And this function will also accept two more extremely useful parameters: DELIMITER and IS_DISTINCT.

DELIMITER is self-explanatory: it will be used to separate the values. IS_DISTINCT, on the other hard, is more tricky: it will be evaluated on each step, and will be used to decide if this very value should be filtered out.

As you can see, we utilize a custom datatype here. We need to store DELIMITER along with intermediate aggregated data, as it's the only data FINALFUNC has access to. We also use array operations to store data and search for duplicates.

The aggregate groups all records that have value grouped and leaves all other records as is.

When using this aggregate against the indexed column on large datasets, it's better to use DISTINCT clause in an uncorrelated subquery. This aggregate will not utilize any indexes on the aggregated column, while DISTINCT will: