Notes

*** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 4138,4143 **** --- 4138,4155 ---- + ev_kind + char + + + l = with local check option, + c = with cascaded check option, + n = no check option specified, + e = rule was explictly created by user + + + + ev_qual text *** a/doc/src/sgml/information_schema.sgml --- b/doc/src/sgml/information_schema.sgml *************** *** 5419,5425 **** ORDER BY c.ordinal_position; check_option character_data ! Applies to a feature not available in PostgreSQL --- 5419,5429 ---- check_option character_data ! ! The level of integrity checking in updatable views, ! either LOCAL, CASCADED ! or NONE ! *** a/doc/src/sgml/intro.sgml --- b/doc/src/sgml/intro.sgml *************** *** 110,116 **** triggers ! views transactional integrity --- 110,116 ---- triggers ! updatable views transactional integrity *** a/doc/src/sgml/ref/create_view.sgml --- b/doc/src/sgml/ref/create_view.sgml *************** *** 23,28 **** PostgreSQL documentation --- 23,29 ---- CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query + [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] *************** *** 108,113 **** CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW n --- 109,150 ---- + + + CHECK OPTION + + + This option has to do with updatable views. All + INSERT and UPDATE commands on the view + will be checked to ensure data satisfy the view-defining + condition (that is, the new data would be visible through the + view). If they do not, the update will be rejected. Currently + views specified with this options aren't updatable in PostgreSQL and the creation + of a view with this option will be rejected. + + + + + + LOCAL + + + Check for integrity on this view. + + + + + + CASCADED + + + Check for integrity on this view and on any dependent + view. CASCADED is assumed if neither + CASCADED nor LOCAL is specified. + + + + *************** *** 115,125 **** CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW n Notes ! Currently, views are read only: the system will not allow an insert, ! update, or delete on a view. You can get the effect of an updatable ! view by creating rules that rewrite inserts, etc. on the view into ! appropriate actions on other tables. For more information see ! . --- 152,201 ---- Notes ! Currently, views are updatable following SQL92 specifications, that ! is: ! ! ! ! ! ! Views with just one base table (or another updatable view) ! ! ! ! ! No aggregate functions ! ! ! ! ! No HAVING, DISTINCT nor ! GROUP BY clauses ! ! ! ! ! ! No UNION, INTERSECT, ! EXCEPTclauses ! ! ! ! ! ! Views are insertable only if you provide in the defining ! SELECT statement all columns that are NOT NULL and don't ! have a default value. ! ! ! ! The updatable views implementation is based on the rule system. Because of ! this, you can get the same effect in more complex views by creating your ! own rules that rewrite the INSERT, ! UPDATE and UPDATE actions on the view ! into appropriate actions on other tables. You can also replace ! automatically generated rules with your own rules. For more information, ! refer to . *************** *** 170,224 **** CREATE VIEW comedies AS Compatibility - The SQL standard specifies some additional capabilities for the - CREATE VIEW statement: - - CREATE VIEW name [ ( column_name [, ...] ) ] - AS query - [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] - - - - - The optional clauses for the full SQL command are: - - - - CHECK OPTION - - - This option has to do with updatable views. All - INSERT and UPDATE commands on the view - will be checked to ensure data satisfy the view-defining - condition (that is, the new data would be visible through the - view). If they do not, the update will be rejected. - - - - - - LOCAL - - - Check for integrity on this view. - - - - - - CASCADED - - - Check for integrity on this view and on any dependent - view. CASCADED is assumed if neither - CASCADED nor LOCAL is specified. - - - - - - - CREATE OR REPLACE VIEW is a PostgreSQL language extension. So is the concept of a temporary view. --- 246,251 ---- *** a/src/backend/commands/view.c --- b/src/backend/commands/view.c *************** *** 28,33 **** --- 28,34 ---- #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteManip.h" #include "rewrite/rewriteSupport.h" + #include "rewrite/viewUpdate.h" #include "utils/acl.h" #include "utils/builtins.h" #include "utils/lsyscache.h" *************** *** 298,304 **** checkViewTupleDesc(TupleDesc newdesc, TupleDesc olddesc) } static void ! DefineViewRules(Oid viewOid, Query *viewParse, bool replace) { /* * Set up the ON SELECT rule. Since the query has already been through --- 299,306 ---- } static void ! DefineViewRules(Oid viewOid, RangeVar *var, Query *viewParse, bool replace, ! bool checkOption, bool cascade) { /* * Set up the ON SELECT rule. Since the query has already been through *************** *** 308,313 **** DefineViewRules(Oid viewOid, Query *viewParse, bool replace) --- 310,316 ---- viewOid, NULL, CMD_SELECT, + makeViewCheckOption(checkOption, cascade), true, replace, list_make1(viewParse)); *************** *** 315,320 **** DefineViewRules(Oid viewOid, Query *viewParse, bool replace) --- 318,324 ---- /* * Someday: automatic ON INSERT, etc */ + CreateViewUpdateRules(viewParse, var, checkOption, cascade); } /*--------------------------------------------------------------- *************** *** 388,394 **** UpdateRangeTableOfViewParse(Oid viewOid, Query *viewParse) * Execute a CREATE VIEW command. */ void ! DefineView(ViewStmt *stmt, const char *queryString) { Query *viewParse; Oid viewOid; --- 392,399 ---- * Execute a CREATE VIEW command. */ void ! DefineView(ViewStmt *stmt, const char *queryString, bool checkOption, ! bool cascade) { Query *viewParse; Oid viewOid; *************** *** 482,486 **** DefineView(ViewStmt *stmt, const char *queryString) /* * Now create the rules associated with the view. */ ! DefineViewRules(viewOid, viewParse, stmt->replace); } --- 487,492 ---- /* * Now create the rules associated with the view. */ ! DefineViewRules(viewOid, stmt->view, viewParse, stmt->replace, ! checkOption, cascade); } *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** *** 5519,5525 **** AlterOwnerStmt: ALTER AGGREGATE func_name aggr_args OWNER TO RoleId *****************************************************************************/ RuleStmt: CREATE opt_or_replace RULE name AS ! { QueryIsRule=TRUE; } ON event TO qualified_name where_clause DO opt_instead RuleActionList { --- 5519,5525 ---- *****************************************************************************/ RuleStmt: CREATE opt_or_replace RULE name AS ! { QueryIsRule = true; } ON event TO qualified_name where_clause DO opt_instead RuleActionList { *************** *** 5532,5538 **** RuleStmt: CREATE opt_or_replace RULE name AS n->instead = $13; n->actions = $14; $$ = (Node *)n; ! QueryIsRule=FALSE; } ; --- 5532,5538 ---- n->instead = $13; n->actions = $14; $$ = (Node *)n; ! QueryIsRule = false; } ; *************** *** 5545,5557 **** RuleActionList: /* the thrashing around here is to discard "empty" statements... */ RuleActionMulti: RuleActionMulti ';' RuleActionStmtOrEmpty ! { if ($3 != NULL) $$ = lappend($1, $3); else $$ = $1; } | RuleActionStmtOrEmpty ! { if ($1 != NULL) $$ = list_make1($1); else $$ = NIL; --- 5545,5559 ---- /* the thrashing around here is to discard "empty" statements... */ RuleActionMulti: RuleActionMulti ';' RuleActionStmtOrEmpty ! { ! if ($3 != NULL) $$ = lappend($1, $3); else $$ = $1; } | RuleActionStmtOrEmpty ! { ! if ($1 != NULL) $$ = list_make1($1); else $$ = NIL; *************** *** 5813,5818 **** ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list --- 5815,5821 ---- n->view->istemp = $2; n->aliases = $5; n->query = $7; + n->options = $8; n->replace = false; $$ = (Node *) n; } *************** *** 5824,5829 **** ViewStmt: CREATE OptTemp VIEW qualified_name opt_column_list --- 5827,5833 ---- n->view->istemp = $4; n->aliases = $7; n->query = $9; + n->options = $10; n->replace = true; $$ = (Node *) n; } *** a/src/backend/rewrite/Makefile --- b/src/backend/rewrite/Makefile *************** *** 13,18 **** top_builddir = ../../.. include $(top_builddir)/src/Makefile.global OBJS = rewriteRemove.o rewriteDefine.o \ ! rewriteHandler.o rewriteManip.o rewriteSupport.o include $(top_srcdir)/src/backend/common.mk --- 13,19 ---- include $(top_builddir)/src/Makefile.global OBJS = rewriteRemove.o rewriteDefine.o \ ! rewriteHandler.o rewriteManip.o rewriteSupport.o \ ! viewUpdate.o include $(top_srcdir)/src/backend/common.mk *** a/src/backend/rewrite/rewriteDefine.c --- b/src/backend/rewrite/rewriteDefine.c *************** *** 25,30 **** --- 25,31 ---- #include "parser/parse_utilcmd.h" #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteManip.h" + #include "rewrite/rewriteRemove.h" #include "rewrite/rewriteSupport.h" #include "utils/acl.h" #include "utils/builtins.h" *************** *** 49,63 **** static void setRuleCheckAsUser_Query(Query *qry, Oid userid); static Oid InsertRule(char *rulname, int evtype, ! Oid eventrel_oid, 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]; bool nulls[Natts_pg_rewrite]; --- 50,66 ---- static Oid InsertRule(char *rulname, int evtype, ! Relation event_relation, AttrNumber evslot_index, bool evinstead, + char evkind, Node *event_qual, List *action, bool replace) { char *evqual = nodeToString(event_qual); char *actiontree = nodeToString((Node *) action); + Oid eventrel_oid = RelationGetRelid(event_relation); int i; Datum values[Natts_pg_rewrite]; bool nulls[Natts_pg_rewrite]; *************** *** 84,89 **** InsertRule(char *rulname, --- 87,93 ---- values[i++] = CharGetDatum(evtype + '0'); /* ev_type */ values[i++] = CharGetDatum(RULE_FIRES_ON_ORIGIN); /* ev_enabled */ values[i++] = BoolGetDatum(evinstead); /* is_instead */ + values[i++] = CharGetDatum(evkind); /* ev_kind */ values[i++] = CStringGetTextDatum(evqual); /* ev_qual */ values[i++] = CStringGetTextDatum(actiontree); /* ev_action */ *************** *** 102,112 **** InsertRule(char *rulname, if (HeapTupleIsValid(oldtup)) { if (!replace) ! ereport(ERROR, ! (errcode(ERRCODE_DUPLICATE_OBJECT), ! errmsg("rule \"%s\" for relation \"%s\" already exists", ! rulname, get_rel_name(eventrel_oid)))); /* * When replacing, we don't need to replace every attribute --- 106,174 ---- if (HeapTupleIsValid(oldtup)) { + char old_tup_ev_kind = evkind; + Datum dat; + bool isnull; + + dat = heap_getattr(oldtup, + Anum_pg_rewrite_ev_kind, + RelationGetDescr(pg_rewrite_desc), + &isnull); + + if (isnull) + /* should not happen */ + elog(ERROR, "got null field in ev_kind where not null expected"); + if (!replace) ! { ! /* ! * If REPLACE was not used we still have to check if the ! * rule is implicit: then we have to replace it anyways. ! */ ! ! old_tup_ev_kind = DatumGetChar(dat); ! ! if (old_tup_ev_kind == NO_OPTION_EXPLICIT) ! { ! ereport(ERROR, ! (errcode(ERRCODE_DUPLICATE_OBJECT), ! errmsg("rule \"%s\" for relation \"%s\" already exists", ! rulname, get_rel_name(eventrel_oid)))); ! } ! } ! ! /* ! * If we are about to replace an implicit rule ! * with an user defined rule, we need to drop all ! * implicit rules on the same action (for implicit ! * conditional rules we have DO INSTEAD NOTHING RULES ! * present and vice versa...). We have to be careful not ! * to drop the current rule we want to replace... ! */ ! if ((old_tup_ev_kind != NO_OPTION_EXPLICIT) ! && (evkind == NO_OPTION_EXPLICIT)) ! { ! DeleteImplicitRulesOnEvent(event_relation, evtype, ! HeapTupleGetOid(oldtup)); ! } ! ! /* ! * If we want to replace an implicit rule with a new ! * implicit rule of the same action kind, we need to ! * be careful when the old implicit rule has a check option ! * attached to it or not. If true, we need to drop all other ! * implicit actions on this action besides our rule we want to ! * replace now when no check option was given (this will drop ! * all assigned rules for the specified action, we don't need ! * anymore). ! */ ! if (((old_tup_ev_kind == LOCAL_OPTION_IMPLICIT) ! || (old_tup_ev_kind == CASCADED_OPTION_IMPLICIT)) ! && (evkind = NO_OPTION_IMPLICIT)) ! { ! DeleteImplicitRulesOnEvent(event_relation, evtype, ! HeapTupleGetOid(oldtup)); ! } /* * When replacing, we don't need to replace every attribute *************** *** 115,120 **** InsertRule(char *rulname, --- 177,183 ---- replaces[Anum_pg_rewrite_ev_attr - 1] = true; replaces[Anum_pg_rewrite_ev_type - 1] = true; replaces[Anum_pg_rewrite_is_instead - 1] = true; + replaces[Anum_pg_rewrite_ev_kind - 1] = true; replaces[Anum_pg_rewrite_ev_qual - 1] = true; replaces[Anum_pg_rewrite_ev_action - 1] = true; *************** *** 130,137 **** InsertRule(char *rulname, } else { ! tup = heap_form_tuple(pg_rewrite_desc->rd_att, values, nulls); rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); } --- 193,210 ---- } else { ! /* ! * Implicit rules should be dropped automatically when someone ! * wants to have its *own* rules on the view. is_implicit is set ! * to NO_OPTION_EXPLICIT in this case so we drop all implicit ! * rules on the specified event type immediately. ! */ + if (evkind == NO_OPTION_EXPLICIT) + DeleteImplicitRulesOnEvent(event_relation, evtype, + InvalidOid); + + tup = heap_form_tuple(pg_rewrite_desc->rd_att, values, nulls); rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup); } *************** *** 204,209 **** DefineRule(RuleStmt *stmt, const char *queryString) --- 277,283 ---- relId, whereClause, stmt->event, + NO_OPTION_EXPLICIT, stmt->instead, stmt->replace, actions); *************** *** 222,227 **** DefineQueryRewrite(char *rulename, --- 296,302 ---- Oid event_relid, Node *event_qual, CmdType event_type, + char event_kind, bool is_instead, bool replace, List *action) *************** *** 458,466 **** DefineQueryRewrite(char *rulename, { ruleId = InsertRule(rulename, event_type, ! event_relid, event_attno, is_instead, event_qual, action, replace); --- 533,543 ---- { ruleId = InsertRule(rulename, event_type, ! /*event_relid,*/ ! event_relation, event_attno, is_instead, + event_kind, event_qual, action, replace); *** a/src/backend/rewrite/rewriteRemove.c --- b/src/backend/rewrite/rewriteRemove.c *************** *** 90,95 **** RemoveRewriteRule(Oid owningRel, const char *ruleName, DropBehavior behavior, --- 90,147 ---- performDeletion(&object, behavior); } + /* + * deleteImplicitRulesOnEvent + * + * This will delete implicit rules, if any exists, on the event in the + * relation. + * You can use exceptOid to exclude a specific implicit + * rule from deletion, use InvalidOid if you want to + * safely delete all implicit rules. + */ + void + DeleteImplicitRulesOnEvent(Relation rel, CmdType event_type, + Oid exceptOid) + { + RuleLock *rulelocks = rel->rd_rules; + int nlocks; + int i; + + /* + * Select rules are implicit (are they marked as implicit??) + * but we don't want to delete them + */ + if (event_type == CMD_SELECT) + return; + + /* + * If there are no rules on the relation we waste no more time + */ + if (rulelocks == NULL) + return; + + nlocks = rulelocks->numLocks; + + /* + * Look at all rules looking for the ones that are on the event and are + * implicit + */ + for (i = 0; i < nlocks; i++) + { + RewriteRule *oneLock = rulelocks->rules[i]; + + if ((oneLock->event == event_type) + && (oneLock->ev_kind != NO_OPTION_EXPLICIT) + && (oneLock->ruleId != exceptOid)) + { + RemoveRewriteRuleById(oneLock->ruleId); + elog(DEBUG1, "removing implicit rule with oid %d\n", + oneLock->ruleId); + deleteDependencyRecordsFor(RewriteRelationId, oneLock->ruleId); + } + } + } + /* * Guts of rule deletion. *** a/src/backend/tcop/utility.c --- b/src/backend/tcop/utility.c *************** *** 20,25 **** --- 20,26 ---- #include "access/xact.h" #include "catalog/catalog.h" #include "catalog/namespace.h" + #include "catalog/pg_rewrite.h" #include "catalog/toasting.h" #include "commands/alter.h" #include "commands/async.h" *************** *** 741,747 **** ProcessUtility(Node *parsetree, break; case T_ViewStmt: /* CREATE VIEW */ ! DefineView((ViewStmt *) parsetree, queryString); break; case T_CreateFunctionStmt: /* CREATE FUNCTION */ --- 742,769 ---- break; case T_ViewStmt: /* CREATE VIEW */ ! { ! ViewStmt *stmt = (ViewStmt *) parsetree; ! bool checkOption = false; ! bool cascade = false; ! ListCell *cell; ! ! if (list_length(stmt->options) > 0) ! { ! checkOption = true; ! } ! ! foreach(cell, stmt->options) ! { ! Value *val = (Value *) lfirst(cell); ! if (strncmp(strVal(val), "cascade", strlen("cascade")) == 0) ! { ! cascade = true; ! } ! } ! ! DefineView(stmt, queryString, checkOption, cascade); ! } break; case T_CreateFunctionStmt: /* CREATE FUNCTION */ *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** *** 29,34 **** --- 29,35 ---- #include "catalog/pg_opclass.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" + #include "catalog/pg_rewrite.h" #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" #include "commands/defrem.h" *************** *** 2169,2174 **** make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc, --- 2170,2176 ---- Oid ev_class; int2 ev_attr; bool is_instead; + char ev_kind; char *ev_qual; char *ev_action; List *actions = NIL; *************** *** 2191,2196 **** make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc, --- 2193,2201 ---- fno = SPI_fnumber(rulettc, "is_instead"); is_instead = (bool) SPI_getbinval(ruletup, rulettc, fno, &isnull); + fno = SPI_fnumber(rulettc, "ev_kind"); + ev_kind = (char) SPI_getbinval(ruletup, rulettc, fno, &isnull); + fno = SPI_fnumber(rulettc, "ev_qual"); ev_qual = SPI_getvalue(ruletup, rulettc, fno); *************** *** 2218,2225 **** make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc, get_query_def(query, buf, NIL, RelationGetDescr(ev_relation), prettyFlags, 0); - appendStringInfo(buf, ";"); heap_close(ev_relation, AccessShareLock); } --- 2223,2255 ---- get_query_def(query, buf, NIL, RelationGetDescr(ev_relation), prettyFlags, 0); + /* + * Support for updatable views: append the check option if + * required. If any pretty print flags is enabled, we need + * to do a linebreak before. + */ + + switch (ev_kind) + { + case LOCAL_OPTION_IMPLICIT: + { + if (prettyFlags & PRETTYFLAG_INDENT) + appendStringInfo(buf, "\n"); + appendStringInfo(buf, " WITH LOCAL CHECK OPTION"); + break; + } + case CASCADED_OPTION_IMPLICIT: + { + if (prettyFlags & PRETTYFLAG_INDENT) + appendStringInfo(buf, "\n"); + appendStringInfo(buf, " WITH CASCADED CHECK OPTION"); + break; + } + + } + + appendStringInfo(buf, ";"); heap_close(ev_relation, AccessShareLock); } *** a/src/backend/utils/cache/relcache.c --- b/src/backend/utils/cache/relcache.c *************** *** 662,667 **** RelationBuildRuleLock(Relation relation) --- 662,668 ---- rule->attrno = rewrite_form->ev_attr; rule->enabled = rewrite_form->ev_enabled; rule->isInstead = rewrite_form->is_instead; + rule->ev_kind = rewrite_form->ev_kind; /* * Must use heap_getattr to fetch ev_action and ev_qual. Also, the *************** *** 785,790 **** equalRuleLocks(RuleLock *rlock1, RuleLock *rlock2) --- 786,793 ---- return false; if (!equal(rule1->actions, rule2->actions)) return false; + if(rule1->ev_kind != rule2->ev_kind) + return false; } } else if (rlock2 != NULL) *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** *** 39,44 **** int optreset; --- 39,45 ---- #include "catalog/pg_cast.h" #include "catalog/pg_class.h" #include "catalog/pg_proc.h" + #include "catalog/pg_rewrite.h" #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" #include "libpq/libpq-fs.h" *************** *** 4007,4014 **** getRules(int *numRules) appendPQExpBuffer(query, "SELECT " "tableoid, oid, rulename, " "ev_class as ruletable, ev_type, is_instead, " ! "ev_enabled " "FROM pg_rewrite " "ORDER BY oid"); } else if (g_fout->remoteVersion >= 70100) --- 4008,4016 ---- appendPQExpBuffer(query, "SELECT " "tableoid, oid, rulename, " "ev_class as ruletable, ev_type, is_instead, " ! "ev_enabled, ev_kind " "FROM pg_rewrite " + "WHERE ev_kind = 'e' " "ORDER BY oid"); } else if (g_fout->remoteVersion >= 70100) *** a/src/bin/pg_dump/pg_dump.h --- b/src/bin/pg_dump/pg_dump.h *************** *** 304,310 **** typedef struct _ruleInfo bool is_instead; char ev_enabled; bool separate; /* TRUE if must dump as separate item */ ! /* separate is always true for non-ON SELECT rules */ } RuleInfo; typedef struct _triggerInfo --- 304,312 ---- bool is_instead; char ev_enabled; bool separate; /* TRUE if must dump as separate item */ ! /* separate is always true for non-ON SELECT rules and ! * for implicit rules (updatable views) ! */ } RuleInfo; typedef struct _triggerInfo *** a/src/include/catalog/pg_rewrite.h --- b/src/include/catalog/pg_rewrite.h *************** *** 39,44 **** CATALOG(pg_rewrite,2618) --- 39,45 ---- char ev_type; char ev_enabled; bool is_instead; + char ev_kind; /* NB: remaining fields must be accessed via heap_getattr */ text ev_qual; *************** *** 56,69 **** typedef FormData_pg_rewrite *Form_pg_rewrite; * compiler constants for pg_rewrite * ---------------- */ ! #define Natts_pg_rewrite 8 #define Anum_pg_rewrite_rulename 1 #define Anum_pg_rewrite_ev_class 2 #define Anum_pg_rewrite_ev_attr 3 #define Anum_pg_rewrite_ev_type 4 #define Anum_pg_rewrite_ev_enabled 5 #define Anum_pg_rewrite_is_instead 6 ! #define Anum_pg_rewrite_ev_qual 7 ! #define Anum_pg_rewrite_ev_action 8 #endif /* PG_REWRITE_H */ --- 57,79 ---- * compiler constants for pg_rewrite * ---------------- */ ! #define Natts_pg_rewrite 9 #define Anum_pg_rewrite_rulename 1 #define Anum_pg_rewrite_ev_class 2 #define Anum_pg_rewrite_ev_attr 3 #define Anum_pg_rewrite_ev_type 4 #define Anum_pg_rewrite_ev_enabled 5 #define Anum_pg_rewrite_is_instead 6 ! #define Anum_pg_rewrite_ev_kind 7 ! #define Anum_pg_rewrite_ev_qual 8 ! #define Anum_pg_rewrite_ev_action 9 ! ! /* ! * Possible values for ev_kind ! */ ! #define LOCAL_OPTION_IMPLICIT 'l' /* WITH LOCAL CHECK OPTION */ ! #define CASCADED_OPTION_IMPLICIT 'c' /* WITH CASCADED CHECK OPTION */ ! #define NO_OPTION_IMPLICIT 'n' /* no check option specified */ ! #define NO_OPTION_EXPLICIT 'e' /* rule was created by user */ #endif /* PG_REWRITE_H */ *** a/src/include/commands/view.h --- b/src/include/commands/view.h *************** *** 16,21 **** #include "nodes/parsenodes.h" ! extern void DefineView(ViewStmt *stmt, const char *queryString); #endif /* VIEW_H */ --- 16,22 ---- #include "nodes/parsenodes.h" ! extern void DefineView(ViewStmt *stmt, const char *queryString, ! bool checkOption, bool cascade); #endif /* VIEW_H */ *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** *** 2077,2082 **** typedef struct ViewStmt --- 2077,2083 ---- List *aliases; /* target column names */ Node *query; /* the SELECT query */ bool replace; /* replace an existing view? */ + List *options; /* view check options, NIL means no check */ } ViewStmt; /* ---------------------- *** a/src/include/nodes/relation.h --- b/src/include/nodes/relation.h *************** *** 353,359 **** typedef struct RelOptInfo /* information about a base rel (not set for join rels!) */ Index relid; ! RTEKind rtekind; /* RELATION, SUBQUERY, or FUNCTION */ AttrNumber min_attr; /* smallest attrno of rel (often <0) */ AttrNumber max_attr; /* largest attrno of rel */ Relids *attr_needed; /* array indexed [min_attr .. max_attr] */ --- 353,359 ---- /* information about a base rel (not set for join rels!) */ Index relid; ! RTEKind rtekind; /* see parsenodes.h */ AttrNumber min_attr; /* smallest attrno of rel (often <0) */ AttrNumber max_attr; /* largest attrno of rel */ Relids *attr_needed; /* array indexed [min_attr .. max_attr] */ *** a/src/include/rewrite/prs2lock.h --- b/src/include/rewrite/prs2lock.h *************** *** 30,35 **** typedef struct RewriteRule --- 30,36 ---- List *actions; char enabled; bool isInstead; + char ev_kind; } RewriteRule; /* *** a/src/include/rewrite/rewriteDefine.h --- b/src/include/rewrite/rewriteDefine.h *************** *** 28,33 **** extern void DefineQueryRewrite(char *rulename, --- 28,34 ---- Oid event_relid, Node *event_qual, CmdType event_type, + char ev_kind, bool is_instead, bool replace, List *action); *** a/src/include/rewrite/rewriteRemove.h --- b/src/include/rewrite/rewriteRemove.h *************** *** 20,24 **** --- 20,26 ---- extern void RemoveRewriteRule(Oid owningRel, const char *ruleName, DropBehavior behavior, bool missing_ok); extern void RemoveRewriteRuleById(Oid ruleOid); + extern void DeleteImplicitRulesOnEvent(Relation rel, CmdType event_type, + Oid exceptOid); #endif /* REWRITEREMOVE_H */ *** a/src/include/rewrite/rewriteSupport.h --- b/src/include/rewrite/rewriteSupport.h *************** *** 17,22 **** --- 17,33 ---- /* The ON SELECT rule of a view is always named this: */ #define ViewSelectRuleName "_RETURN" + /*------------------------------------------------------------------------------ + * some names to be used for implicit view update rules + *------------------------------------------------------------------------------ + */ + #define INSERTRULENAME "_INSERT" + #define DELETERULENAME "_DELETE" + #define UPDATERULENAME "_UPDATE" + #define NOTHING_INSERTRULENAME "_NOTHING_INSERT" + #define NOTHING_UPDATERULENAME "_NOTHING_UPDATE" + #define NOTHING_DELETERULENAME "_NOTHING_DELETE" + extern bool IsDefinedRewriteRule(Oid owningRel, const char *ruleName); extern void SetRelationRuleStatus(Oid relationId, bool relHasRules, *** a/src/test/regress/expected/alter_table.out --- b/src/test/regress/expected/alter_table.out *************** *** 132,137 **** ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1; --- 132,138 ---- ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1; -- renaming views CREATE VIEW tmp_view (unique1) AS SELECT unique1 FROM tenk1; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules ALTER TABLE tmp_view RENAME TO tmp_view_new; -- hack to ensure we get an indexscan here ANALYZE tenk1; *************** *** 592,597 **** alter table atacc1 alter oid drop not null; --- 593,599 ---- ERROR: cannot alter system column "oid" -- try creating a view and altering that, should fail create view myview as select * from atacc1; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules alter table myview alter column test drop not null; ERROR: "myview" is not a table alter table myview alter column test set not null; *************** *** 659,664 **** ERROR: column "c3" of relation "def_test" does not exist --- 661,667 ---- -- to allow insertions into it, and then alter the view to add -- a default create view def_view_test as select * from def_test; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create rule def_view_test_ins as on insert to def_view_test do instead insert into def_test select new.*; *************** *** 842,847 **** alter table atacc1 drop xmin; --- 845,851 ---- ERROR: cannot drop system column "xmin" -- try creating a view and altering that, should fail create view myview as select * from atacc1; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules select * from myview; b | c | d ---+---+--- *************** *** 1436,1441 **** create table alter1.t1(f1 serial primary key, f2 int check (f2 > 0)); --- 1440,1446 ---- NOTICE: CREATE TABLE will create implicit sequence "t1_f1_seq" for serial column "t1.f1" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" create view alter1.v1 as select * from alter1.t1; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create function alter1.plus1(int) returns int as 'select $1+1' language sql; create domain alter1.posint integer check (value > 0); create type alter1.ctype as (f1 int, f2 text); *** a/src/test/regress/expected/create_view.out --- b/src/test/regress/expected/create_view.out *************** *** 27,34 **** CREATE TABLE viewtest_tbl (a int, b int); --- 27,36 ---- COPY viewtest_tbl FROM stdin; CREATE OR REPLACE VIEW viewtest AS SELECT * FROM viewtest_tbl; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE OR REPLACE VIEW viewtest AS SELECT * FROM viewtest_tbl WHERE a > 10; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT * FROM viewtest; a | b ----+---- *************** *** 38,43 **** SELECT * FROM viewtest; --- 40,46 ---- CREATE OR REPLACE VIEW viewtest AS SELECT a, b FROM viewtest_tbl WHERE a > 5 ORDER BY b DESC; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT * FROM viewtest; a | b ----+---- *************** *** 71,83 **** SET search_path TO temp_view_test, public; --- 74,90 ---- CREATE TEMPORARY TABLE temp_table (a int, id int); -- should be created in temp_view_test schema CREATE VIEW v1 AS SELECT * FROM base_table; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules -- should be created in temp object schema CREATE VIEW v1_temp AS SELECT * FROM temp_table; NOTICE: view "v1_temp" will be a temporary view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules -- should be created in temp object schema CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules -- should be created in temp_views schema CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules -- should fail CREATE VIEW temp_view_test.v3_temp AS SELECT * FROM temp_table; NOTICE: view "v3_temp" will be a temporary view *************** *** 107,124 **** CREATE VIEW v5_temp AS --- 114,138 ---- NOTICE: view "v5_temp" will be a temporary view -- subqueries CREATE VIEW v4 AS SELECT * FROM base_table WHERE id IN (SELECT id FROM base_table2); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v5 AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM base_table2) t2; CREATE VIEW v6 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM base_table2); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v6_temp AS SELECT * FROM base_table WHERE id IN (SELECT id FROM temp_table); NOTICE: view "v6_temp" will be a temporary view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v7_temp AS SELECT t1.id, t2.a FROM base_table t1, (SELECT * FROM temp_table) t2; NOTICE: view "v7_temp" will be a temporary view CREATE VIEW v8_temp AS SELECT * FROM base_table WHERE EXISTS (SELECT 1 FROM temp_table); NOTICE: view "v8_temp" will be a temporary view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v9_temp AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM temp_table); NOTICE: view "v9_temp" will be a temporary view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules -- a view should also be temporary if it references a temporary view CREATE VIEW v10_temp AS SELECT * FROM v7_temp; NOTICE: view "v10_temp" will be a temporary view *************** *** 130,137 **** NOTICE: view "v12_temp" will be a temporary view --- 144,153 ---- CREATE SEQUENCE seq1; CREATE TEMPORARY SEQUENCE seq1_temp; CREATE VIEW v9 AS SELECT seq1.is_called FROM seq1; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW v13_temp AS SELECT seq1_temp.is_called FROM seq1_temp; NOTICE: view "v13_temp" will be a temporary view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT relname FROM pg_class WHERE relname LIKE 'v_' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'temp_view_test') *************** *** 219,224 **** CREATE TEMP TABLE tmptbl (i int, j int); --- 235,241 ---- CREATE VIEW pubview AS SELECT * FROM tbl1 WHERE tbl1.a BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT count(*) FROM pg_class where relname = 'pubview' AND relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname = 'testviewschm2'); count *************** *** 232,237 **** BETWEEN (SELECT d FROM tbl2 WHERE c = 1) AND (SELECT e FROM tbl3 WHERE f = 2) --- 249,255 ---- AND EXISTS (SELECT g FROM tbl4 LEFT JOIN tbl3 ON tbl4.h = tbl3.f) AND NOT EXISTS (SELECT g FROM tbl4 LEFT JOIN tmptbl ON tbl4.h = tmptbl.j); NOTICE: view "mytempview" will be a temporary view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT count(*) FROM pg_class where relname LIKE 'mytempview' And relnamespace IN (SELECT OID FROM pg_namespace WHERE nspname LIKE 'pg_temp%'); count *** a/src/test/regress/expected/drop_if_exists.out --- b/src/test/regress/expected/drop_if_exists.out *************** *** 13,18 **** ERROR: view "test_view_exists" does not exist --- 13,19 ---- DROP VIEW IF EXISTS test_view_exists; NOTICE: view "test_view_exists" does not exist, skipping CREATE VIEW test_view_exists AS select * from test_exists; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules DROP VIEW IF EXISTS test_view_exists; DROP VIEW test_view_exists; ERROR: view "test_view_exists" does not exist *** a/src/test/regress/expected/plancache.out --- b/src/test/regress/expected/plancache.out *************** *** 79,84 **** EXECUTE prepstmt2(123); --- 79,85 ---- -- but should trigger invalidation anyway CREATE TEMP VIEW pcacheview AS SELECT * FROM pcachetest; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules PREPARE vprep AS SELECT * FROM pcacheview; EXECUTE vprep; q1 | q2 *************** *** 236,241 **** select cachebug(); --- 237,245 ---- NOTICE: table "temptable" does not exist, skipping CONTEXT: SQL statement "drop table if exists temptable cascade" PL/pgSQL function "cachebug" line 3 at SQL statement + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules + CONTEXT: SQL statement "create temp view vv as select * from temptable" + PL/pgSQL function "cachebug" line 5 at SQL statement NOTICE: 1 NOTICE: 2 NOTICE: 3 *************** *** 248,253 **** select cachebug(); --- 252,260 ---- NOTICE: drop cascades to view vv CONTEXT: SQL statement "drop table if exists temptable cascade" PL/pgSQL function "cachebug" line 3 at SQL statement + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules + CONTEXT: SQL statement "create temp view vv as select * from temptable" + PL/pgSQL function "cachebug" line 5 at SQL statement NOTICE: 1 NOTICE: 2 NOTICE: 3 *** a/src/test/regress/expected/portals.out --- b/src/test/regress/expected/portals.out *************** *** 1229,1234 **** ROLLBACK; --- 1229,1235 ---- -- WHERE CURRENT OF may someday work with views, but today is not that day. -- For now, just make sure it errors out cleanly. CREATE TEMP VIEW ucview AS SELECT * FROM uctest; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE RULE ucrule AS ON DELETE TO ucview DO INSTEAD DELETE FROM uctest WHERE f1 = OLD.f1; BEGIN; *** a/src/test/regress/expected/privileges.out --- b/src/test/regress/expected/privileges.out *************** *** 189,197 **** DELETE FROM atest3; -- ok --- 189,200 ---- -- views SET SESSION AUTHORIZATION regressuser3; CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules /* The next *should* fail, but it's not implemented that way yet. */ CREATE VIEW atestv2 AS SELECT * FROM atest2; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT * FROM atestv1; -- ok a | b ---+----- *************** *** 219,224 **** SELECT * FROM atestv3; -- ok --- 222,228 ---- (0 rows) CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules SELECT * FROM atestv4; -- ok one | two | three -----+-----+------- *** a/src/test/regress/expected/returning.out --- b/src/test/regress/expected/returning.out *************** *** 195,200 **** SELECT * FROM foochild; --- 195,201 ---- DROP TABLE foochild; -- Rules and views CREATE TEMP VIEW voo AS SELECT f1, f2 FROM foo; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules CREATE RULE voo_i AS ON INSERT TO voo DO INSTEAD INSERT INTO foo VALUES(new.*, 57); INSERT INTO voo VALUES(11,'zit'); *** a/src/test/regress/expected/rules.out --- b/src/test/regress/expected/rules.out *************** *** 10,15 **** create table rtest_t1 (a int4, b int4); --- 10,16 ---- create table rtest_t2 (a int4, b int4); create table rtest_t3 (a int4, b int4); create view rtest_v1 as select * from rtest_t1; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create rule rtest_v1_ins as on insert to rtest_v1 do instead insert into rtest_t1 values (new.a, new.b); create rule rtest_v1_upd as on update to rtest_v1 do instead *************** *** 755,763 **** create table rtest_view3 (a int4, b text); --- 756,767 ---- create table rtest_view4 (a int4, b text, c int4); create view rtest_vview1 as select a, b from rtest_view1 X where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create view rtest_vview2 as select a, b from rtest_view1 where v; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create view rtest_vview3 as select a, b from rtest_vview2 X where 0 < (select count(*) from rtest_view2 Y where Y.a = X.a); + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create view rtest_vview4 as select X.a, X.b, count(Y.a) as refcount from rtest_view1 X, rtest_view2 Y where X.a = Y.a *************** *** 1333,1340 **** SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; ! tablename | rulename | definition ! ---------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary); --- 1337,1344 ---- SELECT tablename, rulename, definition FROM pg_rules ORDER BY tablename, rulename; ! tablename | rulename | definition ! ---------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ pg_settings | pg_settings_n | CREATE RULE pg_settings_n AS ON UPDATE TO pg_settings DO INSTEAD NOTHING; pg_settings | pg_settings_u | CREATE RULE pg_settings_u AS ON UPDATE TO pg_settings WHERE (new.name = old.name) DO SELECT set_config(old.name, new.setting, false) AS set_config; rtest_emp | rtest_emp_del | CREATE RULE rtest_emp_del AS ON DELETE TO rtest_emp DO INSERT INTO rtest_emplog (ename, who, action, newsal, oldsal) VALUES (old.ename, "current_user"(), 'fired'::bpchar, '$0.00'::money, old.salary); *************** *** 1359,1370 **** SELECT tablename, rulename, definition FROM pg_rules rtest_v1 | rtest_v1_del | CREATE RULE rtest_v1_del AS ON DELETE TO rtest_v1 DO INSTEAD DELETE FROM rtest_t1 WHERE (rtest_t1.a = old.a); rtest_v1 | rtest_v1_ins | CREATE RULE rtest_v1_ins AS ON INSERT TO rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) VALUES (new.a, new.b); rtest_v1 | rtest_v1_upd | CREATE RULE rtest_v1_upd AS ON UPDATE TO rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b WHERE (rtest_t1.a = old.a); shoelace | shoelace_del | CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE (shoelace_data.sl_name = old.sl_name); shoelace | shoelace_ins | CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit); shoelace | shoelace_upd | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name); shoelace_data | log_shoelace | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone); 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 --- 1363,1383 ---- rtest_v1 | rtest_v1_del | CREATE RULE rtest_v1_del AS ON DELETE TO rtest_v1 DO INSTEAD DELETE FROM rtest_t1 WHERE (rtest_t1.a = old.a); rtest_v1 | rtest_v1_ins | CREATE RULE rtest_v1_ins AS ON INSERT TO rtest_v1 DO INSTEAD INSERT INTO rtest_t1 (a, b) VALUES (new.a, new.b); rtest_v1 | rtest_v1_upd | CREATE RULE rtest_v1_upd AS ON UPDATE TO rtest_v1 DO INSTEAD UPDATE rtest_t1 SET a = new.a, b = new.b WHERE (rtest_t1.a = old.a); + rtest_vview1 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview1 DO INSTEAD DELETE FROM ONLY rtest_view1 x WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = x.a) ELSE (x.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = x.b) ELSE (x.b IS NULL) END)) RETURNING old.a, old.b; + rtest_vview1 | _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO rtest_vview1 DO INSTEAD INSERT INTO rtest_view1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b; + rtest_vview1 | _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO rtest_vview1 DO INSTEAD UPDATE ONLY rtest_view1 x SET a = new.a, b = new.b WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = x.a) ELSE (x.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = x.b) ELSE (x.b IS NULL) END)) RETURNING new.a, new.b; + rtest_vview2 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview2 DO INSTEAD DELETE FROM ONLY rtest_view1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = rtest_view1.a) ELSE (rtest_view1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = rtest_view1.b) ELSE (rtest_view1.b IS NULL) END)) RETURNING old.a, old.b; + rtest_vview2 | _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO rtest_vview2 DO INSTEAD INSERT INTO rtest_view1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b; + rtest_vview2 | _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO rtest_vview2 DO INSTEAD UPDATE ONLY rtest_view1 SET a = new.a, b = new.b WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = rtest_view1.a) ELSE (rtest_view1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = rtest_view1.b) ELSE (rtest_view1.b IS NULL) END)) RETURNING new.a, new.b; + rtest_vview3 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview3 DO INSTEAD DELETE FROM ONLY rtest_vview2 x WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = x.a) ELSE (x.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = x.b) ELSE (x.b IS NULL) END)) RETURNING old.a, old.b; + rtest_vview3 | _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO rtest_vview3 DO INSTEAD INSERT INTO rtest_vview2 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b; + rtest_vview3 | _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO rtest_vview3 DO INSTEAD UPDATE ONLY rtest_vview2 x SET a = new.a, b = new.b WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = x.a) ELSE (x.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = x.b) ELSE (x.b IS NULL) END)) RETURNING new.a, new.b; shoelace | shoelace_del | CREATE RULE shoelace_del AS ON DELETE TO shoelace DO INSTEAD DELETE FROM shoelace_data WHERE (shoelace_data.sl_name = old.sl_name); shoelace | shoelace_ins | CREATE RULE shoelace_ins AS ON INSERT TO shoelace DO INSTEAD INSERT INTO shoelace_data (sl_name, sl_avail, sl_color, sl_len, sl_unit) VALUES (new.sl_name, new.sl_avail, new.sl_color, new.sl_len, new.sl_unit); shoelace | shoelace_upd | CREATE RULE shoelace_upd AS ON UPDATE TO shoelace DO INSTEAD UPDATE shoelace_data SET sl_name = new.sl_name, sl_avail = new.sl_avail, sl_color = new.sl_color, sl_len = new.sl_len, sl_unit = new.sl_unit WHERE (shoelace_data.sl_name = old.sl_name); shoelace_data | log_shoelace | CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data WHERE (new.sl_avail <> old.sl_avail) DO INSERT INTO shoelace_log (sl_name, sl_avail, log_who, log_when) VALUES (new.sl_name, new.sl_avail, 'Al Bundy'::name, 'Thu Jan 01 00:00:00 1970'::timestamp without time zone); 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); ! (38 rows) -- -- CREATE OR REPLACE RULE *************** *** 1466,1471 **** insert into test_2 (name) values ('Test 4'); --- 1479,1485 ---- insert into test_3 (name) values ('Test 5'); insert into test_3 (name) values ('Test 6'); create view id_ordered as select * from id order by id; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create rule update_id_ordered as on update to id_ordered do instead update id set name = new.name where id = old.id; select * from id_ordered; *** a/src/test/regress/expected/subselect.out --- b/src/test/regress/expected/subselect.out *************** *** 349,354 **** create temp table shipped ( --- 349,355 ---- ); create temp view shipped_view as select * from shipped where ttype = 'wt'; + NOTICE: CREATE VIEW will create implicit INSERT/UPDATE/DELETE rules create rule shipped_view_insert as on insert to shipped_view do instead insert into shipped values('wt', new.ordnum, new.partnum, new.value); insert into parts (partnum, cost) values (1, 1234.56);