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@rodonnell.ie
