
-- Trigger function to be added to a table for history generation
-- ==============================================================


DROP FUNCTION history_trigger();
CREATE FUNCTION history_trigger() RETURNS TRIGGER AS $func$
    DECLARE
	hist_table CHAR(100);
	hist_row RECORD;
	ctime TIMESTAMP WITH TIME ZONE;

    BEGIN
	hist_table = TG_ARGV[0];

	ctime = now();

	CREATE TEMPORARY TABLE hist_tmp_table AS SELECT OLD.*,ctime;

	EXECUTE 'INSERT INTO ' || quote_ident(hist_table) ||
		' SELECT * FROM hist_tmp_table;';

	DROP TABLE hist_tmp_table;
	
	IF TG_OP = 'UPDATE' THEN
	    NEW.begin_time = ctime;
	    RETURN NEW;
	END IF;

	RETURN OLD;
    END;
$func$ LANGUAGE 'plpgsql';


-- Add history to a table
-- ======================



DROP FUNCTION add_history_to_table(CHAR);
CREATE FUNCTION add_history_to_table(CHAR) RETURNS VOID AS $func$
    DECLARE
	orig_table ALIAS FOR $1;
	--hist_row RECORD;
    BEGIN

	EXECUTE 'CREATE TABLE ' || quote_ident(orig_table) || '_old ' || 
		'(LIKE ' || quote_ident(orig_table) || 
		', end_time TIMESTAMP WITH TIME ZONE NOT NULL);';

	EXECUTE 'CREATE TRIGGER history_trigger ' ||
		'BEFORE UPDATE OR DELETE ON ' || quote_ident(orig_table) ||
		' FOR EACH ROW EXECUTE PROCEDURE history_trigger(' || 
		quote_ident(orig_table) || '_old);';

	EXECUTE 'CREATE VIEW ' || quote_ident(orig_table) || '_hist AS ' ||
		'SELECT * FROM ' || quote_ident(orig_table) || '_old ' ||
		'UNION ALL ' ||
		'SELECT *,NULL FROM ' || quote_ident(orig_table) || ';';
	
	RETURN;
    END;
$func$ LANGUAGE 'plpgsql';
