/* tables */
CREATE TABLE test (
	id INTEGER PRIMARY KEY,
	test TEXT NOT NULL
);

CREATE TABLE join1 (
	id INTEGER PRIMARY KEY,
	text1 TEXT NOT NULL
);

CREATE TABLE join2 (
	id INTEGER PRIMARY KEY,
	text2 TEXT NOT NULL
);

CREATE TABLE join3 (
	id INTEGER PRIMARY KEY,
	text3 TEXT NOT NULL
);

/* view */
CREATE OR REPLACE VIEW view_test AS
	SELECT
		id,
		test,
		text1,
		text2,
		text3
	FROM test
	LEFT JOIN join1 USING (id)
	LEFT JOIN join2 USING (id)
	LEFT JOIN join3 USING (id);

/* data */
INSERT INTO test (id) VALUES ('1','Test 1');
INSERT INTO test (id) VALUES ('2','Test 2');
INSERT INTO test (id) VALUES ('3','Test 3');

INSERT INTO join1 (id,text1) VALUES ('1','Test 1 1');
INSERT INTO join1 (id,text1) VALUES ('2','Test 1 2');
INSERT INTO join1 (id,text1) VALUES ('3','Test 1 3');

INSERT INTO join2 (id,text2) VALUES ('1','Test 2 1');
INSERT INTO join2 (id,text2) VALUES ('2','Test 2 2');
INSERT INTO join2 (id,text2) VALUES ('3','Test 2 3');

INSERT INTO join3 (id,text3) VALUES ('1','Test 3 1');
INSERT INTO join3 (id,text3) VALUES ('2','Test 3 2');
INSERT INTO join3 (id,text3) VALUES ('3','Test 3 3');

/* 1st way of separating updates
   pro: no unnecessary updates on tables
   con: the view gets evaluated 4 times

This was the whole thing being before change.
This can get *really* slow, if the view itself is not the fastest.
   
*/
CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
	DO INSTEAD NOTHING;
CREATE OR REPLACE RULE upd_ AS ON UPDATE TO view_test
	WHERE NEW.test <> OLD.test
	DO UPDATE test
	SET test = NEW.test
	WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_1 AS ON UPDATE TO view_test
	WHERE NEW.text1 <> OLD.text1
	DO UPDATE join1
	SET text1 = NEW.text1
	WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_2 AS ON UPDATE TO view_test
	WHERE NEW.text2 <> OLD.text2
	DO UPDATE join2
	SET text2 = NEW.text2
	WHERE id = OLD.id;
CREATE OR REPLACE RULE upd_3 AS ON UPDATE TO view_test
	WHERE NEW.text3 <> OLD.text3
	DO UPDATE join3
	SET text3 = NEW.text3
	WHERE id = OLD.id;

/* 2nd way of separating updates
   pro: ?
   con: the view gets evaluated 4 times, why?
        unnecessary updates on tables
   
   First approach to reduce execution time of update, but view gets
   also evaluated 4 times (no performance boost).
   Here I discovered the problem that all underlying tables are getting
   the updates, even if the data in that table doesn't change.
   This can hurt you as well, if you log all updates.

*/

DROP RULE upd_ ON view_test;
DROP RULE upd_1 ON view_test;
DROP RULE upd_2 ON view_test;
DROP RULE upd_3 ON view_test;

CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
	DO INSTEAD (
		UPDATE test SET test = NEW.test WHERE id = OLD.id;
		UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
		UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
		UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
	);

/* 3rd way of separating updates
   con: unnecessary updates on tables
   pro: view gets evaluated only 1 time

   Not adressing the problem of unnecessary updates, but the view
   gets only evaluated one time.

*/

CREATE OR REPLACE FUNCTION upd (view_test) RETURNS VOID AS $$
	DECLARE
		NEW ALIAS FOR $1;
	BEGIN
		RAISE NOTICE 'UPDATE';
		UPDATE test SET test = NEW.test WHERE id = OLD.id;
		UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
		UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
		UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
		RETURN;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
	DO INSTEAD SELECT upd (NEW.*);

/* 4th way of doing it 
   pro: view gets evaluated only 1 time
        no unnecessary updates on tables
   con: ??

   Here is the way I solved all my performance problems.
   Only remainig issue: How can I eliminate the response of the select?

*/

CREATE OR REPLACE FUNCTION upd (view_test, view_test) RETURNS VOID AS $$
	DECLARE
		NEW ALIAS FOR $1;
		OLD ALIAS FOR $2;
	BEGIN
		IF (NEW.test <> OLD.test) THEN
			RAISE NOTICE 'UPDATE test';
			UPDATE test SET test = NEW.test WHERE id = OLD.id;
		END IF;
		IF (NEW.text1 <> OLD.text1) THEN
			RAISE NOTICE 'UPDATE join1';
			UPDATE join1 SET text1 = NEW.text1 WHERE id = OLD.id;
		END IF;
		IF (NEW.text2 <> OLD.text2) THEN
			RAISE NOTICE 'UPDATE join2';
			UPDATE join2 SET text2 = NEW.text2 WHERE id = OLD.id;
		END IF;
		IF (NEW.text3 <> OLD.text3) THEN
			RAISE NOTICE 'UPDATE join3';
			UPDATE join3 SET text3 = NEW.text3 WHERE id = OLD.id;
		END IF;
		RETURN;
	END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE RULE upd AS ON UPDATE TO view_test
	DO INSTEAD SELECT upd (NEW.*,OLD.*);