diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml new file mode 100644 index 8102ec2..4505290 *** a/doc/src/sgml/ref/create_view.sgml --- b/doc/src/sgml/ref/create_view.sgml *************** CREATE VIEW vista AS SELECT text 'Hello *** 319,334 **** ! All columns in the view's select list must be simple references to ! columns of the underlying relation. They cannot be expressions, ! literals or functions. System columns cannot be referenced, either. ! ! ! ! ! ! No column of the underlying relation can appear more than once in ! the view's select list. --- 319,326 ---- ! The view's select list must not contain any aggregates, window functions ! or set-returning functions. *************** CREATE VIEW vista AS SELECT text 'Hello *** 341,346 **** --- 333,346 ---- + An automatically updatable view may contain a mix of updatable and + non-updatable columns. A column is updatable if it is a simple reference + to an updatable column of the underlying base relation; otherwise the + column is read-only, and an error will be raised if an INSERT + or UPDATE statement attempts to assign a value to it. + + + If the view is automatically updatable the system will convert any INSERT, UPDATE or DELETE statement on the view into the corresponding statement on the underlying base *************** CREATE VIEW pg_comedies AS *** 435,440 **** --- 435,459 ---- + Create a view with a mix of updatable and non-updatable columns: + + + CREATE VIEW comedies AS + SELECT f.*, + country_code_to_name(f.country_code) AS country + (SELECT avg(r.rating) + FROM user_ratings r + WHERE r.film_id = f.id) AS avg_rating + FROM films f + WHERE f.kind = 'Comedy'; + + This view will support INSERT, UPDATE and + DELETE. All the columns from the films table will + be updatable, whereas the computed columns country and + avg_rating will be read-only. + + + Create a recursive view consisting of the numbers from 1 to 100: CREATE RECURSIVE VIEW nums_1_100 (n) AS diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c new file mode 100644 index adc74dd..fc257f6 *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** ATExecSetRelOptions(Relation rel, List * *** 8800,8806 **** if (check_option) { const char *view_updatable_error = ! view_query_is_auto_updatable(view_query, security_barrier); if (view_updatable_error) ereport(ERROR, --- 8800,8807 ---- if (check_option) { const char *view_updatable_error = ! view_query_is_auto_updatable(view_query, ! security_barrier, true); if (view_updatable_error) ereport(ERROR, diff --git a/src/backend/commands/view.c b/src/backend/commands/view.c new file mode 100644 index 832de43..aca40e7 *** a/src/backend/commands/view.c --- b/src/backend/commands/view.c *************** DefineView(ViewStmt *stmt, const char *q *** 469,475 **** if (check_option) { const char *view_updatable_error = ! view_query_is_auto_updatable(viewParse, security_barrier); if (view_updatable_error) ereport(ERROR, --- 469,475 ---- if (check_option) { const char *view_updatable_error = ! view_query_is_auto_updatable(viewParse, security_barrier, true); if (view_updatable_error) ereport(ERROR, diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index c55fb14..23f4fe1 *** a/src/backend/rewrite/rewriteHandler.c --- b/src/backend/rewrite/rewriteHandler.c *************** static List *matchLocks(CmdType event, R *** 61,66 **** --- 61,68 ---- int varno, Query *parsetree); static Query *fireRIRrules(Query *parsetree, List *activeRIRs, bool forUpdatePushedDown); + static bool view_has_instead_trigger(Relation view, CmdType event); + static Bitmapset *adjust_view_column_set(Bitmapset *cols, List *targetlist); /* *************** adjustJoinTreeList(Query *parsetree, boo *** 616,627 **** * and UPDATE, replace explicit DEFAULT specifications with column default * expressions. * ! * 2. For an UPDATE on a view, add tlist entries for any unassigned-to ! * attributes, assigning them their old values. These will later get ! * expanded to the output values of the view. (This is equivalent to what ! * the planner's expand_targetlist() will do for UPDATE on a regular table, ! * but it's more convenient to do it here while we still have easy access ! * to the view's original RT index.) * * 3. Merge multiple entries for the same target attribute, or declare error * if we can't. Multiple entries are only allowed for INSERT/UPDATE of --- 618,634 ---- * and UPDATE, replace explicit DEFAULT specifications with column default * expressions. * ! * 2. For an UPDATE on a trigger-updatable view, add tlist entries for any ! * unassigned-to attributes, assigning them their old values. These will ! * later get expanded to the output values of the view. (This is equivalent ! * to what the planner's expand_targetlist() will do for UPDATE on a regular ! * table, but it's more convenient to do it here while we still have easy ! * access to the view's original RT index.) This is only necessary for ! * trigger-updatable views, for which the view remains the result relation of ! * the query. For auto-updatable we must not do this, since it might add ! * assignments to non-updatable view columns. For rule-updatable views it is ! * unnecessary extra work, since the query will be rewritten with a different ! * result relation which will be processed when we recurse via RewriteQuery. * * 3. Merge multiple entries for the same target attribute, or declare error * if we can't. Multiple entries are only allowed for INSERT/UPDATE of *************** rewriteTargetListIU(Query *parsetree, Re *** 783,793 **** } /* ! * For an UPDATE on a view, provide a dummy entry whenever there is no ! * explicit assignment. */ if (new_tle == NULL && commandType == CMD_UPDATE && ! target_relation->rd_rel->relkind == RELKIND_VIEW) { Node *new_expr; --- 790,801 ---- } /* ! * For an UPDATE on a trigger-updatable view, provide a dummy entry ! * whenever there is no explicit assignment. */ if (new_tle == NULL && commandType == CMD_UPDATE && ! target_relation->rd_rel->relkind == RELKIND_VIEW && ! view_has_instead_trigger(target_relation, CMD_UPDATE)) { Node *new_expr; *************** get_view_query(Relation view) *** 1895,1901 **** * view_has_instead_trigger - does view have an INSTEAD OF trigger for event? * * If it does, we don't want to treat it as auto-updatable. This test can't ! * be folded into view_is_auto_updatable because it's not an error condition. */ static bool view_has_instead_trigger(Relation view, CmdType event) --- 1903,1910 ---- * view_has_instead_trigger - does view have an INSTEAD OF trigger for event? * * If it does, we don't want to treat it as auto-updatable. This test can't ! * be folded into view_query_is_auto_updatable because it's not an error ! * condition. */ static bool view_has_instead_trigger(Relation view, CmdType event) *************** view_has_instead_trigger(Relation view, *** 1925,1979 **** /* ! * view_is_auto_updatable - ! * Retrive the view definition and options and then determine if the view ! * can be auto-updated by calling view_query_is_auto_updatable(). Returns ! * NULL or a message string giving the reason the view is not auto ! * updateable. See view_query_is_auto_updatable() for details. ! * ! * The only view option which affects if a view can be auto-updated, today, ! * is the security_barrier option. If other options are added later, they ! * will also need to be handled here. ! * ! * Caller must have verified that the relation is a view! ! * ! * Note that the checks performed here are local to this view. We do not ! * check whether the view's underlying base relation is updatable; that ! * will be dealt with in later, recursive processing. * ! * Also note that we don't check for INSTEAD triggers or rules here; those ! * also prevent auto-update, but they must be checked for by the caller. */ ! const char * ! view_is_auto_updatable(Relation view) { ! Query *viewquery = get_view_query(view); ! bool security_barrier = RelationIsSecurityView(view); ! return view_query_is_auto_updatable(viewquery, security_barrier); } /* ! * view_query_is_auto_updatable - ! * Test if the specified view definition can be automatically updated, given ! * the view's options (currently only security_barrier affects a view's ! * auto-updatable status). * ! * This will either return NULL (if the view can be updated) or a message ! * string giving the reason that it cannot be. * ! * Note that the checks performed here are only based on the view ! * definition. We do not check whether any base relations referred to by ! * the view are updatable. */ const char * ! view_query_is_auto_updatable(Query *viewquery, bool security_barrier) { RangeTblRef *rtr; RangeTblEntry *base_rte; - Bitmapset *bms; - ListCell *cell; /*---------- * Check if the view is simply updatable. According to SQL-92 this means: --- 1934,1997 ---- /* ! * view_col_is_auto_updatable - test whether the specified column of a view ! * is auto-updatable. Returns NULL (if the column can be updated) or a message ! * string giving the reason that it cannot be. * ! * Note that the checks performed here are local to this view. We do not check ! * whether the referenced column of the underlying base relation is updatable. */ ! static const char * ! view_col_is_auto_updatable(RangeTblRef *rtr, TargetEntry *tle) { ! Var *var = (Var *) tle->expr; ! /* ! * For now, the only updatable columns we support are those that are Vars ! * referring to user columns of the underlying base relation. ! * ! * The view targetlist may contain resjunk columns (e.g., a view defined ! * like "SELECT * FROM t ORDER BY a+b" is auto-updatable) but such columns ! * are not auto-updatable, and in fact should never appear in the outer ! * query's targetlist. ! */ ! if (tle->resjunk) ! return gettext_noop("Junk view columns are not updatable."); ! ! if (!IsA(var, Var) || ! var->varno != rtr->rtindex || ! var->varlevelsup != 0) ! return gettext_noop("View columns that are not columns of their base relation are not updatable."); ! ! if (var->varattno < 0) ! return gettext_noop("View columns that refer to system columns are not updatable."); ! ! if (var->varattno == 0) ! return gettext_noop("View columns that return whole-row references are not updatable."); ! ! return NULL; /* the view column is updatable */ } /* ! * view_query_is_auto_updatable - test whether the specified view definition ! * represents an auto-updatable view. Returns NULL (if the view can be updated) ! * or a message string giving the reason that it cannot be. * ! * If check_cols is true, the view is required to have at least one updatable ! * column (necessary for INSERT/UPDATE). Otherwise the view's columns are not ! * checked for updatability. See also view_cols_are_auto_updatable. * ! * Note that the checks performed here are only based on the view definition. ! * We do not check whether any base relations referred to by the view are ! * updatable. */ const char * ! view_query_is_auto_updatable(Query *viewquery, bool security_barrier, ! bool check_cols) { RangeTblRef *rtr; RangeTblEntry *base_rte; /*---------- * Check if the view is simply updatable. According to SQL-92 this means: *************** view_query_is_auto_updatable(Query *view *** 1990,2000 **** --- 2008,2025 ---- * arise in Postgres, since any such sub-query will not see any updates * executed by the outer query anyway, thanks to MVCC snapshotting.) * + * We also relax the second restriction by supporting part of SQL:1999 + * feature T111, which allows for a mix of updatable and non-updatable + * columns, provided that an INSERT or UPDATE doesn't attempt to assign to + * a non-updatable column. + * * In addition we impose these constraints, involving features that are * not part of SQL-92: * - No CTEs (WITH clauses). * - No OFFSET or LIMIT clauses (this matches a SQL:2008 restriction). * - No system columns (including whole-row references) in the tlist. + * - No window functions in the tlist. + * - No set-returning functions in the tlist. * * Note that we do these checks without recursively expanding the view. * If the base relation is a view, we'll recursively deal with it later. *************** view_query_is_auto_updatable(Query *view *** 2019,2024 **** --- 2044,2067 ---- return gettext_noop("Views containing LIMIT or OFFSET are not automatically updatable."); /* + * We must not allow window functions or set returning functions in the + * targetlist. Otherwise we might end up inserting them into the quals of + * the main query. We must also check for aggregates in the targetlist in + * case they appear without a GROUP BY. + * + * These restrictions ensure that each row of the view corresponds to a + * unique row in the underlying base relation. + */ + if (viewquery->hasAggs) + return gettext_noop("Views that return aggregate functions are not automatically updatable"); + + if (viewquery->hasWindowFuncs) + return gettext_noop("Views that return window functions are not automatically updatable"); + + if (expression_returns_set((Node *) viewquery->targetList)) + return gettext_noop("Views that return set-returning functions are not automatically updatable."); + + /* * For now, we also don't support security-barrier views, because of the * difficulty of keeping upper-level qual expressions away from * lower-level data. This might get relaxed in the future. *************** view_query_is_auto_updatable(Query *view *** 2045,2086 **** return gettext_noop("Views that do not select from a single table or view are not automatically updatable."); /* ! * The view's targetlist entries should all be Vars referring to user ! * columns of the base relation, and no two should refer to the same ! * column. ! * ! * Note however that we should ignore resjunk entries. This proviso is ! * relevant because ORDER BY is not disallowed, and we shouldn't reject a ! * view defined like "SELECT * FROM t ORDER BY a+b". */ ! bms = NULL; ! foreach(cell, viewquery->targetList) { ! TargetEntry *tle = (TargetEntry *) lfirst(cell); ! Var *var = (Var *) tle->expr; ! if (tle->resjunk) ! continue; ! if (!IsA(var, Var) || ! var->varno != rtr->rtindex || ! var->varlevelsup != 0) ! return gettext_noop("Views that return columns that are not columns of their base relation are not automatically updatable."); ! if (var->varattno < 0) ! return gettext_noop("Views that return system columns are not automatically updatable."); ! if (var->varattno == 0) ! return gettext_noop("Views that return whole-row references are not automatically updatable."); - if (bms_is_member(var->varattno, bms)) - return gettext_noop("Views that return the same column more than once are not automatically updatable."); ! bms = bms_add_member(bms, var->varattno); } - bms_free(bms); /* just for cleanliness */ ! return NULL; /* the view is simply updatable */ } --- 2088,2191 ---- return gettext_noop("Views that do not select from a single table or view are not automatically updatable."); /* ! * Check that the view has at least one updatable column. This is required ! * for INSERT/UPDATE but not for DELETE. */ ! if (check_cols) { ! ListCell *cell; ! bool found; ! found = false; ! foreach(cell, viewquery->targetList) ! { ! TargetEntry *tle = (TargetEntry *) lfirst(cell); ! if (view_col_is_auto_updatable(rtr, tle) == NULL) ! { ! found = true; ! break; ! } ! } ! if (!found) ! return gettext_noop("Views that have no updatable columns are not automatically updatable."); ! } ! return NULL; /* the view is updatable */ ! } ! /* ! * view_cols_are_auto_updatable - test whether the all of the required columns ! * of an auto-updatable view are actually updatable. Returns NULL (if all the ! * required columns can be updated) or a message string giving the reason that ! * they cannot be. ! * ! * This should be used for INSERT/UPDATE to ensure that we don't attempt to ! * assign to any non-updatable columns. ! * ! * Additionally it may be used to retrieve the set of updatable columns in the ! * view or, if one or more of the required columns is not updatable, the name ! * of the first offending non-updatable column. ! * ! * The caller must have already verified that this is an auto-updatable view ! * using view_query_is_auto_updatable. ! * ! * Note that the checks performed here are only based on the view definition. ! * We do not check whether the referenced columns of the base relation are ! * updatable. ! */ ! static const char * ! view_cols_are_auto_updatable(Query *viewquery, ! Bitmapset *required_cols, ! Bitmapset **updatable_cols, ! char **non_updatable_col) ! { ! RangeTblRef *rtr; ! AttrNumber col; ! ListCell *cell; ! ! /* ! * The caller should have verified that this view is auto-updatable and ! * so there should be a single base relation. ! */ ! Assert(list_length(viewquery->jointree->fromlist) == 1); ! rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); ! Assert(IsA(rtr, RangeTblRef)); ! ! /* Initialize the optional return values */ ! if (updatable_cols != NULL) ! *updatable_cols = NULL; ! if (non_updatable_col != NULL) ! *non_updatable_col = NULL; ! ! /* Test each view column for updatability */ ! col = -FirstLowInvalidHeapAttributeNumber; ! foreach(cell, viewquery->targetList) ! { ! TargetEntry *tle = (TargetEntry *) lfirst(cell); ! const char *col_update_detail; ! ! col++; ! col_update_detail = view_col_is_auto_updatable(rtr, tle); ! ! if (col_update_detail == NULL) ! { ! /* The column is updatable */ ! if (updatable_cols != NULL) ! *updatable_cols = bms_add_member(*updatable_cols, col); ! } ! else if (bms_is_member(col, required_cols)) ! { ! /* The required column is not updatable */ ! if (non_updatable_col != NULL) ! *non_updatable_col = tle->resname; ! return col_update_detail; ! } } ! return NULL; /* all the required view columns are updatable */ } *************** view_query_is_auto_updatable(Query *view *** 2088,2093 **** --- 2193,2204 ---- * relation_is_updatable - determine which update events the specified * relation supports. * + * Note that views may contain a mix of updatable and non-updatable columns. + * For a view to support INSERT/UPDATE it must have at least one updatable + * column, but there is no such restriction for DELETE. If include_cols is + * non-NULL, then only the specified columns are considered when testing for + * updatability. + * * This is used for the information_schema views, which have separate concepts * of "updatable" and "trigger updatable". A relation is "updatable" if it * can be updated without the need for triggers (either because it has a *************** view_query_is_auto_updatable(Query *view *** 2105,2111 **** * so that we can test for UPDATE plus DELETE support in a single call.) */ int ! relation_is_updatable(Oid reloid, bool include_triggers) { int events = 0; Relation rel; --- 2216,2224 ---- * so that we can test for UPDATE plus DELETE support in a single call.) */ int ! relation_is_updatable(Oid reloid, ! bool include_triggers, ! Bitmapset *include_cols) { int events = 0; Relation rel; *************** relation_is_updatable(Oid reloid, bool i *** 2200,2231 **** } /* Check if this is an automatically updatable view */ ! if (rel->rd_rel->relkind == RELKIND_VIEW && ! view_is_auto_updatable(rel) == NULL) { ! Query *viewquery; ! RangeTblRef *rtr; ! RangeTblEntry *base_rte; ! Oid baseoid; ! ! /* The base relation must also be updatable */ ! viewquery = get_view_query(rel); ! rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); ! base_rte = rt_fetch(rtr->rtindex, viewquery->rtable); ! Assert(base_rte->rtekind == RTE_RELATION); ! if (base_rte->relkind == RELKIND_RELATION) ! { ! /* Tables are always updatable */ ! relation_close(rel, AccessShareLock); ! return ALL_EVENTS; ! } ! else { ! /* Do a recursive check for any other kind of base relation */ ! baseoid = base_rte->relid; ! relation_close(rel, AccessShareLock); ! return relation_is_updatable(baseoid, include_triggers); } } --- 2313,2369 ---- } /* Check if this is an automatically updatable view */ ! if (rel->rd_rel->relkind == RELKIND_VIEW) { ! Query *viewquery = get_view_query(rel); ! if (view_query_is_auto_updatable(viewquery, ! RelationIsSecurityView(rel), ! false) == NULL) { ! Bitmapset *updatable_cols; ! int auto_events; ! RangeTblRef *rtr; ! RangeTblEntry *base_rte; ! Oid baseoid; ! ! /* ! * Determine which of the view's columns are updatable. If there ! * are none within the set of of columns we are looking at, then ! * the view doesn't support INSERT/UPDATE, but it may still ! * support DELETE. ! */ ! view_cols_are_auto_updatable(viewquery, NULL, ! &updatable_cols, NULL); ! ! if (include_cols != NULL) ! updatable_cols = bms_int_members(updatable_cols, include_cols); ! ! if (bms_is_empty(updatable_cols)) ! auto_events = (1 << CMD_DELETE); /* May support DELETE */ ! else ! auto_events = ALL_EVENTS; /* May support all events */ ! ! /* ! * The base relation must also support these update commands. ! * Tables are always updatable, but for any other kind of base ! * relation we must do a recursive check limited to the columns ! * referenced by the locally updatable columns in this view. ! */ ! rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); ! base_rte = rt_fetch(rtr->rtindex, viewquery->rtable); ! Assert(base_rte->rtekind == RTE_RELATION); ! ! if (base_rte->relkind != RELKIND_RELATION) ! { ! baseoid = base_rte->relid; ! include_cols = adjust_view_column_set(updatable_cols, ! viewquery->targetList); ! auto_events &= relation_is_updatable(baseoid, ! include_triggers, ! include_cols); ! } ! events |= auto_events; } } *************** relation_is_updatable(Oid reloid, bool i *** 2241,2247 **** * This is used with simply-updatable views to map column-permissions sets for * the view columns onto the matching columns in the underlying base relation. * The targetlist is expected to be a list of plain Vars of the underlying ! * relation (as per the checks above in view_is_auto_updatable). */ static Bitmapset * adjust_view_column_set(Bitmapset *cols, List *targetlist) --- 2379,2385 ---- * This is used with simply-updatable views to map column-permissions sets for * the view columns onto the matching columns in the underlying base relation. * The targetlist is expected to be a list of plain Vars of the underlying ! * relation (as per the checks above in view_query_is_auto_updatable). */ static Bitmapset * adjust_view_column_set(Bitmapset *cols, List *targetlist) *************** adjust_view_column_set(Bitmapset *cols, *** 2319,2326 **** static Query * rewriteTargetView(Query *parsetree, Relation view) { - const char *auto_update_detail; Query *viewquery; RangeTblRef *rtr; int base_rt_index; int new_rt_index; --- 2457,2464 ---- static Query * rewriteTargetView(Query *parsetree, Relation view) { Query *viewquery; + const char *auto_update_detail; RangeTblRef *rtr; int base_rt_index; int new_rt_index; *************** rewriteTargetView(Query *parsetree, Rela *** 2331,2338 **** List *view_targetlist; ListCell *lc; ! /* The view must be simply updatable, else fail */ ! auto_update_detail = view_is_auto_updatable(view); if (auto_update_detail) { /* messages here should match execMain.c's CheckValidResultRel */ --- 2469,2482 ---- List *view_targetlist; ListCell *lc; ! /* The view must be updatable, else fail */ ! viewquery = get_view_query(view); ! ! auto_update_detail = ! view_query_is_auto_updatable(viewquery, ! RelationIsSecurityView(view), ! parsetree->commandType != CMD_DELETE); ! if (auto_update_detail) { /* messages here should match execMain.c's CheckValidResultRel */ *************** rewriteTargetView(Query *parsetree, Rela *** 2369,2383 **** } } /* Locate RTE describing the view in the outer query */ view_rte = rt_fetch(parsetree->resultRelation, parsetree->rtable); /* ! * If we get here, view_is_auto_updatable() has verified that the view ! * contains a single base relation. */ - viewquery = get_view_query(view); - Assert(list_length(viewquery->jointree->fromlist) == 1); rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); Assert(IsA(rtr, RangeTblRef)); --- 2513,2582 ---- } } + /* + * For INSERT/UPDATE the modified columns must all be updatable. Note that + * we get the modified columns from the query's targetlist, not from the + * result RTE's modifiedCols set, since rewriteTargetListIU may have added + * additional targetlist entries for view defaults, and these must also be + * updatable. + */ + if (parsetree->commandType != CMD_DELETE) + { + Bitmapset *modified_cols = NULL; + char *non_updatable_col; + + foreach(lc, parsetree->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + + if (!tle->resjunk) + modified_cols = bms_add_member(modified_cols, + tle->resno - FirstLowInvalidHeapAttributeNumber); + } + + auto_update_detail = view_cols_are_auto_updatable(viewquery, + modified_cols, + NULL, + &non_updatable_col); + if (auto_update_detail) + { + /* + * This is a different error, caused by an attempt to update a + * non-updatable column in an otherwise updatable view. + */ + switch (parsetree->commandType) + { + case CMD_INSERT: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot insert into column \"%s\" of view \"%s\"", + non_updatable_col, + RelationGetRelationName(view)), + errdetail_internal("%s", _(auto_update_detail)))); + break; + case CMD_UPDATE: + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot update column \"%s\" of view \"%s\"", + non_updatable_col, + RelationGetRelationName(view)), + errdetail_internal("%s", _(auto_update_detail)))); + break; + default: + elog(ERROR, "unrecognized CmdType: %d", + (int) parsetree->commandType); + break; + } + } + } + /* Locate RTE describing the view in the outer query */ view_rte = rt_fetch(parsetree->resultRelation, parsetree->rtable); /* ! * If we get here, view_query_is_auto_updatable() has verified that the ! * view contains a single base relation. */ Assert(list_length(viewquery->jointree->fromlist) == 1); rtr = (RangeTblRef *) linitial(viewquery->jointree->fromlist); Assert(IsA(rtr, RangeTblRef)); diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c new file mode 100644 index aecdcd0..8aba906 *** a/src/backend/utils/adt/misc.c --- b/src/backend/utils/adt/misc.c *************** *** 20,25 **** --- 20,26 ---- #include #include + #include "access/sysattr.h" #include "catalog/catalog.h" #include "catalog/pg_tablespace.h" #include "catalog/pg_type.h" *************** pg_relation_is_updatable(PG_FUNCTION_ARG *** 547,563 **** Oid reloid = PG_GETARG_OID(0); bool include_triggers = PG_GETARG_BOOL(1); ! PG_RETURN_INT32(relation_is_updatable(reloid, include_triggers)); } /* * pg_column_is_updatable - determine whether a column is updatable * ! * Currently we just check whether the column's relation is updatable. ! * Eventually we might allow views to have some updatable and some ! * non-updatable columns. ! * ! * Also, this function encapsulates the decision about just what * information_schema.columns.is_updatable actually means. It's not clear * whether deletability of the column's relation should be required, so * we want that decision in C code where we could change it without initdb. --- 548,560 ---- Oid reloid = PG_GETARG_OID(0); bool include_triggers = PG_GETARG_BOOL(1); ! PG_RETURN_INT32(relation_is_updatable(reloid, include_triggers, NULL)); } /* * pg_column_is_updatable - determine whether a column is updatable * ! * This function encapsulates the decision about just what * information_schema.columns.is_updatable actually means. It's not clear * whether deletability of the column's relation should be required, so * we want that decision in C code where we could change it without initdb. *************** pg_column_is_updatable(PG_FUNCTION_ARGS) *** 567,572 **** --- 564,570 ---- { Oid reloid = PG_GETARG_OID(0); AttrNumber attnum = PG_GETARG_INT16(1); + AttrNumber col = attnum - FirstLowInvalidHeapAttributeNumber; bool include_triggers = PG_GETARG_BOOL(2); int events; *************** pg_column_is_updatable(PG_FUNCTION_ARGS) *** 574,580 **** if (attnum <= 0) PG_RETURN_BOOL(false); ! events = relation_is_updatable(reloid, include_triggers); /* We require both updatability and deletability of the relation */ #define REQ_EVENTS ((1 << CMD_UPDATE) | (1 << CMD_DELETE)) --- 572,579 ---- if (attnum <= 0) PG_RETURN_BOOL(false); ! events = relation_is_updatable(reloid, include_triggers, ! bms_make_singleton(col)); /* We require both updatability and deletability of the relation */ #define REQ_EVENTS ((1 << CMD_UPDATE) | (1 << CMD_DELETE)) diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h new file mode 100644 index f0604b0..c959590 *** a/src/include/rewrite/rewriteHandler.h --- b/src/include/rewrite/rewriteHandler.h *************** extern void AcquireRewriteLocks(Query *p *** 22,30 **** extern Node *build_column_default(Relation rel, int attrno); extern Query *get_view_query(Relation view); - extern const char *view_is_auto_updatable(Relation view); extern const char *view_query_is_auto_updatable(Query *viewquery, ! bool security_barrier); ! extern int relation_is_updatable(Oid reloid, bool include_triggers); #endif /* REWRITEHANDLER_H */ --- 22,32 ---- extern Node *build_column_default(Relation rel, int attrno); extern Query *get_view_query(Relation view); extern const char *view_query_is_auto_updatable(Query *viewquery, ! bool security_barrier, ! bool check_cols); ! extern int relation_is_updatable(Oid reloid, ! bool include_triggers, ! Bitmapset *include_cols); #endif /* REWRITEHANDLER_H */ diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out new file mode 100644 index 4af9354..e463b43 *** a/src/test/regress/expected/updatable_views.out --- b/src/test/regress/expected/updatable_views.out *************** *** 1,7 **** -- -- UPDATABLE VIEWS -- ! -- check that non-updatable views are rejected with useful error messages CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported --- 1,8 ---- -- -- UPDATABLE VIEWS -- ! -- check that non-updatable views and columns are rejected with useful error ! -- messages CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); CREATE VIEW ro_view1 AS SELECT DISTINCT a, b FROM base_tbl; -- DISTINCT not supported *************** CREATE VIEW ro_view10 AS SELECT 1 AS a; *** 17,34 **** CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable ! CREATE VIEW ro_view14 AS SELECT ctid FROM base_tbl; -- System columns not supported ! CREATE VIEW ro_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function in targetlist ! CREATE VIEW ro_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable CREATE VIEW ro_view18 WITH (security_barrier = true) AS SELECT * FROM base_tbl; -- Security barrier views not updatable CREATE VIEW ro_view19 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable CREATE SEQUENCE seq; CREATE VIEW ro_view20 AS SELECT * FROM seq; -- View based on a sequence SELECT table_name, is_insertable_into FROM information_schema.tables ! WHERE table_name LIKE 'ro_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- --- 18,36 ---- CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable ! CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an updatable view ! CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part of an updatable view ! CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an updatable view CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable CREATE VIEW ro_view18 WITH (security_barrier = true) AS SELECT * FROM base_tbl; -- Security barrier views not updatable CREATE VIEW ro_view19 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable CREATE SEQUENCE seq; CREATE VIEW ro_view20 AS SELECT * FROM seq; -- View based on a sequence + CREATE VIEW ro_view21 AS SELECT a, b, generate_series(1, a) g FROM base_tbl; -- SRF in targetlist not supported SELECT table_name, is_insertable_into FROM information_schema.tables ! WHERE table_name LIKE E'r_\_view%' ORDER BY table_name; table_name | is_insertable_into ------------+-------------------- *************** SELECT table_name, is_insertable_into *** 37,50 **** ro_view11 | NO ro_view12 | NO ro_view13 | NO - ro_view14 | NO - ro_view15 | NO - ro_view16 | NO ro_view17 | NO ro_view18 | NO ro_view19 | NO ro_view2 | NO ro_view20 | NO ro_view3 | NO ro_view4 | NO ro_view5 | NO --- 39,50 ---- ro_view11 | NO ro_view12 | NO ro_view13 | NO ro_view17 | NO ro_view18 | NO ro_view19 | NO ro_view2 | NO ro_view20 | NO + ro_view21 | NO ro_view3 | NO ro_view4 | NO ro_view5 | NO *************** SELECT table_name, is_insertable_into *** 52,62 **** ro_view7 | NO ro_view8 | NO ro_view9 | NO ! (20 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views ! WHERE table_name LIKE 'ro_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- --- 52,65 ---- ro_view7 | NO ro_view8 | NO ro_view9 | NO ! rw_view14 | YES ! rw_view15 | YES ! rw_view16 | YES ! (21 rows) SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views ! WHERE table_name LIKE E'r_\_view%' ORDER BY table_name; table_name | is_updatable | is_insertable_into ------------+--------------+-------------------- *************** SELECT table_name, is_updatable, is_inse *** 65,78 **** ro_view11 | NO | NO ro_view12 | NO | NO ro_view13 | NO | NO - ro_view14 | NO | NO - ro_view15 | NO | NO - ro_view16 | NO | NO ro_view17 | NO | NO ro_view18 | NO | NO ro_view19 | NO | NO ro_view2 | NO | NO ro_view20 | NO | NO ro_view3 | NO | NO ro_view4 | NO | NO ro_view5 | NO | NO --- 68,79 ---- ro_view11 | NO | NO ro_view12 | NO | NO ro_view13 | NO | NO ro_view17 | NO | NO ro_view18 | NO | NO ro_view19 | NO | NO ro_view2 | NO | NO ro_view20 | NO | NO + ro_view21 | NO | NO ro_view3 | NO | NO ro_view4 | NO | NO ro_view5 | NO | NO *************** SELECT table_name, is_updatable, is_inse *** 80,90 **** ro_view7 | NO | NO ro_view8 | NO | NO ro_view9 | NO | NO ! (20 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns ! WHERE table_name LIKE 'ro_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+---------------+-------------- --- 81,94 ---- ro_view7 | NO | NO ro_view8 | NO | NO ro_view9 | NO | NO ! rw_view14 | YES | YES ! rw_view15 | YES | YES ! rw_view16 | YES | YES ! (21 rows) SELECT table_name, column_name, is_updatable FROM information_schema.columns ! WHERE table_name LIKE E'r_\_view%' ORDER BY table_name, ordinal_position; table_name | column_name | is_updatable ------------+---------------+-------------- *************** SELECT table_name, column_name, is_updat *** 96,107 **** ro_view12 | a | NO ro_view13 | a | NO ro_view13 | b | NO - ro_view14 | ctid | NO - ro_view15 | a | NO - ro_view15 | upper | NO - ro_view16 | a | NO - ro_view16 | b | NO - ro_view16 | aa | NO ro_view17 | a | NO ro_view17 | b | NO ro_view18 | a | NO --- 100,105 ---- *************** SELECT table_name, column_name, is_updat *** 119,124 **** --- 117,125 ---- ro_view20 | log_cnt | NO ro_view20 | is_cycled | NO ro_view20 | is_called | NO + ro_view21 | a | NO + ro_view21 | b | NO + ro_view21 | g | NO ro_view3 | ?column? | NO ro_view4 | count | NO ro_view5 | a | NO *************** SELECT table_name, column_name, is_updat *** 131,138 **** ro_view8 | b | NO ro_view9 | a | NO ro_view9 | b | NO ! (43 rows) DELETE FROM ro_view1; ERROR: cannot delete from view "ro_view1" DETAIL: Views containing DISTINCT are not automatically updatable. --- 132,148 ---- ro_view8 | b | NO ro_view9 | a | NO ro_view9 | b | NO ! rw_view14 | ctid | NO ! rw_view14 | a | YES ! rw_view14 | b | YES ! rw_view15 | a | YES ! rw_view15 | upper | NO ! rw_view16 | a | YES ! rw_view16 | b | YES ! rw_view16 | aa | YES ! (48 rows) + -- Read-only views DELETE FROM ro_view1; ERROR: cannot delete from view "ro_view1" DETAIL: Views containing DISTINCT are not automatically updatable. *************** DETAIL: Views containing HAVING are not *** 147,157 **** HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. DELETE FROM ro_view4; ERROR: cannot delete from view "ro_view4" ! DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. DELETE FROM ro_view5; ERROR: cannot delete from view "ro_view5" ! DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable. HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. DELETE FROM ro_view6; ERROR: cannot delete from view "ro_view6" --- 157,167 ---- HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. DELETE FROM ro_view4; ERROR: cannot delete from view "ro_view4" ! DETAIL: Views that return aggregate functions are not automatically updatable HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. DELETE FROM ro_view5; ERROR: cannot delete from view "ro_view5" ! DETAIL: Views that return window functions are not automatically updatable HINT: To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule. DELETE FROM ro_view6; ERROR: cannot delete from view "ro_view6" *************** INSERT INTO ro_view13 VALUES (3, 'Row 3' *** 185,202 **** ERROR: cannot insert into view "ro_view13" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. ! INSERT INTO ro_view14 VALUES (null); ! ERROR: cannot insert into view "ro_view14" ! DETAIL: Views that return system columns are not automatically updatable. ! HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. ! INSERT INTO ro_view15 VALUES (3, 'ROW 3'); ! ERROR: cannot insert into view "ro_view15" ! DETAIL: Views that return columns that are not columns of their base relation are not automatically updatable. ! HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. ! INSERT INTO ro_view16 VALUES (3, 'Row 3', 3); ! ERROR: cannot insert into view "ro_view16" ! DETAIL: Views that return the same column more than once are not automatically updatable. ! HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. INSERT INTO ro_view17 VALUES (3, 'ROW 3'); ERROR: cannot insert into view "ro_view1" DETAIL: Views containing DISTINCT are not automatically updatable. --- 195,269 ---- ERROR: cannot insert into view "ro_view13" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable inserting into the view, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule. ! -- Partially updatable view ! INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail ! ERROR: cannot insert into column "ctid" of view "rw_view14" ! DETAIL: View columns that refer to system columns are not updatable. ! INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK ! UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail ! ERROR: cannot update column "ctid" of view "rw_view14" ! DETAIL: View columns that refer to system columns are not updatable. ! UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK ! SELECT * FROM base_tbl; ! a | b ! ----+-------- ! -2 | Row -2 ! -1 | Row -1 ! 0 | Row 0 ! 1 | Row 1 ! 2 | Row 2 ! 3 | ROW 3 ! (6 rows) ! ! DELETE FROM rw_view14 WHERE a=3; -- should be OK ! -- Partially updatable view ! INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail ! ERROR: cannot insert into column "upper" of view "rw_view15" ! DETAIL: View columns that are not columns of their base relation are not updatable. ! INSERT INTO rw_view15 (a) VALUES (3); -- should be OK ! ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; ! INSERT INTO rw_view15 (a) VALUES (4); -- should fail ! ERROR: cannot insert into column "upper" of view "rw_view15" ! DETAIL: View columns that are not columns of their base relation are not updatable. ! UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail ! ERROR: cannot update column "upper" of view "rw_view15" ! DETAIL: View columns that are not columns of their base relation are not updatable. ! UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail ! ERROR: cannot update column "upper" of view "rw_view15" ! DETAIL: View columns that are not columns of their base relation are not updatable. ! UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK ! SELECT * FROM base_tbl; ! a | b ! ----+------------- ! -2 | Row -2 ! -1 | Row -1 ! 0 | Row 0 ! 1 | Row 1 ! 2 | Row 2 ! 4 | Unspecified ! (6 rows) ! ! DELETE FROM rw_view15 WHERE a=4; -- should be OK ! -- Partially updatable view ! INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail ! ERROR: multiple assignments to same column "a" ! INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK ! UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail ! ERROR: multiple assignments to same column "a" ! UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK ! SELECT * FROM base_tbl; ! a | b ! ----+-------- ! -2 | Row -2 ! -1 | Row -1 ! 0 | Row 0 ! 1 | Row 1 ! 2 | Row 2 ! -3 | Row 3 ! (6 rows) ! ! DELETE FROM rw_view16 WHERE a=-3; -- should be OK ! -- Read-only views INSERT INTO ro_view17 VALUES (3, 'ROW 3'); ERROR: cannot insert into view "ro_view1" DETAIL: Views containing DISTINCT are not automatically updatable. *************** UPDATE ro_view20 SET max_value=1000; *** 213,220 **** ERROR: cannot update view "ro_view20" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. DROP TABLE base_tbl CASCADE; ! NOTICE: drop cascades to 16 other objects DETAIL: drop cascades to view ro_view1 drop cascades to view ro_view17 drop cascades to view ro_view2 --- 280,291 ---- ERROR: cannot update view "ro_view20" DETAIL: Views that do not select from a single table or view are not automatically updatable. HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. + UPDATE ro_view21 SET b=upper(b); + ERROR: cannot update view "ro_view21" + DETAIL: Views that return set-returning functions are not automatically updatable. + HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule. DROP TABLE base_tbl CASCADE; ! NOTICE: drop cascades to 17 other objects DETAIL: drop cascades to view ro_view1 drop cascades to view ro_view17 drop cascades to view ro_view2 *************** drop cascades to view ro_view8 *** 226,236 **** drop cascades to view ro_view9 drop cascades to view ro_view11 drop cascades to view ro_view13 ! drop cascades to view ro_view15 ! drop cascades to view ro_view16 drop cascades to view ro_view18 drop cascades to view ro_view4 ! drop cascades to view ro_view14 DROP VIEW ro_view10, ro_view12, ro_view19; DROP SEQUENCE seq CASCADE; NOTICE: drop cascades to view ro_view20 --- 297,308 ---- drop cascades to view ro_view9 drop cascades to view ro_view11 drop cascades to view ro_view13 ! drop cascades to view rw_view15 ! drop cascades to view rw_view16 drop cascades to view ro_view18 + drop cascades to view ro_view21 drop cascades to view ro_view4 ! drop cascades to view rw_view14 DROP VIEW ro_view10, ro_view12, ro_view19; DROP SEQUENCE seq CASCADE; NOTICE: drop cascades to view ro_view20 *************** SELECT * FROM rw_view1; *** 1063,1068 **** --- 1135,1282 ---- DROP TABLE base_tbl CASCADE; NOTICE: drop cascades to view rw_view1 + -- views with updatable and non-updatable columns + CREATE TABLE base_tbl(a float); + INSERT INTO base_tbl SELECT i/10.0 FROM generate_series(1,10) g(i); + CREATE VIEW rw_view1 AS + SELECT ctid, sin(a) s, a, cos(a) c + FROM base_tbl + WHERE a != 0 + ORDER BY abs(a); + INSERT INTO rw_view1 VALUES (null, null, 1.1, null); -- should fail + ERROR: cannot insert into column "ctid" of view "rw_view1" + DETAIL: View columns that refer to system columns are not updatable. + INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail + ERROR: cannot insert into column "s" of view "rw_view1" + DETAIL: View columns that are not columns of their base relation are not updatable. + INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK + a | s | c + -----+-------------------+------------------- + 1.1 | 0.891207360061435 | 0.453596121425577 + (1 row) + + UPDATE rw_view1 SET s = s WHERE a = 1.1; -- should fail + ERROR: cannot update column "s" of view "rw_view1" + DETAIL: View columns that are not columns of their base relation are not updatable. + UPDATE rw_view1 SET a = 1.05 WHERE a = 1.1 RETURNING s; -- OK + s + ------------------- + 0.867423225594017 + (1 row) + + DELETE FROM rw_view1 WHERE a = 1.05; -- OK + CREATE VIEW rw_view2 AS + SELECT s, c, s/c t, a base_a, ctid + FROM rw_view1; + INSERT INTO rw_view2 VALUES (null, null, null, 1.1, null); -- should fail + ERROR: cannot insert into column "t" of view "rw_view2" + DETAIL: View columns that are not columns of their base relation are not updatable. + INSERT INTO rw_view2(s, c, base_a) VALUES (null, null, 1.1); -- should fail + ERROR: cannot insert into column "s" of view "rw_view1" + DETAIL: View columns that are not columns of their base relation are not updatable. + INSERT INTO rw_view2(base_a) VALUES (1.1) RETURNING t; -- OK + t + ------------------ + 1.96475965724865 + (1 row) + + UPDATE rw_view2 SET s = s WHERE base_a = 1.1; -- should fail + ERROR: cannot update column "s" of view "rw_view1" + DETAIL: View columns that are not columns of their base relation are not updatable. + UPDATE rw_view2 SET t = t WHERE base_a = 1.1; -- should fail + ERROR: cannot update column "t" of view "rw_view2" + DETAIL: View columns that are not columns of their base relation are not updatable. + UPDATE rw_view2 SET base_a = 1.05 WHERE base_a = 1.1; -- OK + DELETE FROM rw_view2 WHERE base_a = 1.05 RETURNING base_a, s, c, t; -- OK + base_a | s | c | t + --------+-------------------+-------------------+------------------ + 1.05 | 0.867423225594017 | 0.497571047891727 | 1.74331530998317 + (1 row) + + CREATE VIEW rw_view3 AS + SELECT s, c, s/c t, ctid + FROM rw_view1; + INSERT INTO rw_view3 VALUES (null, null, null, null); -- should fail + ERROR: cannot insert into column "t" of view "rw_view3" + DETAIL: View columns that are not columns of their base relation are not updatable. + INSERT INTO rw_view3(s) VALUES (null); -- should fail + ERROR: cannot insert into column "s" of view "rw_view1" + DETAIL: View columns that are not columns of their base relation are not updatable. + UPDATE rw_view3 SET s = s; -- should fail + ERROR: cannot update column "s" of view "rw_view1" + DETAIL: View columns that are not columns of their base relation are not updatable. + DELETE FROM rw_view3 WHERE s = sin(0.1); -- should be OK + SELECT * FROM base_tbl ORDER BY a; + a + ----- + 0.2 + 0.3 + 0.4 + 0.5 + 0.6 + 0.7 + 0.8 + 0.9 + 1 + (9 rows) + + SELECT table_name, is_insertable_into + FROM information_schema.tables + WHERE table_name LIKE E'r_\_view%' + ORDER BY table_name; + table_name | is_insertable_into + ------------+-------------------- + rw_view1 | YES + rw_view2 | YES + rw_view3 | NO + (3 rows) + + SELECT table_name, is_updatable, is_insertable_into + FROM information_schema.views + WHERE table_name LIKE E'r_\_view%' + ORDER BY table_name; + table_name | is_updatable | is_insertable_into + ------------+--------------+-------------------- + rw_view1 | YES | YES + rw_view2 | YES | YES + rw_view3 | NO | NO + (3 rows) + + SELECT table_name, column_name, is_updatable + FROM information_schema.columns + WHERE table_name LIKE E'r_\_view%' + ORDER BY table_name, ordinal_position; + table_name | column_name | is_updatable + ------------+-------------+-------------- + rw_view1 | ctid | NO + rw_view1 | s | NO + rw_view1 | a | YES + rw_view1 | c | NO + rw_view2 | s | NO + rw_view2 | c | NO + rw_view2 | t | NO + rw_view2 | base_a | YES + rw_view2 | ctid | NO + rw_view3 | s | NO + rw_view3 | c | NO + rw_view3 | t | NO + rw_view3 | ctid | NO + (13 rows) + + SELECT events & 4 != 0 AS upd, + events & 8 != 0 AS ins, + events & 16 != 0 AS del + FROM pg_catalog.pg_relation_is_updatable('rw_view3'::regclass, false) t(events); + upd | ins | del + -----+-----+----- + f | f | t + (1 row) + + DROP TABLE base_tbl CASCADE; + NOTICE: drop cascades to 3 other objects + DETAIL: drop cascades to view rw_view1 + drop cascades to view rw_view2 + drop cascades to view rw_view3 -- inheritance tests CREATE TABLE base_tbl_parent (a int); CREATE TABLE base_tbl_child (CHECK (a > 0)) INHERITS (base_tbl_parent); diff --git a/src/test/regress/sql/updatable_views.sql b/src/test/regress/sql/updatable_views.sql new file mode 100644 index 0481800..b7d9ba7 *** a/src/test/regress/sql/updatable_views.sql --- b/src/test/regress/sql/updatable_views.sql *************** *** 2,8 **** -- UPDATABLE VIEWS -- ! -- check that non-updatable views are rejected with useful error messages CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); --- 2,9 ---- -- UPDATABLE VIEWS -- ! -- check that non-updatable views and columns are rejected with useful error ! -- messages CREATE TABLE base_tbl (a int PRIMARY KEY, b text DEFAULT 'Unspecified'); INSERT INTO base_tbl SELECT i, 'Row ' || i FROM generate_series(-2, 2) g(i); *************** CREATE VIEW ro_view10 AS SELECT 1 AS a; *** 20,50 **** CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable ! CREATE VIEW ro_view14 AS SELECT ctid FROM base_tbl; -- System columns not supported ! CREATE VIEW ro_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function in targetlist ! CREATE VIEW ro_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable CREATE VIEW ro_view18 WITH (security_barrier = true) AS SELECT * FROM base_tbl; -- Security barrier views not updatable CREATE VIEW ro_view19 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable CREATE SEQUENCE seq; CREATE VIEW ro_view20 AS SELECT * FROM seq; -- View based on a sequence SELECT table_name, is_insertable_into FROM information_schema.tables ! WHERE table_name LIKE 'ro_view%' ORDER BY table_name; SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views ! WHERE table_name LIKE 'ro_view%' ORDER BY table_name; SELECT table_name, column_name, is_updatable FROM information_schema.columns ! WHERE table_name LIKE 'ro_view%' ORDER BY table_name, ordinal_position; DELETE FROM ro_view1; DELETE FROM ro_view2; DELETE FROM ro_view3; --- 21,53 ---- CREATE VIEW ro_view11 AS SELECT b1.a, b2.b FROM base_tbl b1, base_tbl b2; -- Multiple base relations CREATE VIEW ro_view12 AS SELECT * FROM generate_series(1, 10) AS g(a); -- SRF in rangetable CREATE VIEW ro_view13 AS SELECT a, b FROM (SELECT * FROM base_tbl) AS t; -- Subselect in rangetable ! CREATE VIEW rw_view14 AS SELECT ctid, a, b FROM base_tbl; -- System columns may be part of an updatable view ! CREATE VIEW rw_view15 AS SELECT a, upper(b) FROM base_tbl; -- Expression/function may be part of an updatable view ! CREATE VIEW rw_view16 AS SELECT a, b, a AS aa FROM base_tbl; -- Repeated column may be part of an updatable view CREATE VIEW ro_view17 AS SELECT * FROM ro_view1; -- Base relation not updatable CREATE VIEW ro_view18 WITH (security_barrier = true) AS SELECT * FROM base_tbl; -- Security barrier views not updatable CREATE VIEW ro_view19 AS SELECT * FROM (VALUES(1)) AS tmp(a); -- VALUES in rangetable CREATE SEQUENCE seq; CREATE VIEW ro_view20 AS SELECT * FROM seq; -- View based on a sequence + CREATE VIEW ro_view21 AS SELECT a, b, generate_series(1, a) g FROM base_tbl; -- SRF in targetlist not supported SELECT table_name, is_insertable_into FROM information_schema.tables ! WHERE table_name LIKE E'r_\_view%' ORDER BY table_name; SELECT table_name, is_updatable, is_insertable_into FROM information_schema.views ! WHERE table_name LIKE E'r_\_view%' ORDER BY table_name; SELECT table_name, column_name, is_updatable FROM information_schema.columns ! WHERE table_name LIKE E'r_\_view%' ORDER BY table_name, ordinal_position; + -- Read-only views DELETE FROM ro_view1; DELETE FROM ro_view2; DELETE FROM ro_view3; *************** UPDATE ro_view10 SET a=a+1; *** 58,70 **** UPDATE ro_view11 SET a=a+1; UPDATE ro_view12 SET a=a+1; INSERT INTO ro_view13 VALUES (3, 'Row 3'); ! INSERT INTO ro_view14 VALUES (null); ! INSERT INTO ro_view15 VALUES (3, 'ROW 3'); ! INSERT INTO ro_view16 VALUES (3, 'Row 3', 3); INSERT INTO ro_view17 VALUES (3, 'ROW 3'); INSERT INTO ro_view18 VALUES (3, 'ROW 3'); DELETE FROM ro_view19; UPDATE ro_view20 SET max_value=1000; DROP TABLE base_tbl CASCADE; DROP VIEW ro_view10, ro_view12, ro_view19; --- 61,96 ---- UPDATE ro_view11 SET a=a+1; UPDATE ro_view12 SET a=a+1; INSERT INTO ro_view13 VALUES (3, 'Row 3'); ! -- Partially updatable view ! INSERT INTO rw_view14 VALUES (null, 3, 'Row 3'); -- should fail ! INSERT INTO rw_view14 (a, b) VALUES (3, 'Row 3'); -- should be OK ! UPDATE rw_view14 SET ctid=null WHERE a=3; -- should fail ! UPDATE rw_view14 SET b='ROW 3' WHERE a=3; -- should be OK ! SELECT * FROM base_tbl; ! DELETE FROM rw_view14 WHERE a=3; -- should be OK ! -- Partially updatable view ! INSERT INTO rw_view15 VALUES (3, 'ROW 3'); -- should fail ! INSERT INTO rw_view15 (a) VALUES (3); -- should be OK ! ALTER VIEW rw_view15 ALTER COLUMN upper SET DEFAULT 'NOT SET'; ! INSERT INTO rw_view15 (a) VALUES (4); -- should fail ! UPDATE rw_view15 SET upper='ROW 3' WHERE a=3; -- should fail ! UPDATE rw_view15 SET upper=DEFAULT WHERE a=3; -- should fail ! UPDATE rw_view15 SET a=4 WHERE a=3; -- should be OK ! SELECT * FROM base_tbl; ! DELETE FROM rw_view15 WHERE a=4; -- should be OK ! -- Partially updatable view ! INSERT INTO rw_view16 VALUES (3, 'Row 3', 3); -- should fail ! INSERT INTO rw_view16 (a, b) VALUES (3, 'Row 3'); -- should be OK ! UPDATE rw_view16 SET a=3, aa=-3 WHERE a=3; -- should fail ! UPDATE rw_view16 SET aa=-3 WHERE a=3; -- should be OK ! SELECT * FROM base_tbl; ! DELETE FROM rw_view16 WHERE a=-3; -- should be OK ! -- Read-only views INSERT INTO ro_view17 VALUES (3, 'ROW 3'); INSERT INTO ro_view18 VALUES (3, 'ROW 3'); DELETE FROM ro_view19; UPDATE ro_view20 SET max_value=1000; + UPDATE ro_view21 SET b=upper(b); DROP TABLE base_tbl CASCADE; DROP VIEW ro_view10, ro_view12, ro_view19; *************** SELECT * FROM rw_view1; *** 510,515 **** --- 536,603 ---- DROP TABLE base_tbl CASCADE; + -- views with updatable and non-updatable columns + + CREATE TABLE base_tbl(a float); + INSERT INTO base_tbl SELECT i/10.0 FROM generate_series(1,10) g(i); + + CREATE VIEW rw_view1 AS + SELECT ctid, sin(a) s, a, cos(a) c + FROM base_tbl + WHERE a != 0 + ORDER BY abs(a); + + INSERT INTO rw_view1 VALUES (null, null, 1.1, null); -- should fail + INSERT INTO rw_view1 (s, c, a) VALUES (null, null, 1.1); -- should fail + INSERT INTO rw_view1 (a) VALUES (1.1) RETURNING a, s, c; -- OK + UPDATE rw_view1 SET s = s WHERE a = 1.1; -- should fail + UPDATE rw_view1 SET a = 1.05 WHERE a = 1.1 RETURNING s; -- OK + DELETE FROM rw_view1 WHERE a = 1.05; -- OK + + CREATE VIEW rw_view2 AS + SELECT s, c, s/c t, a base_a, ctid + FROM rw_view1; + + INSERT INTO rw_view2 VALUES (null, null, null, 1.1, null); -- should fail + INSERT INTO rw_view2(s, c, base_a) VALUES (null, null, 1.1); -- should fail + INSERT INTO rw_view2(base_a) VALUES (1.1) RETURNING t; -- OK + UPDATE rw_view2 SET s = s WHERE base_a = 1.1; -- should fail + UPDATE rw_view2 SET t = t WHERE base_a = 1.1; -- should fail + UPDATE rw_view2 SET base_a = 1.05 WHERE base_a = 1.1; -- OK + DELETE FROM rw_view2 WHERE base_a = 1.05 RETURNING base_a, s, c, t; -- OK + + CREATE VIEW rw_view3 AS + SELECT s, c, s/c t, ctid + FROM rw_view1; + + INSERT INTO rw_view3 VALUES (null, null, null, null); -- should fail + INSERT INTO rw_view3(s) VALUES (null); -- should fail + UPDATE rw_view3 SET s = s; -- should fail + DELETE FROM rw_view3 WHERE s = sin(0.1); -- should be OK + SELECT * FROM base_tbl ORDER BY a; + + SELECT table_name, is_insertable_into + FROM information_schema.tables + WHERE table_name LIKE E'r_\_view%' + ORDER BY table_name; + + SELECT table_name, is_updatable, is_insertable_into + FROM information_schema.views + WHERE table_name LIKE E'r_\_view%' + ORDER BY table_name; + + SELECT table_name, column_name, is_updatable + FROM information_schema.columns + WHERE table_name LIKE E'r_\_view%' + ORDER BY table_name, ordinal_position; + + SELECT events & 4 != 0 AS upd, + events & 8 != 0 AS ins, + events & 16 != 0 AS del + FROM pg_catalog.pg_relation_is_updatable('rw_view3'::regclass, false) t(events); + + DROP TABLE base_tbl CASCADE; + -- inheritance tests CREATE TABLE base_tbl_parent (a int);