Index: src/backend/commands/view.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/commands/view.c,v retrieving revision 1.68 diff -c -r1.68 view.c *** src/backend/commands/view.c 30 Aug 2002 19:23:19 -0000 1.68 --- src/backend/commands/view.c 31 Aug 2002 05:45:42 -0000 *************** *** 12,20 **** --- 12,24 ---- */ #include "postgres.h" + #include "access/genam.h" + #include "access/heapam.h" #include "access/xact.h" + #include "catalog/catname.h" #include "catalog/dependency.h" #include "catalog/heap.h" + #include "catalog/indexing.h" #include "catalog/namespace.h" #include "commands/tablecmds.h" #include "commands/view.h" *************** *** 26,48 **** #include "rewrite/rewriteManip.h" #include "rewrite/rewriteRemove.h" #include "rewrite/rewriteSupport.h" #include "utils/syscache.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... *--------------------------------------------------------------------- */ static Oid ! DefineVirtualRelation(const RangeVar *relation, List *tlist) { CreateStmt *createStmt = makeNode(CreateStmt); List *attrList, *t; --- 30,56 ---- #include "rewrite/rewriteManip.h" #include "rewrite/rewriteRemove.h" #include "rewrite/rewriteSupport.h" + #include "utils/acl.h" + #include "utils/builtins.h" + #include "utils/fmgroids.h" + #include "utils/lsyscache.h" #include "utils/syscache.h" + #include "utils/tqual.h" + static void CheckAttributeTuples(Oid relid, TupleDesc tupdesc); /*--------------------------------------------------------------------- * DefineVirtualRelation * ! * Create the "view" relation. `DefineRelation' does all the work, we ! * just provide the correct arguments. *--------------------------------------------------------------------- */ static Oid ! DefineVirtualRelation(const RangeVar *relation, List *tlist, bool replace) { + Oid viewOid, + namespaceId; CreateStmt *createStmt = makeNode(CreateStmt); List *attrList, *t; *************** *** 52,58 **** * the (non-junk) targetlist items from the view's SELECT list. */ attrList = NIL; ! foreach(t, tlist) { TargetEntry *entry = lfirst(t); Resdom *res = entry->resdom; --- 60,66 ---- * the (non-junk) targetlist items from the view's SELECT list. */ attrList = NIL; ! foreach (t, tlist) { TargetEntry *entry = lfirst(t); Resdom *res = entry->resdom; *************** *** 82,105 **** if (attrList == NIL) elog(ERROR, "attempted to define virtual relation with no attrs"); ! /* ! * 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... */ ! return DefineRelation(createStmt, RELKIND_VIEW); } static RuleStmt * ! FormViewRetrieveRule(const RangeVar *view, Query *viewParse) { RuleStmt *rule; --- 90,266 ---- if (attrList == NIL) elog(ERROR, "attempted to define virtual relation with no attrs"); ! namespaceId = RangeVarGetCreationNamespace(relation); ! viewOid = get_relname_relid(relation->relname, namespaceId); ! ! if (replace && OidIsValid(viewOid)) ! { ! List *schema = attrList; ! List *entry; ! TupleDesc descriptor; ! ! if (!IsBootstrapProcessingMode()) ! { ! AclResult aclresult; ! aclresult = pg_namespace_aclcheck(namespaceId, GetUserId(), ! ACL_CREATE); ! if (aclresult != ACLCHECK_OK) ! aclcheck_error(aclresult, get_namespace_name(namespaceId)); ! } ! ! /* ! * Check for duplicate names in the explicit list of attributes. ! * FIXME: this can be made faster, remove O(n^2) performance ! */ ! foreach (entry, schema) ! { ! ColumnDef *coldef = lfirst(entry); ! List *rest; ! ! foreach (rest, lnext(entry)) ! { ! ColumnDef *restdef = lfirst(rest); ! ! if (strcmp(coldef->colname, restdef->colname) == 0) ! elog(ERROR, "CREATE VIEW: attribute \"%s\" duplicated", ! coldef->colname); ! } ! } ! ! if (length(schema) <= 0) ! elog(ERROR, "Views must have attributes"); ! ! /* ! * Create a relation descriptor from the relation schema and create ! * the relation. Note that in this stage only inherited (pre-cooked) ! * defaults and constraints will be included into the new relation. ! * (BuildDescForRelation takes care of the inherited defaults, but we ! * have to copy inherited constraints here.) ! */ ! ! descriptor = BuildDescForRelation(schema); ! descriptor->tdhasoid = BoolToHasOid(false); ! CheckAttributeTuples(viewOid, descriptor); ! 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... ! */ ! return DefineRelation(createStmt, RELKIND_VIEW); ! } ! } ! ! /* ! * Check to ensure that the tupdesc formed by the column defs specified ! * by the user does not conflict with the existing definition of the ! * view. We do not allow the user to add, remove, or change the type of ! * the view's columns, as that might break any database objects that ! * rely upon it. ! * ! * If a conflict is found, the command is aborted via elog(ERROR); if ! * this function returns, the caller can assume that the attribute ! * tuples in the specified TupleDesc are sufficiently compatible. ! */ ! ! static void ! CheckAttributeTuples(Oid relid, TupleDesc tupdesc) ! { ! Relation attrel; ! SysScanDesc scan; ! ScanKeyData key[1]; ! HeapTuple attr_tup; ! TupleDesc tupdesc_cpy; ! int i; ! ! /* Make a copy of the passed-in TupleDesc so we can modify it */ ! tupdesc_cpy = CreateTupleDescCopy(tupdesc); ! ! attrel = heap_openr(AttributeRelationName, AccessShareLock); ! ! ScanKeyEntryInitialize(&key[0], 0x0, ! Anum_pg_attribute_attrelid, ! F_OIDEQ, ! ObjectIdGetDatum(relid)); ! ! scan = systable_beginscan(attrel, AttributeRelidNumIndex, ! true, SnapshotNow, 1, key); ! ! while ((attr_tup = systable_getnext(scan)) != NULL) ! { ! Form_pg_attribute heap_attr; ! Form_pg_attribute supplied_attr = NULL; ! ! heap_attr = (Form_pg_attribute) GETSTRUCT(attr_tup); ! ! Assert(HeapTupleIsValid(attr_tup)); ! ! for (i = 0; i < tupdesc_cpy->natts; i++) ! { ! Form_pg_attribute curr_attr = tupdesc_cpy->attrs[i]; ! ! if (curr_attr != NULL && ! strncmp(NameStr(curr_attr->attname), ! NameStr(heap_attr->attname), ! NAMEDATALEN) == 0) ! { ! supplied_attr = curr_attr; ! tupdesc_cpy->attrs[i] = NULL; /* mark entry as found */ ! break; ! } ! } ! ! /* An entry in pg_attribute is missing from the column list */ ! if (supplied_attr == NULL) ! elog(ERROR, "Column \"%s\" is missing from column list." ! "\n\tIf you wish to change the columns of this view, " ! "\n\tyou must drop and re-create the view.", ! NameStr(heap_attr->attname)); ! ! if (heap_attr->atttypid != supplied_attr->atttypid) ! elog(ERROR, "Column \"%s\" has a different data type." ! "\n\tIf you wish to change the data type of a column " ! "in a view,\n\tyou must drop and re-create the view.", ! NameStr(heap_attr->attname)); ! ! /* XXX is this necessary? */ ! if (heap_attr->atttypmod != supplied_attr->atttypmod) ! elog(ERROR, "Column \"%s\" has a different type modifer." ! "\n\tIf you wish to change the type modifier of a column " ! "in a view,\n\tyou must drop and re-create the view.", ! NameStr(heap_attr->attname)); ! } ! ! systable_endscan(scan); ! heap_close(attrel, AccessShareLock); /* ! * Are there any user-created columns for which we don't have a ! * pg_attribute tuple? */ ! for (i = 0; i < tupdesc_cpy->natts; i++) ! { ! if (tupdesc_cpy->attrs[i] != NULL) ! elog(ERROR, "Attribute \"%s\" has been added to this view." ! "\n\tIf you wish to add a new column to a view," ! "\n\tyou must drop and re-create the view.", ! NameStr(tupdesc_cpy->attrs[i]->attname)); ! } } static RuleStmt * ! FormViewRetrieveRule(const RangeVar *view, Query *viewParse, bool replace) { RuleStmt *rule; *************** *** 114,125 **** rule->event = CMD_SELECT; rule->instead = true; rule->actions = makeList1(viewParse); return rule; } static void ! DefineViewRules(const RangeVar *view, Query *viewParse) { RuleStmt *retrieve_rule; --- 275,287 ---- rule->event = CMD_SELECT; rule->instead = true; rule->actions = makeList1(viewParse); + rule->replace = replace; return rule; } static void ! DefineViewRules(const RangeVar *view, Query *viewParse, bool replace) { RuleStmt *retrieve_rule; *************** *** 129,138 **** RuleStmt *delete_rule; #endif ! retrieve_rule = FormViewRetrieveRule(view, viewParse); #ifdef NOTYET - replace_rule = FormViewReplaceRule(view, viewParse); append_rule = FormViewAppendRule(view, viewParse); delete_rule = FormViewDeleteRule(view, viewParse); --- 291,299 ---- RuleStmt *delete_rule; #endif ! retrieve_rule = FormViewRetrieveRule(view, viewParse, replace); #ifdef NOTYET replace_rule = FormViewReplaceRule(view, viewParse); append_rule = FormViewAppendRule(view, viewParse); delete_rule = FormViewDeleteRule(view, viewParse); *************** *** 221,236 **** *------------------------------------------------------------------- */ void ! DefineView(const RangeVar *view, Query *viewParse) { Oid viewOid; /* * Create the view relation * ! * NOTE: if it already exists, the xact will be aborted. */ ! viewOid = DefineVirtualRelation(view, viewParse->targetList); /* * The relation we have just created is not visible to any other --- 382,399 ---- *------------------------------------------------------------------- */ void ! DefineView(const RangeVar *view, Query *viewParse, bool replace) { Oid viewOid; /* * Create the view relation * ! * NOTE: if it already exists and replace is false, the xact will ! * be aborted. */ ! ! viewOid = DefineVirtualRelation(view, viewParse->targetList, replace); /* * The relation we have just created is not visible to any other *************** *** 248,254 **** /* * Now create the rules associated with the view. */ ! DefineViewRules(view, viewParse); } /* --- 411,417 ---- /* * Now create the rules associated with the view. */ ! DefineViewRules(view, viewParse, replace); } /* Index: src/backend/nodes/copyfuncs.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/nodes/copyfuncs.c,v retrieving revision 1.208 diff -c -r1.208 copyfuncs.c *** src/backend/nodes/copyfuncs.c 30 Aug 2002 19:23:19 -0000 1.208 --- src/backend/nodes/copyfuncs.c 31 Aug 2002 05:45:42 -0000 *************** *** 2172,2177 **** --- 2172,2178 ---- Node_Copy(from, newnode, whereClause); newnode->event = from->event; newnode->instead = from->instead; + newnode->replace = from->replace; Node_Copy(from, newnode, actions); return newnode; *************** *** 2237,2242 **** --- 2238,2244 ---- Node_Copy(from, newnode, view); Node_Copy(from, newnode, aliases); Node_Copy(from, newnode, query); + newnode->replace = from->replace; return newnode; } Index: src/backend/nodes/equalfuncs.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/nodes/equalfuncs.c,v retrieving revision 1.156 diff -c -r1.156 equalfuncs.c *** src/backend/nodes/equalfuncs.c 30 Aug 2002 19:23:19 -0000 1.156 --- src/backend/nodes/equalfuncs.c 31 Aug 2002 05:45:42 -0000 *************** *** 1003,1008 **** --- 1003,1010 ---- return false; if (a->instead != b->instead) return false; + if (a->replace != b->replace) + return false; if (!equal(a->actions, b->actions)) return false; *************** *** 1066,1071 **** --- 1068,1075 ---- if (!equal(a->aliases, b->aliases)) return false; if (!equal(a->query, b->query)) + return false; + if (a->replace != b->replace) return false; return true; Index: src/backend/parser/gram.y =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/parser/gram.y,v retrieving revision 2.364 diff -c -r2.364 gram.y *** src/backend/parser/gram.y 29 Aug 2002 00:17:04 -0000 2.364 --- src/backend/parser/gram.y 31 Aug 2002 05:45:42 -0000 *************** *** 3346,3363 **** * *****************************************************************************/ ! RuleStmt: CREATE 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; $$ = (Node *)n; QueryIsRule=FALSE; } --- 3346,3364 ---- * *****************************************************************************/ ! 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->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,3548 **** * *****************************************************************************/ ! ViewStmt: CREATE VIEW qualified_name opt_column_list AS SelectStmt { ViewStmt *n = makeNode(ViewStmt); ! n->view = $3; ! n->aliases = $4; ! n->query = (Query *) $6; $$ = (Node *)n; } ; --- 3538,3551 ---- * *****************************************************************************/ ! ViewStmt: CREATE opt_or_replace VIEW qualified_name opt_column_list ! AS SelectStmt { ViewStmt *n = makeNode(ViewStmt); ! n->replace = $2; ! n->view = $4; ! n->aliases = $5; ! n->query = (Query *) $7; $$ = (Node *)n; } ; Index: src/backend/rewrite/rewriteDefine.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/rewrite/rewriteDefine.c,v retrieving revision 1.77 diff -c -r1.77 rewriteDefine.c *** src/backend/rewrite/rewriteDefine.c 5 Aug 2002 03:29:17 -0000 1.77 --- src/backend/rewrite/rewriteDefine.c 31 Aug 2002 05:45:42 -0000 *************** *** 48,71 **** AttrNumber evslot_index, bool evinstead, Node *event_qual, ! List *action) { char *evqual = nodeToString(event_qual); char *actiontree = nodeToString((Node *) action); int i; Datum values[Natts_pg_rewrite]; char nulls[Natts_pg_rewrite]; NameData rname; Relation pg_rewrite_desc; TupleDesc tupDesc; ! HeapTuple tup; Oid rewriteObjectId; ObjectAddress myself, referenced; ! ! if (IsDefinedRewriteRule(eventrel_oid, rulname)) ! elog(ERROR, "Attempt to insert rule \"%s\" failed: already exists", ! rulname); /* * Set up *nulls and *values arrays --- 48,71 ---- AttrNumber evslot_index, bool evinstead, Node *event_qual, ! 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, ! oldtup = NULL; Oid rewriteObjectId; ObjectAddress myself, referenced; ! bool is_update = false; /* * Set up *nulls and *values arrays *************** *** 87,104 **** */ pg_rewrite_desc = heap_openr(RewriteRelationName, RowExclusiveLock); ! tupDesc = pg_rewrite_desc->rd_att; ! tup = heap_formtuple(tupDesc, ! values, ! nulls); ! rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); CatalogUpdateIndexes(pg_rewrite_desc, tup); heap_freetuple(tup); /* * Install dependency on rule's relation to ensure it will go away * on relation deletion. If the rule is ON SELECT, make the dependency --- 87,142 ---- */ pg_rewrite_desc = heap_openr(RewriteRelationName, RowExclusiveLock); ! oldtup = SearchSysCache(RULERELNAME, ! ObjectIdGetDatum(eventrel_oid), ! PointerGetDatum(rulname), ! 0, 0); ! ! /* ! * When replacing, we don't need to replace every attribute ! */ ! if (HeapTupleIsValid(oldtup)) ! { ! if (!replace) ! elog(ERROR,"Attempt to insert rule \"%s\" failed: already exists", ! rulname); ! ! MemSet(replaces, ' ', sizeof(replaces)); ! replaces[Anum_pg_rewrite_ev_attr - 1] = 'r'; ! 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); ! ! ReleaseSysCache(oldtup); ! is_update = true; ! rewriteObjectId = HeapTupleGetOid(tup); ! } ! else ! { ! tupDesc = pg_rewrite_desc->rd_att; ! tup = heap_formtuple(tupDesc, values, nulls); ! ! rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); ! } CatalogUpdateIndexes(pg_rewrite_desc, tup); heap_freetuple(tup); + 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,126 **** referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, ! (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 */ --- 152,165 ---- referenced.objectSubId = 0; recordDependencyOn(&myself, &referenced, ! (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,148 **** --- 182,188 ---- 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,238 **** * event relation, ... */ i = 0; ! foreach(tllist, query->targetList) { TargetEntry *tle = (TargetEntry *) lfirst(tllist); Resdom *resdom = tle->resdom; --- 272,278 ---- * event relation, ... */ i = 0; ! foreach (tllist, query->targetList) { TargetEntry *tle = (TargetEntry *) lfirst(tllist); Resdom *resdom = tle->resdom; *************** *** 282,288 **** /* * ... there must not be another ON SELECT rule already ... */ ! if (event_relation->rd_rules != NULL) { for (i = 0; i < event_relation->rd_rules->numLocks; i++) { --- 322,328 ---- /* * ... there must not be another ON SELECT rule already ... */ ! if (!replace && event_relation->rd_rules != NULL) { for (i = 0; i < event_relation->rd_rules->numLocks; i++) { *************** *** 364,370 **** event_attno, is_instead, event_qual, ! action); /* * Set pg_class 'relhasrules' field TRUE for event relation. If --- 404,411 ---- event_attno, is_instead, event_qual, ! action, ! replace); /* * Set pg_class 'relhasrules' field TRUE for event relation. If Index: src/backend/tcop/utility.c =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/backend/tcop/utility.c,v retrieving revision 1.175 diff -c -r1.175 utility.c *** src/backend/tcop/utility.c 30 Aug 2002 19:23:20 -0000 1.175 --- src/backend/tcop/utility.c 31 Aug 2002 05:45:42 -0000 *************** *** 595,601 **** { ViewStmt *stmt = (ViewStmt *) parsetree; ! DefineView(stmt->view, stmt->query); } break; --- 595,601 ---- { ViewStmt *stmt = (ViewStmt *) parsetree; ! DefineView(stmt->view, stmt->query, stmt->replace); } break; Index: src/include/commands/view.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/commands/view.h,v retrieving revision 1.16 diff -c -r1.16 view.h *** src/include/commands/view.h 1 Jul 2002 15:27:56 -0000 1.16 --- src/include/commands/view.h 31 Aug 2002 05:45:42 -0000 *************** *** 16,22 **** #include "nodes/parsenodes.h" ! extern void DefineView(const RangeVar *view, Query *view_parse); extern void RemoveView(const RangeVar *view, DropBehavior behavior); #endif /* VIEW_H */ --- 16,22 ---- #include "nodes/parsenodes.h" ! extern void DefineView(const RangeVar *view, Query *view_parse, bool replace); extern void RemoveView(const RangeVar *view, DropBehavior behavior); #endif /* VIEW_H */ Index: src/include/nodes/parsenodes.h =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/include/nodes/parsenodes.h,v retrieving revision 1.203 diff -c -r1.203 parsenodes.h *** src/include/nodes/parsenodes.h 30 Aug 2002 19:23:20 -0000 1.203 --- src/include/nodes/parsenodes.h 31 Aug 2002 05:45:42 -0000 *************** *** 1349,1354 **** --- 1349,1355 ---- CmdType event; /* SELECT, INSERT, etc */ bool instead; /* is a 'do instead'? */ List *actions; /* the action statements */ + bool replace; /* OR REPLACE */ } RuleStmt; /* ---------------------- *************** *** 1414,1419 **** --- 1415,1421 ---- RangeVar *view; /* the view to be created */ List *aliases; /* target column names */ Query *query; /* the SQL statement */ + bool replace; /* replace an existing view? */ } ViewStmt; /* ---------------------- Index: src/test/regress/expected/create_view.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/create_view.out,v retrieving revision 1.3 diff -c -r1.3 create_view.out *** src/test/regress/expected/create_view.out 10 Jun 2000 05:19:14 -0000 1.3 --- src/test/regress/expected/create_view.out 31 Aug 2002 05:45:42 -0000 *************** *** 15,17 **** --- 15,63 ---- 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: Column "b" is missing from column list. + If you wish to change the columns of this view, + you must drop and re-create the view. + -- should fail + CREATE OR REPLACE VIEW viewtest AS + SELECT 1, * FROM viewtest_tbl; + ERROR: Attribute "?column?" has been added to this view. + If you wish to add a new column to a view, + you must drop and re-create the view. + -- should fail + CREATE OR REPLACE VIEW viewtest AS + SELECT a, b::numeric FROM viewtest_tbl; + ERROR: Column "b" has a different data type. + If you wish to change the data type of a column in a view, + you must drop and re-create the view. + DROP VIEW viewtest; + DROP TABLE viewtest_tbl; Index: src/test/regress/expected/rules.out =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/expected/rules.out,v retrieving revision 1.60 diff -c -r1.60 rules.out *** src/test/regress/expected/rules.out 29 Aug 2002 01:19:41 -0000 1.60 --- src/test/regress/expected/rules.out 31 Aug 2002 05:45:42 -0000 *************** *** 1343,1345 **** --- 1343,1363 ---- 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) + Index: src/test/regress/sql/create_view.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/create_view.sql,v retrieving revision 1.3 diff -c -r1.3 create_view.sql *** src/test/regress/sql/create_view.sql 10 Jun 2000 05:19:26 -0000 1.3 --- src/test/regress/sql/create_view.sql 31 Aug 2002 05:45:42 -0000 *************** *** 19,21 **** --- 19,60 ---- 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; \ No newline at end of file Index: src/test/regress/sql/rules.sql =================================================================== RCS file: /var/lib/cvs/pgsql-server/src/test/regress/sql/rules.sql,v retrieving revision 1.20 diff -c -r1.20 rules.sql *** src/test/regress/sql/rules.sql 3 May 2002 00:32:19 -0000 1.20 --- src/test/regress/sql/rules.sql 31 Aug 2002 05:45:42 -0000 *************** *** 765,767 **** --- 765,785 ---- 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; \ No newline at end of file