Creating Custom Aggregate Functions in PostgreSQL using PL/PERL

This article describes how to create aggregate functions, similar to SQL's min and max, to summarise data for you in queries.

Postgres supports the creation of custom aggregate functions in various languages, including PL/SQL, PL/Perl, PL/Python, and PL/C. The general method is described in their documentation. Using PL/PERL for custom functions is also described. However, there is no good description of how to the aggregate function operations using Perl.

The only relevant resource I could find was how to create aggregate functions using PL/SQL. This is fine if the type you are attempting to summarise is "accessible" using SQL. Unfortunately Postgres's path data type does not have a feature allowing you to extract the actual points that compose that path (!). Hence, some text processing is necessary, and hence I used Perl.

An aggregate requires three things:

The aggregate will normally return a tuple of the type of the state variable, but this can be changed by using a final function to post-process the result of the aggregate, and provide a more useful return type. See the pages above for details.

The function to be used for the aggregate must accept two arguments. The type of the first is the type of the state variable, whilst the type of the second is the type of the column to which the aggregate function will be applied (the base type specified when the aggregate is defined). Note that you can have polymorphic functions (see the web pages above).

An important point is that SQL is case insensitive. This means that all the fields of your custom type, as well as your function name should be lower case. Use underscores to separate words. If you do not do this, you risk errors of the form "Perl hash contains non-existant column %s", where %s is the field name in question.

The state variable is passed to the function as a reference to a hash, which then has values for keys that have the same name as the fields of the custom type you defined.

The function itself can use any of the normal Perl constructs. However, for debugging, print does not work, so one way to achieve a similar effect is to define a field of type TEXT in your custom type, which you then write into with whatever debug output you wish. This will then be printed in the result tuple.

Given the above, here is an example: