Creating simple aggregates

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 ...

Get Mastering PostgreSQL 11 - Second Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.