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.

1 files changed, 189 insertions, 63 deletions

diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index e917554f5c5..31b25957b60 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -14,6 +14,7 @@
 #include "postgres.h"
 
 #include "access/heapam.h"
+#include "access/sysattr.h"
 #include "catalog/pg_type.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
@@ -43,14 +44,15 @@ static Query *rewriteRuleAction(Query *parsetree,
 				  CmdType event,
 				  bool *returning_flag);
 static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_index);
-static void rewriteTargetList(Query *parsetree, Relation target_relation,
-				  List **attrno_list);
+static void rewriteTargetListIU(Query *parsetree, Relation target_relation,
+					List **attrno_list);
 static TargetEntry *process_matched_tle(TargetEntry *src_tle,
 					TargetEntry *prior_tle,
 					const char *attrName);
 static Node *get_assignment_input(Node *node);
 static void rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation,
 				 List *attrnos);
+static void rewriteTargetListUD(Query *parsetree, Relation target_relation);
 static void markQueryForLocking(Query *qry, Node *jtnode,
 					bool forUpdate, bool noWait, bool pushedDown);
 static List *matchLocks(CmdType event, RuleLock *rulelocks,
@@ -554,7 +556,7 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
 
 
 /*
- * rewriteTargetList - rewrite INSERT/UPDATE targetlist into standard form
+ * rewriteTargetListIU - rewrite INSERT/UPDATE targetlist into standard form
  *
  * This has the following responsibilities:
  *
@@ -566,7 +568,14 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
  * and UPDATE, replace explicit DEFAULT specifications with column default
  * expressions.
  *
- * 2. Merge multiple entries for the same target attribute, or declare error
+ * 2. For an UPDATE on a view, add tlist entries for any unassigned-to
+ * attributes, assigning them their old values.  These will later get
+ * expanded to the output values of the view.  (This is equivalent to what
+ * the planner's expand_targetlist() will do for UPDATE on a regular table,
+ * but it's more convenient to do it here while we still have easy access
+ * to the view's original RT index.)
+ *
+ * 3. Merge multiple entries for the same target attribute, or declare error
  * if we can't.  Multiple entries are only allowed for INSERT/UPDATE of
  * portions of an array or record field, for example
  *			UPDATE table SET foo[2] = 42, foo[4] = 43;
@@ -574,13 +583,13 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
  * the expression we want to produce in this case is like
  *		foo = array_set(array_set(foo, 2, 42), 4, 43)
  *
- * 3. Sort the tlist into standard order: non-junk fields in order by resno,
+ * 4. Sort the tlist into standard order: non-junk fields in order by resno,
  * then junk fields (these in no particular order).
  *
- * We must do items 1 and 2 before firing rewrite rules, else rewritten
- * references to NEW.foo will produce wrong or incomplete results.	Item 3
+ * We must do items 1,2,3 before firing rewrite rules, else rewritten
+ * references to NEW.foo will produce wrong or incomplete results.	Item 4
  * is not needed for rewriting, but will be needed by the planner, and we
- * can do it essentially for free while handling items 1 and 2.
+ * can do it essentially for free while handling the other items.
  *
  * If attrno_list isn't NULL, we return an additional output besides the
  * rewritten targetlist: an integer list of the assigned-to attnums, in
@@ -588,8 +597,8 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
  * processing VALUES RTEs.
  */
 static void
-rewriteTargetList(Query *parsetree, Relation target_relation,
-				  List **attrno_list)
+rewriteTargetListIU(Query *parsetree, Relation target_relation,
+					List **attrno_list)
 {
 	CmdType		commandType = parsetree->commandType;
 	TargetEntry **new_tles;
@@ -724,6 +733,27 @@ rewriteTargetList(Query *parsetree, Relation target_relation,
 										  false);
 		}
 
+		/*
+		 * For an UPDATE on a view, provide a dummy entry whenever there is
+		 * no explicit assignment.
+		 */
+		if (new_tle == NULL && commandType == CMD_UPDATE &&
+			target_relation->rd_rel->relkind == RELKIND_VIEW)
+		{
+			Node	   *new_expr;
+
+			new_expr = (Node *) makeVar(parsetree->resultRelation,
+										attrno,
+										att_tup->atttypid,
+										att_tup->atttypmod,
+										0);
+
+			new_tle = makeTargetEntry((Expr *) new_expr,
+									  attrno,
+									  pstrdup(NameStr(att_tup->attname)),
+									  false);
+		}
+
 		if (new_tle)
 			new_tlist = lappend(new_tlist, new_tle);
 	}
@@ -985,8 +1015,8 @@ searchForDefault(RangeTblEntry *rte)
 /*
  * When processing INSERT ... VALUES with a VALUES RTE (ie, multiple VALUES
  * lists), we have to replace any DEFAULT items in the VALUES lists with
- * the appropriate default expressions.  The other aspects of rewriteTargetList
- * need be applied only to the query's targetlist proper.
+ * the appropriate default expressions.  The other aspects of targetlist
+ * rewriting need be applied only to the query's targetlist proper.
  *
  * Note that we currently can't support subscripted or field assignment
  * in the multi-VALUES case.  The targetlist will contain simple Vars
@@ -1068,6 +1098,62 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation target_relation, List *attrnos)
 
 
 /*
+ * rewriteTargetListUD - rewrite UPDATE/DELETE targetlist as needed
+ *
+ * This function adds a "junk" TLE that is needed to allow the executor to
+ * find the original row for the update or delete.  When the target relation
+ * is a regular table, the junk TLE emits the ctid attribute of the original
+ * row.  When the target relation is a view, there is no ctid, so we instead
+ * emit a whole-row Var that will contain the "old" values of the view row.
+ *
+ * For UPDATE queries, this is applied after rewriteTargetListIU.  The
+ * ordering isn't actually critical at the moment.
+ */
+static void
+rewriteTargetListUD(Query *parsetree, Relation target_relation)
+{
+	Var		   *var;
+	const char *attrname;
+	TargetEntry *tle;
+
+	if (target_relation->rd_rel->relkind == RELKIND_RELATION)
+	{
+		/*
+		 * Emit CTID so that executor can find the row to update or delete.
+		 */
+		var = makeVar(parsetree->resultRelation,
+					  SelfItemPointerAttributeNumber,
+					  TIDOID,
+					  -1,
+					  0);
+
+		attrname = "ctid";
+	}
+	else
+	{
+		/*
+		 * Emit whole-row Var so that executor will have the "old" view row
+		 * to pass to the INSTEAD OF trigger.
+		 */
+		var = makeVar(parsetree->resultRelation,
+					  InvalidAttrNumber,
+					  RECORDOID,
+					  -1,
+					  0);
+
+		attrname = "wholerow";
+	}
+
+	tle = makeTargetEntry((Expr *) var,
+						  list_length(parsetree->targetList) + 1,
+						  pstrdup(attrname),
+						  true);
+
+	parsetree->targetList = lappend(parsetree->targetList, tle);
+}
+
+
+/*
  * matchLocks -
  *	  match the list of locks and returns the matching rules
  */
@@ -1157,6 +1243,67 @@ ApplyRetrieveRule(Query *parsetree,
 	if (!relation_level)
 		elog(ERROR, "cannot handle per-attribute ON SELECT rule");
 
+	if (rt_index == parsetree->resultRelation)
+	{
+		/*
+		 * We have a view as the result relation of the query, and it wasn't
+		 * rewritten by any rule.  This case is supported if there is an
+		 * INSTEAD OF trigger that will trap attempts to insert/update/delete
+		 * view rows.  The executor will check that; for the moment just plow
+		 * ahead.  We have two cases:
+		 *
+		 * For INSERT, we needn't do anything.  The unmodified RTE will serve
+		 * fine as the result relation.
+		 *
+		 * For UPDATE/DELETE, we need to expand the view so as to have source
+		 * data for the operation.  But we also need an unmodified RTE to
+		 * serve as the target.  So, copy the RTE and add the copy to the
+		 * rangetable.  Note that the copy does not get added to the jointree.
+		 * Also note that there's a hack in fireRIRrules to avoid calling
+		 * this function again when it arrives at the copied RTE.
+		 */
+		if (parsetree->commandType == CMD_INSERT)
+			return parsetree;
+		else if (parsetree->commandType == CMD_UPDATE ||
+				 parsetree->commandType == CMD_DELETE)
+		{
+			RangeTblEntry *newrte;
+
+			rte = rt_fetch(rt_index, parsetree->rtable);
+			newrte = copyObject(rte);
+			parsetree->rtable = lappend(parsetree->rtable, newrte);
+			parsetree->resultRelation = list_length(parsetree->rtable);
+
+			/*
+			 * There's no need to do permissions checks twice, so wipe out
+			 * the permissions info for the original RTE (we prefer to keep
+			 * the bits set on the result RTE).
+			 */
+			rte->requiredPerms = 0;
+			rte->checkAsUser = InvalidOid;
+			rte->selectedCols = NULL;
+			rte->modifiedCols = NULL;
+
+			/*
+			 * For the most part, Vars referencing the view should remain as
+			 * they are, meaning that they implicitly represent OLD values.
+			 * But in the RETURNING list if any, we want such Vars to
+			 * represent NEW values, so change them to reference the new RTE.
+			 *
+			 * Since ChangeVarNodes scribbles on the tree in-place, copy the
+			 * RETURNING list first for safety.
+			 */
+			parsetree->returningList = copyObject(parsetree->returningList);
+			ChangeVarNodes((Node *) parsetree->returningList, rt_index,
+						   parsetree->resultRelation, 0);
+
+			/* Now, continue with expanding the original view RTE */
+		}
+		else
+			elog(ERROR, "unrecognized commandType: %d",
+				 (int) parsetree->commandType);
+	}
+
 	/*
 	 * If FOR UPDATE/SHARE of view, be sure we get right initial lock on the
 	 * relations it references.
@@ -1178,8 +1325,8 @@ ApplyRetrieveRule(Query *parsetree,
 	rule_action = fireRIRrules(rule_action, activeRIRs, forUpdatePushedDown);
 
 	/*
-	 * VIEWs are really easy --- just plug the view query in as a subselect,
-	 * replacing the relation's original RTE.
+	 * Now, plug the view query in as a subselect, replacing the relation's
+	 * original RTE.
 	 */
 	rte = rt_fetch(rt_index, parsetree->rtable);
 
@@ -1320,6 +1467,7 @@ fireRIRonSubLink(Node *node, List *activeRIRs)
 static Query *
 fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
 {
+	int			origResultRelation = parsetree->resultRelation;
 	int			rt_index;
 	ListCell   *lc;
 
@@ -1372,6 +1520,14 @@ fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown)
 			continue;
 
 		/*
+		 * Also, if this is a new result relation introduced by
+		 * ApplyRetrieveRule, we don't want to do anything more with it.
+		 */
+		if (rt_index == parsetree->resultRelation &&
+			rt_index != origResultRelation)
+			continue;
+
+		/*
 		 * We can use NoLock here since either the parser or
 		 * AcquireRewriteLocks should have locked the rel already.
 		 */
@@ -1634,7 +1790,8 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
 	List	   *rewritten = NIL;
 
 	/*
-	 * If the statement is an update, insert or delete - fire rules on it.
+	 * If the statement is an insert, update, or delete, adjust its targetlist
+	 * as needed, and then fire INSERT/UPDATE/DELETE rules on it.
 	 *
 	 * SELECT rules are handled later when we have all the queries that should
 	 * get executed.  Also, utilities aren't rewritten at all (do we still
@@ -1659,13 +1816,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
 		rt_entry_relation = heap_open(rt_entry->relid, NoLock);
 
 		/*
-		 * If it's an INSERT or UPDATE, rewrite the targetlist into standard
-		 * form.  This will be needed by the planner anyway, and doing it now
-		 * ensures that any references to NEW.field will behave sanely.
+		 * Rewrite the targetlist as needed for the command type.
 		 */
-		if (event == CMD_UPDATE)
-			rewriteTargetList(parsetree, rt_entry_relation, NULL);
-		else if (event == CMD_INSERT)
+		if (event == CMD_INSERT)
 		{
 			RangeTblEntry *values_rte = NULL;
 
@@ -1692,16 +1845,27 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
 				List	   *attrnos;
 
 				/* Process the main targetlist ... */
-				rewriteTargetList(parsetree, rt_entry_relation, &attrnos);
+				rewriteTargetListIU(parsetree, rt_entry_relation, &attrnos);
 				/* ... and the VALUES expression lists */
 				rewriteValuesRTE(values_rte, rt_entry_relation, attrnos);
 			}
 			else
 			{
 				/* Process just the main targetlist */
-				rewriteTargetList(parsetree, rt_entry_relation, NULL);
+				rewriteTargetListIU(parsetree, rt_entry_relation, NULL);
 			}
 		}
+		else if (event == CMD_UPDATE)
+		{
+			rewriteTargetListIU(parsetree, rt_entry_relation, NULL);
+			rewriteTargetListUD(parsetree, rt_entry_relation);
+		}
+		else if (event == CMD_DELETE)
+		{
+			rewriteTargetListUD(parsetree, rt_entry_relation);
+		}
+		else
+			elog(ERROR, "unrecognized commandType: %d", (int) event);
 
 		/*
 		 * Collect and apply the appropriate rules.
@@ -1850,7 +2014,7 @@ List *
 QueryRewrite(Query *parsetree)
 {
 	List	   *querylist;
-	List	   *results = NIL;
+	List	   *results;
 	ListCell   *l;
 	CmdType		origCmdType;
 	bool		foundOriginalQuery;
@@ -1868,50 +2032,12 @@ QueryRewrite(Query *parsetree)
 	 *
 	 * Apply all the RIR rules on each query
 	 */
+	results = NIL;
 	foreach(l, querylist)
 	{
 		Query	   *query = (Query *) lfirst(l);
 
 		query = fireRIRrules(query, NIL, false);
-
-		/*
-		 * If the query target was rewritten as a view, complain.
-		 */
-		if (query->resultRelation)
-		{
-			RangeTblEntry *rte = rt_fetch(query->resultRelation,
-										  query->rtable);
-
-			if (rte->rtekind == RTE_SUBQUERY)
-			{
-				switch (query->commandType)
-				{
-					case CMD_INSERT:
-						ereport(ERROR,
-								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-								 errmsg("cannot insert into a view"),
-								 errhint("You need an unconditional ON INSERT DO INSTEAD rule.")));
-						break;
-					case CMD_UPDATE:
-						ereport(ERROR,
-								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-								 errmsg("cannot update a view"),
-								 errhint("You need an unconditional ON UPDATE DO INSTEAD rule.")));
-						break;
-					case CMD_DELETE:
-						ereport(ERROR,
-								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-								 errmsg("cannot delete from a view"),
-								 errhint("You need an unconditional ON DELETE DO INSTEAD rule.")));
-						break;
-					default:
-						elog(ERROR, "unrecognized commandType: %d",
-							 (int) query->commandType);
-						break;
-				}
-			}
-		}
-
 		results = lappend(results, query);
 	}