tsmultirange.sql

tsmultirange.sql

CREATE EXTENSION btree_gist;

CREATE TABLE prices (
    id serial PRIMARY KEY,

    gas_station_id int,
    gas_price_in_cents int
        CONSTRAINT positive_price CHECK (gas_price_in_cents > 0),

    ts_range tstzmultirange,

    CONSTRAINT overlapping_times EXCLUDE USING GIST (
      gas_station_id WITH =,
      ts_range WITH &&
    ) DEFERRABLE INITIALLY IMMEDIATE
);

INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) VALUES
                   (1, 2000, '{[2000-01-01 00:00, 2000-06-01 00:00)}');
INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) VALUES
                   (1, 2000, '{[2000-06-01 00:00, 2000-12-01 00:00)}');

create or replace function new_gas_price(
    new_gas_station_id integer,
    new_gas_price_in_cents integer,
    timerange tstzmultirange
) returns void language plpgsql as
$$
BEGIN

    UPDATE prices SET ts_range = ts_range - timerange WHERE ts_range && timerange;
    INSERT INTO prices (gas_station_id, gas_price_in_cents, ts_range) VALUES (new_gas_station_id, new_gas_price_in_cents, timerange);
    DELETE FROM prices WHERE ts_range = '{}';

END;
$$;

START TRANSACTION;
SELECT new_gas_price(1, 100, '{[2000-03-01 00:00, 2000-04-01 00:00)}');
COMMIT;

SELECT * FROM prices;

SELECT new_gas_price(1, 600, '{[1999-06-01 00:00, 2001-12-01 00:00)}');

SELECT * FROM prices;

TRUNCATE TABLE prices;