-- 
-- Table with Timetravel Defined.
-- No primary key on id--unique index on id, edate==NULL instead.
-- Unique index on all rows with id + timestamps-->PRIMARY KEY for replication
--
DROP TABLE timetravel CASCADE;
CREATE TABLE timetravel (
	id	   TEXT,
	data	TEXT,
	sdate	TIMESTAMP DEFAULT now(),
	edate	TIMESTAMP,
	UNIQUE (id, sdate, edate)
);
CREATE UNIQUE INDEX timetravel_id ON timetravel (id) WHERE edate IS NULL;

--
-- Update Trigger
-- Disallow updating deleted/closed rows.
-- if this a normal update (not a delete (setting of edate)),
--    Save the old values with a new end date first.
-- Allow Updating of the old row with a new sdate to be the new row,
--
CREATE OR REPLACE FUNCTION upd_timetravel()
RETURNS TRIGGER AS
$$
BEGIN
	IF OLD.edate IS NOT NULL THEN -- NEW.edate can be non-null
		RETURN NULL; -- no update
	END IF;
	IF NEW.edate IS NULL THEN
		INSERT INTO timetravel VALUES (OLD.id, OLD.data, OLD.sdate, now());
		NEW.sdate = now();
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER upd_timetravel BEFORE UPDATE ON timetravel
FOR EACH ROW EXECUTE PROCEDURE upd_timetravel();

--
-- Insert Trigger
-- If this is an existing key,
--   cancel the insert and treat as an update,
--   passing the row to the update trigger
-- otherwise insert
--
CREATE OR REPLACE FUNCTION ins_timetravel()
RETURNS TRIGGER AS
$$
DECLARE
	this_id TEXT;
BEGIN
	SELECT INTO this_id 1 FROM timetravel WHERE id = NEW.id AND edate IS NULL;
	IF FOUND AND NEW.edate IS NULL THEN
		UPDATE timetravel SET data=NEW.data WHERE id=NEW.id;
		RETURN NULL; -- no insert
	END IF;
	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER ins_timetravel BEFORE INSERT ON timetravel
FOR EACH ROW EXECUTE PROCEDURE ins_timetravel();

--
-- Delete Rule
-- instead of deletes, close the old row.
-- by updating it. Update trigger will
-- allow this as a plain update.
--
CREATE OR REPLACE RULE del_timetravel 
AS ON DELETE TO timetravel
DO INSTEAD
	(UPDATE timetravel SET edate=now()
	WHERE edate IS NULL AND id=OLD.id;
	);

--
-- Query Definitions
--

--
-- table_now for current values
--
CREATE VIEW timetravel_now AS
   SELECT id, data FROM timetravel WHERE edate IS NULL;

--
-- When queries on timetravel_when( timestamptz )
--
CREATE OR REPLACE FUNCTION timetravel_when (timestamptz)
RETURNS SETOF timetravel_now AS
$$
	SELECT id, data FROM timetravel
   WHERE (SELECT CASE WHEN edate IS NULL
         THEN (sdate <= $1)
         ELSE (sdate <= $1 AND edate > $1)
         END) ;
$$ LANGUAGE 'sql';