Dec 6, 2024 by Robert Gravelle

One of the standout features of PostgreSQL is its extensive support for user-defined functions and data types. This allows developers to create custom conversion, operator, and aggregate functions. Aggregates offer a powerful way to perform complex calculations and transformations on data, going beyond the standard SQL aggregate functions like SUM, AVG, and COUNT. Both Navicat for PostgreSQL and Navicat Premium make it easy to write custom functions and aggregates that integrate seamlessly with the database, thanks to their specialized graphical user interface (GUI). All we need to do is provide a few details and Navicat produces the pgSQL statement for us! In today's blog, we'll be creating an aggregate to work with the DVD Rental database that concatenates movie titles by category.

About Aggregates

Aggregates are a fundamental feature of SQL that allow you to perform calculations or transformations on a set of rows and return a single result. The most common aggregate functions are SUM, AVG, COUNT, MIN, and MAX, which allow you to quickly summarize data by calculating totals, averages, counts, minimum values, and maximum values, respectively.

However, the built-in aggregate functions provided by SQL don't always meet the specific needs of an application. This is where the ability to create custom aggregates becomes useful. Custom aggregates allow you to define your own logic for summarizing and transforming data, going beyond the standard set of SQL aggregates. The process typically involves defining a state transition function, which is called for each row to update an accumulator, as well as an optional final function that is called to produce the final aggregate result.

Generating the Transition and Final Functions

Our transition function, array_append_state(), will be called for each row to update the aggregate state.

To access Navicat's function editor, click the Function button in the main button bar and then click on "New Function" in the Objects toolbar:

Navicat will start us off with the main function definition. From there, we'll supply the function name, input parameters, and body:

CREATE FUNCTION "public"."array_append_state" (current_state text[], new_value text)
  RETURNS text[] AS $BODY$
BEGIN
  RETURN array_append(current_state, new_value);
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

When we're done, we can click Save to create the function.

Now we'll go back to the Objects tab and click on "New Function" to create the final function.

The array_to_comma_string() function will take an array of film titles and insert a comma between each element:

CREATE FUNCTION "public"."array_to_comma_string" (state text[])
  RETURNS text AS $BODY$
BEGIN
  RETURN array_append(state, ', ');
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

Creating the comma_concat() Aggregate Function

We can now plug our two functions into Navicat's Aggregate Editor. We can access the editor by clicking the Others button in the main button bar and then selecting "Aggregate" from the context menu:

In the form, we'll set the Input type to "text", enter a State type of "text[]" and supply our State and Final functions. Also, make sure that the Initial condition is an empty array ("{}"):

We can see the generated SQL by clicking on the Preview tab:

CREATE AGGREGATE "public"."Untitled" (In "pg_catalog"."text")
(
  SFUNC = "public"."array_append_state",
  STYPE = "pg_catalog"."text[]",
  FINALFUNC = "public"."array_to_comma_string",
  INITCOND = "{}",
  PARALLEL = UNSAFE
);

ALTER AGGREGATE "public"."Untitled"("pg_catalog"."text") OWNER TO "postgres";

Notice that the name of the aggregate is "Untitled". Navicat will prompt us for the name when we hit the Save button and execute the command with the name that we provide.

Using Our Custom Aggregate

We can now invoke our aggregate function just like any other function. Here's a query that fetches a list of movies by category:

SELECT 
    c.name AS category,
    comma_concat(f.title) AS movies
FROM category c
JOIN film_category fc ON c.category_id = fc.category_id
JOIN film f ON fc.film_id = f.film_id
GROUP BY c.name
ORDER BY c.name;

Conclusion

In today's blog, we created a custom PostgreSQL aggregate in Navicat Premium to work with the DVD Rental database that concatenates movie titles by category.

Interested in giving Navicat Premium 17 a try? You can download it for a 14-day fully functional FREE trial. It's available for Windows, macOS, and Linux operating systems.