On 03/07/2021 21:13, Adrian Klaver wrote:
On 7/3/21 12:16 PM, Ray O'Donnell wrote:
Yes, you're right - I realised that after I sent my last email. The
inner loop in the function should have matched overlapping bookings by
aircraft registration:
-- 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;
Was the above supposed to show the change?
Whoops, sorry, here it is:
for m_overlapping_time in
select booking_id, booking_time from bookings
where aircraft_reg = m_rec.aircraft_reg
and booking_id < m_rec.booking_id
and booking_time && m_rec.booking_time
loop
[... etc ...]
select booking_id, aircraft_reg, booking_time from bookings order by
aircraft_reg, lower(booking_time);
Pretty sure lower() is not needed, if I'm following this correctly:
https://www.postgresql.org/docs/12/functions-range.html
"The simple comparison operators <, >, <=, and >= compare the lower
bounds first, and only if those are equal, compare the upper bounds.
These comparisons are not usually very useful for ranges, but are
provided to allow B-tree indexes to be constructed on ranges."
Ah, good - thanks for pointing that out.
In the case where the lower bound is the same I'm thinking using
lower() will result in different ordering under different circumstances:
I see what you mean. It shouldn't matter for our use case; ordering on
the aircraft registration and time is what counts for us, and the output
of the function ought to produce well-ordered booking times for each
aircraft. The other columns are used for display purposes only.
I need to play with it a bit more: for example, if a long,
lower-priority booking is behind a short, higher-priority one such
that the long one extends both before and after the short one, then
the range-difference operator will give me an error about a
non-contiguous result. However, I think I'm heading in the right
direction now.
Great. Good luck going forward.
Thanks again for your help - much appreciated!
Ray.
--
Raymond O'Donnell // Galway // Ireland
ray@xxxxxxxxxxxx
