Using a User‑Defined Multiplicative Aggregate in PostgreSQL to Count Travel Package Combinations

The business requirement is to count the number of possible travel itineraries where each product may have N flights, M segments, and each segment O different hotel choices, resulting in a total of N × (O₁ × O₂ × … × Oₘ). The author shows how to solve this in PostgreSQL using a user‑defined aggregate that performs a continuous multiplication.

Code definition

CREATE FUNCTION multiply_aggregate(numeric,numeric) RETURNS numeric AS ' select $1 * $2; ' language sql IMMUTABLE STRICT;
CREATE AGGREGATE multiply (basetype=numeric, sfunc=multiply_aggregate, stype=numeric, initcond=1 );

Example usage

qunar_group=# select *  from generate_series(1,5);
 generate_series 
-----------------
               1
               2
               3
               4
               5
(5 rows)

Time: 3.127 ms
qunar_group=# select multiply(a) from (select generate_series as a  from generate_series(1,5)) b;
 multiply 
----------
      120
(1 row)

Practical query

select product_id, multiply(hotelnum)
from (
  select s.product_id, s.id, count(1) as hotelnum
  from b2c_package_hotel_seg s
  join b2c_package_hotel h on s.id = h.hotel_seg_id
  group by s.product_id, s.id
) a
group by product_id;

The advantage of this user‑defined aggregate is its high flexibility: as long as the data can be grouped, PostgreSQL can compute the product for each group, making it easy to adapt to various combinatorial counting scenarios.

Author: He Weiping, senior search and database researcher at Qunar’s Vacation Business Unit, translator of the first Chinese PostgreSQL manual and co‑author of Programming Perl 3rd edition, with nearly 18 years of experience in search, distributed systems, and database technologies.