PostgreSQL aggregate function over range

Question

I am trying to create a function that will find the intersection of tsrange, but I can't get it work:

CREATE AGGREGATE intersection ( tsrange ) (
    SFUNC = *,
    STYPE = tsrange
)

Answer 1

There are two modifications to your attempt. First, I don't think you can use an operator as the SFUNC, so you need to define a named function to do the intersection, and use that.

CREATE or REPLACE FUNCTION int_tsrange(a tsrange, b tsrange)
   returns tsrange language plpgsql as 
      'begin return a * b; end';

Secondly, the default value for a range is the empty range -- so the intersection will always be empty. You need to initialize the range to an infinite range '[,]' to begin the aggregate. The aggregate definition then looks like:

CREATE AGGREGATE intersection ( tsrange ) (
    SFUNC = int_tsrange,
    STYPE = tsrange,
    INITCOND = '[,]'
);