CREATE OR REPLACE VIEW, CREATE OR REPLACE RULE.

Gavin Sherry, Neil Conway, and Tom Lane all got their hands dirty
on this one ...

12 files changed, 290 insertions, 70 deletions

diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c
index c635a28571d..46cdf0aca0b 100644
--- a/src/backend/commands/view.c
+++ b/src/backend/commands/view.c
@@ -6,43 +6,43 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$Id: view.c,v 1.68 2002/08/30 19:23:19 tgl Exp $
+ *
+ * IDENTIFICATION
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/view.c,v 1.69 2002/09/02 02:13:01 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
 #include "postgres.h"
 
-#include "access/xact.h"
+#include "access/heapam.h"
 #include "catalog/dependency.h"
-#include "catalog/heap.h"
 #include "catalog/namespace.h"
 #include "commands/tablecmds.h"
 #include "commands/view.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "parser/parse_relation.h"
-#include "parser/parse_type.h"
 #include "rewrite/rewriteDefine.h"
 #include "rewrite/rewriteManip.h"
-#include "rewrite/rewriteRemove.h"
 #include "rewrite/rewriteSupport.h"
-#include "utils/syscache.h"
+#include "utils/acl.h"
+#include "utils/lsyscache.h"
 
 
 /*---------------------------------------------------------------------
  * DefineVirtualRelation
  *
- * Create the "view" relation.
- * `DefineRelation' does all the work, we just provide the correct
- * arguments!
- *
- * If the relation already exists, then 'DefineRelation' will abort
- * the xact...
+ * Create the "view" relation. `DefineRelation' does all the work,
+ * we just provide the correct arguments ... at least when we're
+ * creating a view.  If we're updating an existing view, we have to
+ * work harder.
  *---------------------------------------------------------------------
  */
 static Oid
-DefineVirtualRelation(const RangeVar *relation, List *tlist)
+DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace)
 {
+	Oid			viewOid,
+				namespaceId;
 	CreateStmt *createStmt = makeNode(CreateStmt);
 	List	   *attrList,
 			   *t;
@@ -52,7 +52,7 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist)
 	 * the (non-junk) targetlist items from the view's SELECT list.
 	 */
 	attrList = NIL;
-	foreach(t, tlist)
+	foreach (t, tlist)
 	{
 		TargetEntry *entry = lfirst(t);
 		Resdom	   *res = entry->resdom;
@@ -83,23 +83,74 @@ DefineVirtualRelation(const RangeVar *relation, List *tlist)
 		elog(ERROR, "attempted to define virtual relation with no attrs");
 
 	/*
-	 * now create the parameters for keys/inheritance etc. All of them are
-	 * nil...
+	 * Check to see if we want to replace an existing view.
 	 */
-	createStmt->relation = (RangeVar *) relation;
-	createStmt->tableElts = attrList;
-	createStmt->inhRelations = NIL;
-	createStmt->constraints = NIL;
-	createStmt->hasoids = false;
+	namespaceId = RangeVarGetCreationNamespace(relation);
+	viewOid = get_relname_relid(relation->relname, namespaceId);
 
-	/*
-	 * finally create the relation...
-	 */
-	return DefineRelation(createStmt, RELKIND_VIEW);
+	if (OidIsValid(viewOid) && replace)
+	{
+		Relation	rel;
+		TupleDesc	descriptor;
+
+		/*
+		 * Yes.  Get exclusive lock on the existing view ...
+		 */
+		rel = relation_open(viewOid, AccessExclusiveLock);
+
+		/*
+		 * Make sure it *is* a view, and do permissions checks.
+		 */
+		if (rel->rd_rel->relkind != RELKIND_VIEW)
+			elog(ERROR, "%s is not a view",
+				 RelationGetRelationName(rel));
+
+		if (!pg_class_ownercheck(viewOid, GetUserId()))
+			aclcheck_error(ACLCHECK_NOT_OWNER, RelationGetRelationName(rel));
+
+		/*
+		 * Create a tuple descriptor to compare against the existing view,
+		 * and verify it matches.
+		 *
+		 * XXX the error message is a bit cheesy here: would be useful to
+		 * give a more specific complaint about the difference in the
+		 * descriptors.  No time for it at the moment though.
+		 */
+	    descriptor = BuildDescForRelation(attrList);
+		if (!equalTupleDescs(descriptor, rel->rd_att))
+			elog(ERROR, "Cannot change column set of existing view %s",
+				 RelationGetRelationName(rel));
+
+		/*
+		 * Seems okay, so return the OID of the pre-existing view.
+		 */
+		relation_close(rel, NoLock); /* keep the lock! */
+
+		return viewOid;
+	}
+	else
+	{
+		/*
+		 * now create the parameters for keys/inheritance etc. All of them are
+		 * nil...
+		 */
+		createStmt->relation = (RangeVar *) relation;
+		createStmt->tableElts = attrList;
+		createStmt->inhRelations = NIL;
+		createStmt->constraints = NIL;
+		createStmt->hasoids = false;
+	
+		/*
+		 * finally create the relation (this will error out if there's
+		 * an existing view, so we don't need more code to complain
+		 * if "replace" is false).
+		 */
+		return DefineRelation(createStmt, RELKIND_VIEW);
+	}
 }
 
 static RuleStmt *
-FormViewRetrieveRule(const RangeVar *view, Query *viewParse)
+FormViewRetrieveRule(const RangeVar *view, Query *viewParse, bool replace)
 {
 	RuleStmt   *rule;
 
@@ -114,12 +165,13 @@ FormViewRetrieveRule(const RangeVar *view, Query *viewParse)
 	rule->event = CMD_SELECT;
 	rule->instead = true;
 	rule->actions = makeList1(viewParse);
+	rule->replace = replace;
 
 	return rule;
 }
 
 static void
-DefineViewRules(const RangeVar *view, Query *viewParse)
+DefineViewRules(const RangeVar *view, Query *viewParse, bool replace)
 {
 	RuleStmt   *retrieve_rule;
 
@@ -129,10 +181,9 @@ DefineViewRules(const RangeVar *view, Query *viewParse)
 	RuleStmt   *delete_rule;
 #endif
 
-	retrieve_rule = FormViewRetrieveRule(view, viewParse);
+	retrieve_rule = FormViewRetrieveRule(view, viewParse, replace);
 
 #ifdef NOTYET
-
 	replace_rule = FormViewReplaceRule(view, viewParse);
 	append_rule = FormViewAppendRule(view, viewParse);
 	delete_rule = FormViewDeleteRule(view, viewParse);
@@ -221,16 +272,18 @@ UpdateRangeTableOfViewParse(Oid viewOid, Query *viewParse)
  *-------------------------------------------------------------------
  */
 void
-DefineView(const RangeVar *view, Query *viewParse)
+DefineView(const RangeVar *view, Query *viewParse, bool replace)
 {
 	Oid			viewOid;
 
 	/*
 	 * Create the view relation
 	 *
-	 * NOTE: if it already exists, the xact will be aborted.
+	 * NOTE: if it already exists and replace is false, the xact will 
+	 * be aborted.
 	 */
-	viewOid = DefineVirtualRelation(view, viewParse->targetList);
+
+	viewOid = DefineVirtualRelation(view, viewParse->targetList, replace);
 
 	/*
 	 * The relation we have just created is not visible to any other
@@ -248,7 +301,7 @@ DefineView(const RangeVar *view, Query *viewParse)
 	/*
 	 * Now create the rules associated with the view.
 	 */
-	DefineViewRules(view, viewParse);
+	DefineViewRules(view, viewParse, replace);
 }
 
 /*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1938e7f4738..8a69af3de57 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -15,7 +15,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.209 2002/08/31 22:10:43 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.210 2002/09/02 02:13:01 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -2173,6 +2173,7 @@ _copyRuleStmt(RuleStmt *from)
 	Node_Copy(from, newnode, whereClause);
 	newnode->event = from->event;
 	newnode->instead = from->instead;
+	newnode->replace = from->replace;
 	Node_Copy(from, newnode, actions);
 
 	return newnode;
@@ -2238,6 +2239,7 @@ _copyViewStmt(ViewStmt *from)
 	Node_Copy(from, newnode, view);
 	Node_Copy(from, newnode, aliases);
 	Node_Copy(from, newnode, query);
+	newnode->replace = from->replace;
 
 	return newnode;
 }
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 10b8e79933d..7f677c0837c 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -20,7 +20,7 @@
  * Portions Copyright (c) 1994, Regents of the University of California
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.157 2002/08/31 22:10:43 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.158 2002/09/02 02:13:01 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1003,6 +1003,8 @@ _equalRuleStmt(RuleStmt *a, RuleStmt *b)
 		return false;
 	if (a->instead != b->instead)
 		return false;
+	if (a->replace != b->replace)
+		return false;
 	if (!equal(a->actions, b->actions))
 		return false;
 
@@ -1067,6 +1069,8 @@ _equalViewStmt(ViewStmt *a, ViewStmt *b)
 		return false;
 	if (!equal(a->query, b->query))
 		return false;
+	if (a->replace != b->replace)
+		return false;
 
 	return true;
 }
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 463a8d5a4e5..674a6d0d920 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.364 2002/08/29 00:17:04 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.365 2002/09/02 02:13:01 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -3346,18 +3346,19 @@ opt_column: COLUMN									{ $$ = COLUMN; }
  *
  *****************************************************************************/
 
-RuleStmt:	CREATE RULE name AS
+RuleStmt:	CREATE opt_or_replace RULE name AS
 			{ QueryIsRule=TRUE; }
 			ON event TO qualified_name where_clause
 			DO opt_instead RuleActionList
 				{
 					RuleStmt *n = makeNode(RuleStmt);
-					n->relation = $9;
-					n->rulename = $3;
-					n->whereClause = $10;
-					n->event = $7;
-					n->instead = $12;
-					n->actions = $13;
+					n->replace = $2;
+					n->relation = $10;
+					n->rulename = $4;
+					n->whereClause = $11;
+					n->event = $8;
+					n->instead = $13;
+					n->actions = $14;
 					$$ = (Node *)n;
 					QueryIsRule=FALSE;
 				}
@@ -3537,12 +3538,14 @@ opt_trans:	WORK									{}
  *
  *****************************************************************************/
 
-ViewStmt:	CREATE VIEW qualified_name opt_column_list AS SelectStmt
+ViewStmt:	CREATE opt_or_replace VIEW qualified_name opt_column_list 
+				AS SelectStmt
 				{
 					ViewStmt *n = makeNode(ViewStmt);
-					n->view = $3;
-					n->aliases = $4;
-					n->query = (Query *) $6;
+					n->replace = $2;
+					n->view = $4;
+					n->aliases = $5;
+					n->query = (Query *) $7;
 					$$ = (Node *)n;
 				}
 		;
diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c
index 80952d7ddc4..f2761894260 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.77 2002/08/05 03:29:17 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.78 2002/09/02 02:13:01 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -48,24 +48,23 @@ InsertRule(char *rulname,
 		   AttrNumber evslot_index,
 		   bool evinstead,
 		   Node *event_qual,
-		   List *action)
+		   List *action,
+		   bool replace)
 {
 	char	   *evqual = nodeToString(event_qual);
 	char	   *actiontree = nodeToString((Node *) action);
 	int			i;
 	Datum		values[Natts_pg_rewrite];
 	char		nulls[Natts_pg_rewrite];
+	char		replaces[Natts_pg_rewrite];
 	NameData	rname;
 	Relation	pg_rewrite_desc;
-	TupleDesc	tupDesc;
-	HeapTuple	tup;
+	HeapTuple	tup,
+				oldtup;
 	Oid			rewriteObjectId;
 	ObjectAddress	myself,
 					referenced;
-
-	if (IsDefinedRewriteRule(eventrel_oid, rulname))
-		elog(ERROR, "Attempt to insert rule \"%s\" failed: already exists",
-			 rulname);
+	bool		is_update = false;
 
 	/*
 	 * Set up *nulls and *values arrays
@@ -83,22 +82,61 @@ InsertRule(char *rulname,
 	values[i++] = DirectFunctionCall1(textin, CStringGetDatum(actiontree));	/* ev_action */
 
 	/*
-	 * create a new pg_rewrite tuple
+	 * Ready to store new pg_rewrite tuple
 	 */
 	pg_rewrite_desc = heap_openr(RewriteRelationName, RowExclusiveLock);
 
-	tupDesc = pg_rewrite_desc->rd_att;
+	/*
+	 * Check to see if we are replacing an existing tuple
+	 */
+	oldtup = SearchSysCache(RULERELNAME,
+							ObjectIdGetDatum(eventrel_oid),
+							PointerGetDatum(rulname),
+							0, 0);
+
+	if (HeapTupleIsValid(oldtup))
+	{
+		if (!replace)
+			elog(ERROR,"Attempt to insert rule \"%s\" failed: already exists",
+				 rulname);
+
+		/*
+		 * When replacing, we don't need to replace every attribute
+		 */
+		MemSet(replaces, ' ', sizeof(replaces));
+		replaces[Anum_pg_rewrite_ev_attr - 1] = 'r';
+		replaces[Anum_pg_rewrite_ev_type - 1] = 'r';
+		replaces[Anum_pg_rewrite_is_instead - 1] = 'r';
+		replaces[Anum_pg_rewrite_ev_qual - 1] = 'r';
+		replaces[Anum_pg_rewrite_ev_action - 1] = 'r';
+
+		tup = heap_modifytuple(oldtup, pg_rewrite_desc,
+							   values, nulls, replaces);
+
+		simple_heap_update(pg_rewrite_desc, &tup->t_self, tup);
 
-	tup = heap_formtuple(tupDesc,
-						 values,
-						 nulls);
+		ReleaseSysCache(oldtup);
+
+		rewriteObjectId = HeapTupleGetOid(tup);
+		is_update = true;
+	}
+	else
+	{
+		tup = heap_formtuple(pg_rewrite_desc->rd_att, values, nulls);
 
-	rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup);
+		rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup);
+	}
 
+	/* Need to update indexes in either case */
 	CatalogUpdateIndexes(pg_rewrite_desc, tup);
 
 	heap_freetuple(tup);
 
+	/* If replacing, get rid of old dependencies and make new ones */
+	if (is_update)
+		deleteDependencyRecordsFor(RelationGetRelid(pg_rewrite_desc),
+								   rewriteObjectId);
+
 	/*
 	 * Install dependency on rule's relation to ensure it will go away
 	 * on relation deletion.  If the rule is ON SELECT, make the dependency
@@ -114,13 +152,14 @@ InsertRule(char *rulname,
 	referenced.objectSubId = 0;
 
 	recordDependencyOn(&myself, &referenced,
-		(evtype == CMD_SELECT) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO);
+					   (evtype == CMD_SELECT) ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO);
 
 	/*
 	 * Also install dependencies on objects referenced in action and qual.
 	 */
 	recordDependencyOnExpr(&myself, (Node *) action, NIL,
 						   DEPENDENCY_NORMAL);
+
 	if (event_qual != NULL)
 	{
 		/* Find query containing OLD/NEW rtable entries */
@@ -143,6 +182,7 @@ DefineQueryRewrite(RuleStmt *stmt)
 	Node	   *event_qual = stmt->whereClause;
 	CmdType		event_type = stmt->event;
 	bool		is_instead = stmt->instead;
+	bool		replace = stmt->replace;
 	List	   *action = stmt->actions;
 	Relation	event_relation;
 	Oid			ev_relid;
@@ -232,7 +272,7 @@ DefineQueryRewrite(RuleStmt *stmt)
 		 * event relation, ...
 		 */
 		i = 0;
-		foreach(tllist, query->targetList)
+		foreach (tllist, query->targetList)
 		{
 			TargetEntry *tle = (TargetEntry *) lfirst(tllist);
 			Resdom	   *resdom = tle->resdom;
@@ -282,7 +322,7 @@ DefineQueryRewrite(RuleStmt *stmt)
 		/*
 		 * ... there must not be another ON SELECT rule already ...
 		 */
-		if (event_relation->rd_rules != NULL)
+		if (!replace && event_relation->rd_rules != NULL)
 		{
 			for (i = 0; i < event_relation->rd_rules->numLocks; i++)
 			{
@@ -364,7 +404,8 @@ DefineQueryRewrite(RuleStmt *stmt)
 							event_attno,
 							is_instead,
 							event_qual,
-							action);
+							action,
+							replace);
 
 		/*
 		 * Set pg_class 'relhasrules' field TRUE for event relation. If
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 17a2533b775..40dec9d68b8 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.175 2002/08/30 19:23:20 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/tcop/utility.c,v 1.176 2002/09/02 02:13:01 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -595,7 +595,7 @@ ProcessUtility(Node *parsetree,
 			{
 				ViewStmt   *stmt = (ViewStmt *) parsetree;
 
-				DefineView(stmt->view, stmt->query);
+				DefineView(stmt->view, stmt->query, stmt->replace);
 			}
 			break;
 
diff --git a/src/include/commands/view.h b/src/include/commands/view.h
index 3603f2a4fab..e476d8224e2 100644
--- a/src/include/commands/view.h
+++ b/src/include/commands/view.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: view.h,v 1.16 2002/07/01 15:27:56 tgl Exp $
+ * $Id: view.h,v 1.17 2002/09/02 02:13:02 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -16,7 +16,7 @@
 
 #include "nodes/parsenodes.h"
 
-extern void DefineView(const RangeVar *view, Query *view_parse);
+extern void DefineView(const RangeVar *view, Query *view_parse, bool replace);
 extern void RemoveView(const RangeVar *view, DropBehavior behavior);
 
 #endif   /* VIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a426aeba020..be8b7fe7d1a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: parsenodes.h,v 1.204 2002/08/31 22:10:47 tgl Exp $
+ * $Id: parsenodes.h,v 1.205 2002/09/02 02:13:02 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1371,6 +1371,7 @@ typedef struct RuleStmt
 	CmdType		event;			/* SELECT, INSERT, etc */
 	bool		instead;		/* is a 'do instead'? */
 	List	   *actions;		/* the action statements */
+	bool		replace;		/* OR REPLACE */
 } RuleStmt;
 
 /* ----------------------
@@ -1436,6 +1437,7 @@ typedef struct ViewStmt
 	RangeVar   *view;			/* the view to be created */
 	List	   *aliases;		/* target column names */
 	Query	   *query;			/* the SQL statement */
+	bool		replace;		/* replace an existing view? */
 } ViewStmt;
 
 /* ----------------------
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index e398440bce8..ade98e6c311 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -15,3 +15,43 @@ CREATE VIEW iexit AS
 CREATE VIEW toyemp AS
    SELECT name, age, location, 12*salary AS annualsal
    FROM emp;
+--
+-- CREATE OR REPLACE VIEW
+--
+CREATE TABLE viewtest_tbl (a int, b int);
+COPY viewtest_tbl FROM stdin;
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT * FROM viewtest_tbl;
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT * FROM viewtest_tbl WHERE a > 10;
+SELECT * FROM viewtest;
+ a  | b  
+----+----
+ 15 | 20
+ 20 | 25
+(2 rows)
+
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+SELECT * FROM viewtest;
+ a  | b  
+----+----
+ 20 | 25
+ 15 | 20
+ 10 | 15
+(3 rows)
+
+-- should fail
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT a FROM viewtest_tbl WHERE a <> 20;
+ERROR:  Cannot change column set of existing view viewtest
+-- should fail
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT 1, * FROM viewtest_tbl;
+ERROR:  Cannot change column set of existing view viewtest
+-- should fail
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT a, b::numeric FROM viewtest_tbl;
+ERROR:  Cannot change column set of existing view viewtest
+DROP VIEW viewtest;
+DROP TABLE viewtest_tbl;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 0cfd3f103d4..bcce2332c4a 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1343,3 +1343,21 @@ SELECT tablename, rulename, definition FROM pg_rules
  shoelace_ok   | shoelace_ok_ins | CREATE RULE shoelace_ok_ins AS ON INSERT TO shoelace_ok DO INSTEAD UPDATE shoelace SET sl_avail = (shoelace.sl_avail + new.ok_quant) WHERE (shoelace.sl_name = new.ok_name);
 (29 rows)
 
+--
+-- CREATE OR REPLACE RULE
+--
+CREATE TABLE ruletest_tbl (a int, b int);
+CREATE TABLE ruletest_tbl2 (a int, b int);
+CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
+	DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
+INSERT INTO ruletest_tbl VALUES (99, 99);
+CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
+	DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
+INSERT INTO ruletest_tbl VALUES (99, 99);
+SELECT * FROM ruletest_tbl2;
+  a   |  b   
+------+------
+   10 |   10
+ 1000 | 1000
+(2 rows)
+
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index 858c8ce960d..8c15fc12417 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -19,3 +19,42 @@ CREATE VIEW toyemp AS
    SELECT name, age, location, 12*salary AS annualsal
    FROM emp;
 
+--
+-- CREATE OR REPLACE VIEW
+--
+
+CREATE TABLE viewtest_tbl (a int, b int);
+COPY viewtest_tbl FROM stdin;
+5	10
+10	15
+15	20
+20	25
+\.
+
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT * FROM viewtest_tbl;
+
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT * FROM viewtest_tbl WHERE a > 10;
+
+SELECT * FROM viewtest;
+
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC;
+
+SELECT * FROM viewtest;
+
+-- should fail
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT a FROM viewtest_tbl WHERE a <> 20;
+
+-- should fail
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT 1, * FROM viewtest_tbl;
+
+-- should fail
+CREATE OR REPLACE VIEW viewtest AS
+	SELECT a, b::numeric FROM viewtest_tbl;
+
+DROP VIEW viewtest;
+DROP TABLE viewtest_tbl;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 6ee6f2a5317..20afc9f0eda 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -765,3 +765,21 @@ SELECT viewname, definition FROM pg_views ORDER BY viewname;
 SELECT tablename, rulename, definition FROM pg_rules 
 	ORDER BY tablename, rulename;
 
+--
+-- CREATE OR REPLACE RULE
+--
+
+CREATE TABLE ruletest_tbl (a int, b int);
+CREATE TABLE ruletest_tbl2 (a int, b int);
+
+CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
+	DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (10, 10);
+
+INSERT INTO ruletest_tbl VALUES (99, 99);
+
+CREATE OR REPLACE RULE myrule AS ON INSERT TO ruletest_tbl
+	DO INSTEAD INSERT INTO ruletest_tbl2 VALUES (1000, 1000);
+
+INSERT INTO ruletest_tbl VALUES (99, 99);
+
+SELECT * FROM ruletest_tbl2;