Re: Overlapping timestamptz ranges with priority - Mailing list pgsql-general
From | Ray O'Donnell |
---|---|
Subject | Re: Overlapping timestamptz ranges with priority |
Date | |
Msg-id | b284ebd4-8fd4-a1e7-22e8-adab855e3c7c@rodonnell.ie Whole thread Raw |
In response to | Overlapping timestamptz ranges with priority (Ray O'Donnell <ray@rodonnell.ie>) |
Responses |
Re: Overlapping timestamptz ranges with priority
|
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
pgsql-general by date: