On 03/07/2021 18:59, Adrian Klaver wrote:
I'm not sure this is doing what you think it is;
[...]
select * from get_visible_bookings();
booking_id | aircraft_reg | type_code | booking_time | owner_uid | owner_name
------------+--------------+-----------+-----------------------------------------------------+-----------+------------
1 | A1ZX | type1 | ["2021-07-03 10:00:00-07","2021-07-03 14:00:00-07"] | 1 | aklaver
2 | A1ZX | type1 | ("2021-07-03 14:00:00-07","2021-07-03 16:00:00-07"] | 1 | aklaver
3 | A1ZX | type1 | ["2021-07-04 09:00:00-07","2021-07-04 12:00:00-07"] | 1 | aklaver
4 | B2CA | type2 | ["2021-07-03 09:00:00-07","2021-07-03 10:00:00-07") | 2 | wilbur
(4 rows)
The booking_id for aircraft B2CA with booking_time of ["2021-07-03
11:00:00-07","2021-07-03 14:00:00-07"] is not accounted for. There is a
step missing that accounts for bookings being assigned to a particular
aircraft.
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;
When this is corrected, I get what I'm looking for (trying it here with
your data):
set time zone 'America/Los_Angeles';
SET
select booking_id, aircraft_reg, booking_time from bookings order by
aircraft_reg, lower(booking_time);
booking_id | aircraft_reg | booking_time
------------+--------------+-----------------------------------------------------
25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03
14:00:00-07")
26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03
16:00:00-07")
27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04
12:00:00-07")
28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03
12:00:00-07"]
29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03
14:00:00-07"]
(5 rows)
select booking_id, aircraft_reg, booking_time from
get_visible_bookings() order by aircraft_reg, lower(booking_time);
booking_id | aircraft_reg | booking_time
------------+--------------+-----------------------------------------------------
25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03
14:00:00-07")
26 | A1ZX | ["2021-07-03 14:00:00-07","2021-07-03
16:00:00-07")
27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04
12:00:00-07")
28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03
12:00:00-07"]
29 | B2CA | ("2021-07-03 12:00:00-07","2021-07-03
14:00:00-07"]
(5 rows)
gfc_booking6_dev=# set time zone 'America/Los_Angeles';
SET
gfc_booking6_dev=# select booking_id, aircraft_reg, booking_time from
bookings order by aircraft_reg, lower(booking_time);
booking_id | aircraft_reg | booking_time
------------+--------------+-----------------------------------------------------
25 | A1ZX | ["2021-07-03 10:00:00-07","2021-07-03
14:00:00-07")
26 | A1ZX | ["2021-07-03 12:00:00-07","2021-07-03
16:00:00-07")
27 | A1ZX | ["2021-07-04 09:00:00-07","2021-07-04
12:00:00-07")
28 | B2CA | ["2021-07-03 09:00:00-07","2021-07-03
12:00:00-07"]
29 | B2CA | ["2021-07-03 11:00:00-07","2021-07-03
14:00:00-07"]
(5 rows)
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.
Thanks,
Ray.
--
Raymond O'Donnell // Galway // Ireland
ray@xxxxxxxxxxxx
