Support triggers on views.

This patch adds the SQL-standard concept of an INSTEAD OF trigger, which
is fired instead of performing a physical insert/update/delete.  The
trigger function is passed the entire old and/or new rows of the view,
and must figure out what to do to the underlying tables to implement
the update.  So this feature can be used to implement updatable views
using trigger programming style rather than rule hacking.

In passing, this patch corrects the names of some columns in the
information_schema.triggers view.  It seems the SQL committee renamed
them somewhere between SQL:99 and SQL:2003.

Dean Rasheed, reviewed by Bernd Helmle; some additional hacking by me.

6 files changed, 88 insertions, 1 deletions

diff --git a/src/pl/tcl/expected/pltcl_queries.out b/src/pl/tcl/expected/pltcl_queries.out
index cdab74a436f..b585736f711 100644
--- a/src/pl/tcl/expected/pltcl_queries.out
+++ b/src/pl/tcl/expected/pltcl_queries.out
@@ -196,6 +196,42 @@ NOTICE:  TG_table_name: trigger_test
 NOTICE:  TG_table_schema: public
 NOTICE:  TG_when: BEFORE
 NOTICE:  args: {23 skidoo}
+insert into trigger_test_view values(2,'insert');
+NOTICE:  NEW: {i: 2, v: insert}
+NOTICE:  OLD: {}
+NOTICE:  TG_level: ROW
+NOTICE:  TG_name: show_trigger_data_view_trig
+NOTICE:  TG_op: INSERT
+NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test_view
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: {INSTEAD OF}
+NOTICE:  args: {24 {skidoo view}}
+update trigger_test_view set v = 'update' where i=1;
+NOTICE:  NEW: {i: 1, v: update}
+NOTICE:  OLD: {i: 1, v: insert}
+NOTICE:  TG_level: ROW
+NOTICE:  TG_name: show_trigger_data_view_trig
+NOTICE:  TG_op: UPDATE
+NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test_view
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: {INSTEAD OF}
+NOTICE:  args: {24 {skidoo view}}
+delete from trigger_test_view;
+NOTICE:  NEW: {}
+NOTICE:  OLD: {i: 1, v: insert}
+NOTICE:  TG_level: ROW
+NOTICE:  TG_name: show_trigger_data_view_trig
+NOTICE:  TG_op: DELETE
+NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test_view
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: {INSTEAD OF}
+NOTICE:  args: {24 {skidoo view}}
 update trigger_test set v = 'update' where i = 1;
 NOTICE:  NEW: {i: 1, v: update}
 NOTICE:  OLD: {i: 1, v: insert}
diff --git a/src/pl/tcl/expected/pltcl_queries_1.out b/src/pl/tcl/expected/pltcl_queries_1.out
index 5788df79df4..e3fd24d6b10 100644
--- a/src/pl/tcl/expected/pltcl_queries_1.out
+++ b/src/pl/tcl/expected/pltcl_queries_1.out
@@ -196,6 +196,42 @@ NOTICE:  TG_table_name: trigger_test
 NOTICE:  TG_table_schema: public
 NOTICE:  TG_when: BEFORE
 NOTICE:  args: {23 skidoo}
+insert into trigger_test_view values(2,'insert');
+NOTICE:  NEW: {i: 2, v: insert}
+NOTICE:  OLD: {}
+NOTICE:  TG_level: ROW
+NOTICE:  TG_name: show_trigger_data_view_trig
+NOTICE:  TG_op: INSERT
+NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test_view
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: {INSTEAD OF}
+NOTICE:  args: {24 {skidoo view}}
+update trigger_test_view set v = 'update' where i=1;
+NOTICE:  NEW: {i: 1, v: update}
+NOTICE:  OLD: {i: 1, v: insert}
+NOTICE:  TG_level: ROW
+NOTICE:  TG_name: show_trigger_data_view_trig
+NOTICE:  TG_op: UPDATE
+NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test_view
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: {INSTEAD OF}
+NOTICE:  args: {24 {skidoo view}}
+delete from trigger_test_view;
+NOTICE:  NEW: {}
+NOTICE:  OLD: {i: 1, v: insert}
+NOTICE:  TG_level: ROW
+NOTICE:  TG_name: show_trigger_data_view_trig
+NOTICE:  TG_op: DELETE
+NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test_view
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: {INSTEAD OF}
+NOTICE:  args: {24 {skidoo view}}
 update trigger_test set v = 'update' where i = 1;
 NOTICE:  NEW: {i: 1, v: update}
 NOTICE:  OLD: {i: 1, v: insert}
diff --git a/src/pl/tcl/expected/pltcl_setup.out b/src/pl/tcl/expected/pltcl_setup.out
index e46c1c3e987..f577e662771 100644
--- a/src/pl/tcl/expected/pltcl_setup.out
+++ b/src/pl/tcl/expected/pltcl_setup.out
@@ -51,6 +51,7 @@ create function check_pkey1_exists(int4, bpchar) returns bool as E'
 -- dump trigger data
 CREATE TABLE trigger_test
     (i int, v text );
+CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
 CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
 
 	if { [info exists TG_relid] } {
@@ -85,6 +86,9 @@ $_$;
 CREATE TRIGGER show_trigger_data_trig 
 BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
+CREATE TRIGGER show_trigger_data_view_trig
+INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
 --
 -- Trigger function on every change to T_pkey1
 --
diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c
index 1c45751d8b3..2f0108c3d4c 100644
--- a/src/pl/tcl/pltcl.c
+++ b/src/pl/tcl/pltcl.c
@@ -889,6 +889,8 @@ pltcl_trigger_handler(PG_FUNCTION_ARGS, bool pltrusted)
 			Tcl_DStringAppendElement(&tcl_cmd, "BEFORE");
 		else if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
 			Tcl_DStringAppendElement(&tcl_cmd, "AFTER");
+		else if (TRIGGER_FIRED_INSTEAD(trigdata->tg_event))
+			Tcl_DStringAppendElement(&tcl_cmd, "INSTEAD OF");
 		else
 			elog(ERROR, "unrecognized WHEN tg_event: %u", trigdata->tg_event);
 
diff --git a/src/pl/tcl/sql/pltcl_queries.sql b/src/pl/tcl/sql/pltcl_queries.sql
index 3a99132ae4d..ee711d5170a 100644
--- a/src/pl/tcl/sql/pltcl_queries.sql
+++ b/src/pl/tcl/sql/pltcl_queries.sql
@@ -73,8 +73,12 @@ select 100 @< 4;
 select * from T_pkey1 order by key1 using @<, key2;
 select * from T_pkey2 order by key1 using @<, key2;
 
-
 -- show dump of trigger data
 insert into trigger_test values(1,'insert');
+
+insert into trigger_test_view values(2,'insert');
+update trigger_test_view set v = 'update' where i=1;
+delete from trigger_test_view;
+
 update trigger_test set v = 'update' where i = 1;
 delete from trigger_test;
diff --git a/src/pl/tcl/sql/pltcl_setup.sql b/src/pl/tcl/sql/pltcl_setup.sql
index 4a581ed8b12..a9370d258dc 100644
--- a/src/pl/tcl/sql/pltcl_setup.sql
+++ b/src/pl/tcl/sql/pltcl_setup.sql
@@ -60,6 +60,8 @@ create function check_pkey1_exists(int4, bpchar) returns bool as E'
 CREATE TABLE trigger_test
     (i int, v text );
 
+CREATE VIEW trigger_test_view AS SELECT * FROM trigger_test;
+
 CREATE FUNCTION trigger_data() returns trigger language pltcl as $_$
 
 	if { [info exists TG_relid] } {
@@ -96,6 +98,9 @@ CREATE TRIGGER show_trigger_data_trig
 BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
 FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
 
+CREATE TRIGGER show_trigger_data_view_trig
+INSTEAD OF INSERT OR UPDATE OR DELETE ON trigger_test_view
+FOR EACH ROW EXECUTE PROCEDURE trigger_data(24,'skidoo view');
 
 --
 -- Trigger function on every change to T_pkey1