For the purpose of this example, the goal is to solve a very simple problem. If a customer takes a taxi, they usually have to pay for getting in the taxi—for example, €2.50. Then, let us assume that for each kilometer, the customer has to pay €2.20. The question now is, what is the total price of a trip?
Of course, this example is simple enough to solve without a custom aggregate; however, let's see how it works. First, some test data is created:
test=# CREATE TABLE t_taxi (trip_id int, km numeric); CREATE TABLE test=# INSERT INTO t_taxi VALUES (1, 4.0), (1, 3.2), (1, 4.5), (2, 1.9), (2, 4.5); INSERT 0 5
To create aggregates, PostgreSQL offers the CREATE AGGREGATE command. The syntax of this command has become ...