Re: Overlapping timestamptz ranges with priority - Mailing list pgsql-general

On 27/06/2021 23:41, Ray O'Donnell wrote:
> Hi all,
> 
> I'm playing with timestamptz ranges for a hobby project. I have a
> table with a tstzrange column, in which the timestamps can overlap;
> where they do, rows with a higher priority (derived from a bigint
> primary key column) should be picked.
> 
> What I'd like to do is present a view which shows timestamp ranges at
>  the front of the queue, as it were; where ranges overlap, these may
> be segments of a range from a particular row. I'm having trouble with
> this and would appreciate suggestions.

I've come up with a way of doing it using a function... it's not going 
to be very efficient if the number of rows gets large, due to nested 
loops, but as the system generally keeps only a limited number of 
bookings (no more that a few hundred), I think it'll do - certainly as a 
first run at it.

Firstly, the table structure (as it now stands) on which the function 
will operate:

CREATE TABLE bookings
(
     booking_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
     aircraft_reg text NOT NULL,
     type_code text NOT NULL,
     booking_time tstzrange NOT NULL,
     owner_uid text NOT NULL,
     owner_name text NOT NULL,

     CONSTRAINT bookings_pk PRIMARY KEY (booking_id),

     (... foregin keys etc...)
);


And here's the function:

create or replace function get_visible_bookings()
returns setof bookings
language plpgsql
as
$$
declare
   m_rec bookings;
   m_overlapping record;
   m_visible_time tstzrange;
begin
   -- Loop through all bookings on the system, ordered on booking ID.
   -- The booking ID also give the queue priority of the booking:
   -- bookings with a lower ID have a higher priority.
   for m_rec in
     select * from bookings order by booking_id
   loop
     m_visible_time := m_rec.booking_time;

     -- For each booking, check whether there are any with
     -- a higher priority and whose times overlap it.
     for m_overlapping in
       select booking_id, booking_time from bookings
       where booking_id < m_rec.booking_id
       and booking_time && m_rec.booking_time
     loop
       -- Snip away any overlapping (obscured) time.
       m_visible_time := m_visible_time - m_overlapping.booking_time;
     end loop;

     -- If any of the current booking's time is still visible,
     -- then return the row with what's left of the time.
     if not isempty(m_visible_time) then
    return next row(m_rec.booking_id, m_rec.aircraft_reg,
           m_rec.type_code, m_visible_time,
           m_rec.owner_uid, m_rec.owner_name);
     end if;
   end loop;

   return;
end;
$$;



-- 
Raymond O'Donnell // Galway // Ireland
ray@rodonnell.ie