DROP TABLE items_118;
CREATE TABLE items_118 (
	item_id		SERIAL,
	start_date	date,
	end_date		date,
PRIMARY KEY (item_id, start_date)
);

INSERT INTO items_118 VALUES (DEFAULT, '1/1/2005', '3/1/2005');
INSERT INTO items_118 VALUES (1, '2/1/2005', '3/15/05');
INSERT INTO items_118 VALUES (1, '3/1/2005', NULL);
INSERT INTO items_118 VALUES (DEFAULT, '4/1/2005', '6/1/2005');
INSERT INTO items_118 VALUES (2, '5/1/2005', NULL);
INSERT INTO items_118 VALUES (2, '6/1/2005', NULL);
INSERT INTO items_118 VALUES (DEFAULT, '7/1/2005', NULL);
INSERT INTO items_118 VALUES (DEFAULT, '8/1/2005', NULL);
INSERT INTO items_118 VALUES (DEFAULT, '8/1/2005', '12/1/05');

SELECT * FROM items_118 ORDER BY item_id, start_date;

-- Find the latest version

SELECT item_id, max(start_date), end_date 
FROM items_118
WHERE end_date IS NULL 
	OR (end_date IS NOT NULL AND end_date >= current_date )
GROUP BY item_id, end_date
ORDER BY item_id;

-- Find overlapping versions

SELECT i.item_id, i.start_date, i.end_date, 
		i2.start_date as overlap_start_date, i2.end_date as overlap_end_date
FROM items_118 i JOIN items_118 i2 USING (item_id)
WHERE i.start_date < i2.start_date AND 
	(i.end_date IS NULL OR i.end_date > i2.start_date);

-- Fix the overlapping items

UPDATE items_118 SET end_date = new_start - interval '1 day'
FROM (
	SELECT i.item_id, i.start_date AS old_start, i.end_date AS old_end,
			i2.start_date AS new_start, i2.end_date as new_end
	FROM items_118 i JOIN items_118 i2 USING (item_id)
	WHERE i.start_date < i2.start_date AND
   (i.end_date IS NULL OR i.end_date > i2.start_date)
) foo
WHERE items_118.item_id = foo.item_id
	AND items_118.start_date = old_start;

-- Check for overlapping items (again)

SELECT i.item_id , i.start_date, i.end_date, i2.start_date, i2.end_date
FROM items_118 i JOIN items_118 i2 USING (item_id)
WHERE i.start_date < i2.start_date AND 
	(i.end_date IS NULL OR i.end_date > i2.start_date);

-- Prevent overlapping items
create or replace function no_overlap()
returns TRIGGER AS
$$
DECLARE
	r record;
BEGIN
	SELECT INTO r 
		i.item_id, i.start_date AS old_start, i.end_date AS old_end, 
		i2.start_date AS new_start, i2.end_date AS new_end
	FROM items_118 i JOIN items_118 i2 USING (item_id)
	WHERE i.item_id = NEW.item_id AND i.start_date < i2.start_date AND
   (i.end_date IS NULL OR i.end_date > i2.start_date);

	IF FOUND AND r.old_end IS NULL OR r.old_end > r.new_start THEN
		UPDATE items_118 SET end_date = r.new_start - interval '1 day'
		WHERE r.item_id=items_118.item_id AND r.old_start = items_118.start_date;
	END IF;

	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER nooverlaps AFTER INSERT ON items_118
FOR EACH ROW EXECUTE PROCEDURE no_overlap();

INSERT INTO items_118 VALUES (default, '1/1/2005', '3/1/2005');
INSERT INTO items_118 VALUES (6, '2/1/2005', '3/15/05');
INSERT INTO items_118 VALUES (6, '3/1/2005', NULL);
INSERT INTO items_118 VALUES (default, '4/1/2005', '6/1/2005');
INSERT INTO items_118 VALUES (7, '5/1/2005', NULL);
INSERT INTO items_118 VALUES (7, '6/1/2005', NULL);
INSERT INTO items_118 VALUES (7, '7/1/2005', NULL);
INSERT INTO items_118 VALUES (default, '7/1/2005', NULL);
INSERT INTO items_118 VALUES (default, '8/1/2005', NULL);
INSERT INTO items_118 VALUES (default, '8/1/2005', '12/1/05');

SELECT * FROM items_118 ORDER BY item_id, start_date;