MBXJWQrH

1CREATE TABLE IF NOT EXISTS channels_data_location (
2  channel text NOT NULL REFERENCES channels(name) ON DELETE CASCADE,
3  disk text NOT NULL, 
4  date_start TIMESTAMP with time zone NOT NULL,
5  date_end TIMESTAMP with time zone NOT NULL
6);
7
8TRUNCATE table channels_data_location CASCADE;
9
10INSERT INTO channels_data_location (channel, disk, date_start, date_end) VALUES ('arte', 'd1', '2020-02-27 04:00:00+02', '2020-02-27 5:00:00+02');
11INSERT INTO channels_data_location (channel, disk, date_start, date_end) VALUES ('arte', 'd2', '2020-02-27 05:00:00+02', '2020-02-27 6:00:00+02');
12INSERT INTO channels_data_location (channel, disk, date_start, date_end) VALUES ('arte', 'd1', '2020-02-27 06:00:00+02', '2020-02-27 7:00:00+02');
13
14CREATE OR REPLACE FUNCTION tmp_set(t_channel text, t_disk text, t_date_start timestamp with time zone, t_date_end timestamp with time zone)
15RETURNS VOID AS $$
16DECLARE
17  start_range channels_data_location;
18  end_range channels_data_location;
19  tmp_merge channels_data_location;
20BEGIN
21    -- memorize row with the start of the new range
22    SELECT INTO start_range * FROM channels_data_location WHERE channel = t_channel AND disk = t_disk AND date_start <= t_date_start AND date_end > t_date_start;
23
24    -- memorize row with the end of the new range
25    SELECT INTO end_range * FROM channels_data_location WHERE channel = t_channel AND disk = t_disk AND date_start < t_date_end AND date_end >= t_date_end;
26
27    -- delete all affected row
28    DELETE FROM channels_data_location WHERE channel = t_channel AND not (date_start >= t_date_end or date_end <= t_date_start);
29
30    -- recreate rows and updating values, then inserting new row
31    IF start_range.date_start <> t_date_start THEN
32      INSERT INTO channels_data_location (channel, disk, date_start, date_end) VALUES (start_range.channel, start_range.disk, start_range.date_start, t_date_start);
33    END IF;
34
35    INSERT INTO channels_data_location (channel, disk, date_start, date_end) VALUES (t_channel, t_disk, t_date_start, t_date_end);
36
37    IF end_range.date_end <> t_date_end THEN
38      INSERT INTO channels_data_location (channel, disk, date_start, date_end) VALUES (end_range.channel, end_range.disk, t_date_end, end_range.date_end);
39    END IF;
40
41    -- check that there is no row that need to be merged
42    SELECT INTO tmp_merge c1.channel, c1.disk, c2.date_start, c2.date_end FROM channels_data_location c1, channels_data_location c2 WHERE c1.channel = t_channel AND c1.disk = t_disk AND c1.channel = c2.channel AND c1.disk = c2.disk AND c1.date_end = c2.date_start;
43    while FOUND loop
44      DELETE FROM channels_data_location WHERE channel = tmp_merge.channel AND disk = tmp_merge.disk AND date_start = tmp_merge.date_start;
45      UPDATE channels_data_location set date_end = tmp_merge.date_end WHERE channel = tmp_merge.channel AND disk = tmp_merge.disk AND date_end = tmp_merge.date_start;
46
47      SELECT INTO tmp_merge c1.channel, c1.disk, c2.date_start, c2.date_end FROM channels_data_location c1, channels_data_location c2 WHERE c1.channel = t_channel AND c1.disk = t_disk AND c1.channel = c2.channel AND c1.disk = c2.disk AND c1.date_end = c2.date_start;
48    end loop;
49  return;
50END;
51$$ LANGUAGE plpgsql;
52
53SELECT tmp_set('arte', 'd1', '2020-02-27 03:30:00+01', '2020-02-27 05:30:00+01');
54SELECT * FROM channels_data_location ;