create extension btree_gist;
--DROP TABLE IF EXISTS events CASCADE;
--DROP TABLE IF EXISTS event_buckets_5 CASCADE;
CREATE TABLE events (
    id bigserial,
    starttime timestamp,
    endtime timestamp,
    data varchar,
    primary key(id)
);

CREATE TABLE event_buckets_5(
CHECK (bucket<=5),
bucket integer null,
event_id bigint not null references events(id),
starttime timestamp,
endtime timestamp,
EXCLUDE USING gist(bucket with =, tsrange(starttime, endtime, '[)') WITH &&)
);


create function bucket_insert_event_inner(event events, bucket_number integer)
RETURNS integer
AS $$
BEGIN
insert into event_buckets_5 (bucket, event_id, starttime, endtime) values (bucket_number, event.id, event.starttime, event.endtime);
return bucket_number;
EXCEPTION
WHEN exclusion_violation then
return null;
END
$$ language plpgsql;

create function bucket_insert_event(event events, max_bucket_num integer)
RETURNS integer
AS $$
DECLARE
  current_bucket_num integer := 1;
  inner_result integer;
BEGIN
WHILE current_bucket_num <= max_bucket_num LOOP
    inner_result = bucket_insert_event_inner(event, current_bucket_num);
    IF inner_result IS NOT NULL THEN
        return inner_result;
    END IF;
    current_bucket_num := current_bucket_num + 1;
END LOOP;
-- If we get here without having hit the return, we've never
-- successfully inserted even up to max bucket num. Insert with null.
insert into event_buckets_5 (bucket, event_id, starttime, endtime) values (null, event.id, event.starttime, event.endtime);
return null;
END
$$ language plpgsql;



insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T00:10:00', '';
select bucket_insert_event((select e from events e where id=1), 5);

insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T00:20:00', '';
select bucket_insert_event((select e from events e where id=2), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T00:30:00', '';
select bucket_insert_event((select e from events e where id=3), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T00:40:00', '';
select bucket_insert_event((select e from events e where id=4), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T00:50:00', '';
select bucket_insert_event((select e from events e where id=5), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T01:00:00', '';
select bucket_insert_event((select e from events e where id=6), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:00:00', '2021-12-12T01:00:00', '';
select bucket_insert_event((select e from events e where id=(select max(id) from events)), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:30:00', '2021-12-12T01:00:00', '';
select bucket_insert_event((select e from events e where id=(select max(id) from events)), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:40:00', '2021-12-12T01:00:00', '';
select bucket_insert_event((select e from events e where id=(select max(id) from events)), 5);
insert into events select nextval('events_id_seq'), '2021-12-12T00:20:00', '2021-12-12T01:50:00', '';
select bucket_insert_event((select e from events e where id=(select max(id) from events)), 5);

select * from event_buckets_5 order by ctid;

--  bucket | event_id |      starttime      |       endtime
-- --------+----------+---------------------+---------------------
--       1 |        1 | 2021-12-12 00:00:00 | 2021-12-12 00:10:00
--       2 |        2 | 2021-12-12 00:00:00 | 2021-12-12 00:20:00
--       3 |        3 | 2021-12-12 00:00:00 | 2021-12-12 00:30:00
--       4 |        4 | 2021-12-12 00:00:00 | 2021-12-12 00:40:00
--       5 |        5 | 2021-12-12 00:00:00 | 2021-12-12 00:50:00
--         |        6 | 2021-12-12 00:00:00 | 2021-12-12 01:00:00
--         |        7 | 2021-12-12 00:00:00 | 2021-12-12 01:00:00
--       1 |        8 | 2021-12-12 00:30:00 | 2021-12-12 01:00:00
--       2 |        9 | 2021-12-12 00:40:00 | 2021-12-12 01:00:00
--         |       10 | 2021-12-12 00:20:00 | 2021-12-12 01:50:00
-- (10 rows)