--
-- Showing table logging with a rule and a view.
--

--
-- create a table and a log table to work with
--
create table orig_table (
	col	int,
	col2	int
);
create table log_table (
	col	int,
	col2	int,
	logtime	timestamp
);

insert into orig_table (col, col2) values (1,1);
insert into orig_table (col, col2) values (2,2);
insert into orig_table (col, col2) values (3,3);

--
-- create a (nonsense) view on the original table
--
create view orig_table_view as select col2, col from orig_table;

--
-- create the update rule on the view as UPDATE, INSERT
--
create rule update_orig_table_view AS ON UPDATE TO orig_table_view DO INSTEAD
(
	UPDATE orig_table set col=NEW.col, col2=NEW.col2 where col=OLD.col;
   INSERT INTO log_table (col, col2, logtime)
	VALUES (OLD.col, OLD.col2, now()); 
);

--
-- Try it out
--
update orig_table_view
	set col2=11 where col = 1;

select * from orig_table;
select * from log_table;

\echo Notice that the value 11 was logged instead of the value 1
\echo

--
-- Change the rule to be INSERT, UPDATE
--
drop rule update_orig_table_view on orig_table_view;
create rule update_orig_table_view AS ON UPDATE TO orig_table_view DO INSTEAD
(
   INSERT INTO log_table (col, col2, logtime) 
   	VALUES (OLD.col, OLD.col2, now());
   UPDATE orig_table set col=NEW.col, col2=NEW.col2 where col=OLD.col;
);

--
-- Try it out
--
update orig_table_view
	set col2=22 where col = 2;

select * from orig_table;
select * from log_table;

\echo This should be correct
\echo

--
-- Now let us try a trigger
--

--
-- get rid of the old view update rule and use a simpler one
--
drop rule update_orig_table_view ON orig_table_view;
create rule update_orig_table_view AS ON UPDATE TO orig_table_view DO INSTEAD
   UPDATE orig_table set col=NEW.col, col2=NEW.col2 where col=OLD.col;

--
-- Create a trigger function to do the logging
--
create or replace function logorig()
returns TRIGGER as
'
BEGIN
   INSERT INTO log_table (col, col2, logtime)
   	VALUES (OLD.col, OLD.col2, now());
	RETURN NEW;
END;
' language 'plpgsql';

--
-- Attach the trigger function to the update of the original table
--
create trigger updtrig_orig_table BEFORE UPDATE
	ON orig_table for each row
	EXECUTE PROCEDURE logorig();

--
-- Try it out
--
update orig_table_view
	set col2=33 where col = 3;

select * from orig_table;
select * from log_table;

\echo This also should  be correct
\echo