Mastering PostgreSQL 9.6

Writing hypothetical aggregates

Writing aggregates is not hard and it can be highly beneficial to perform more complex operations. In this section the plan is to write a hypothetical aggregate, which has already been discussed in this chapter.

Implementing hypothetical aggregates is not too different from writing normal aggregates. The really hard part is to figure out, when to actually use one. To make this section as easy to understand as possible I have decided to include a trivial example: given a specific order, what would the result be if we added abc to the end of the string?

Here is how it works:

CREATE AGGREGATE name ( [ [ argmode ] [ argname ] arg_data_type [ , ... ] ]  ORDER BY [ argmode ] [ argname ] arg_data_type [ , ... ] ) ...