*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 4145,4150 ****
--- 4145,4157 ----
+ is_auto
+ bool
+
+ True if the rule was automatically generated
+
+
+
ev_qual
text
*** 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
***************
*** 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
! .
--- 115,169 ----
Notes
! Currently, views are updatable in accordance with the
! specifications of the SQL-92 standard. That means a view is
! updatable if it contains:
!
!
!
!
! exactly one base table (or another updatable view)
!
!
!
!
! no aggregate function calls
!
!
!
!
! no DISTINCT, GROUP BY, or
! HAVING clauses
!
!
!
!
!
! no UNION, INTERSECT, or
! EXCEPTclauses
!
!
!
!
!
!
! Views are insertable only if they are updatable and the defining
! SELECT statement contains all columns in the
! select list that are both not nullable and don't have a default
! value. (The INSERT operation will insert a
! null value into columns not in the select list.)
!
!
!
! The updatable views implementation is based on the rule system.
! Because of this, you can also make more complex views updatable or
! insertable 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 the automatically generated rules with your own
! rules. For more information, refer
! to .
*** 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"
***************
*** 308,320 **** DefineViewRules(Oid viewOid, Query *viewParse, bool replace)
viewOid,
NULL,
CMD_SELECT,
! true,
replace,
list_make1(viewParse));
/*
! * Someday: automatic ON INSERT, etc
*/
}
/*---------------------------------------------------------------
--- 309,334 ----
viewOid,
NULL,
CMD_SELECT,
! true, /* is_instead */
! true, /* is_auto */
replace,
list_make1(viewParse));
/*
! * Delete all implicit rules on replace.
*/
+ if (replace)
+ {
+ Relation rel = heap_open(viewOid, AccessExclusiveLock);
+ RemoveAutomaticRulesOnEvent(rel, CMD_INSERT, InvalidOid);
+ RemoveAutomaticRulesOnEvent(rel, CMD_DELETE, InvalidOid);
+ RemoveAutomaticRulesOnEvent(rel, CMD_UPDATE, InvalidOid);
+ heap_close(rel, NoLock);
+ }
+
+ CommandCounterIncrement();
+
+ CreateViewUpdateRules(viewParse);
}
/*---------------------------------------------------------------
*** 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,
+ bool is_auto,
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++] = BoolGetDatum(is_auto); /* is_auto */
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,138 ----
if (HeapTupleIsValid(oldtup))
{
+ bool old_rule_is_auto = is_auto;
+
if (!replace)
! {
! /*
! * If REPLACE was not used we still have to check if the
! * rule is automatic: Then we have to replace it anyways.
! */
! old_rule_is_auto = ((Form_pg_rewrite) GETSTRUCT(oldtup))->is_auto;
!
! if (!old_rule_is_auto)
! 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 automatic rule with a
! * user-defined rule, we need to drop all automatic rules on
! * the same action, except the new user-defined rule is a DO ALSO
! * rule. We have be careful not to drop the current rule we
! * want to replace.
! */
! if (old_rule_is_auto && !is_auto && evinstead)
! RemoveAutomaticRulesOnEvent(event_relation, evtype,
! HeapTupleGetOid(oldtup));
/*
* When replacing, we don't need to replace every attribute
***************
*** 115,120 **** InsertRule(char *rulname,
--- 141,147 ----
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_is_auto - 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);
}
--- 157,174 ----
}
else
{
! /*
! * If defining a non-automatic rule, drop all automatic rules
! * on the same event (except DO ALSO). This is the case where someone
! * manually defines an update rule that has a different name from the
! * one the system chose for its automatically generated
! * updated rules and additional actions.
! */
! if (!is_auto && evinstead)
! RemoveAutomaticRulesOnEvent(event_relation, evtype,
! InvalidOid);
+ tup = heap_form_tuple(pg_rewrite_desc->rd_att, values, nulls);
rewriteObjectId = simple_heap_insert(pg_rewrite_desc, tup);
}
***************
*** 205,210 **** DefineRule(RuleStmt *stmt, const char *queryString)
--- 242,248 ----
whereClause,
stmt->event,
stmt->instead,
+ false, /* not is_auto */
stmt->replace,
actions);
}
***************
*** 223,228 **** DefineQueryRewrite(char *rulename,
--- 261,267 ----
Node *event_qual,
CmdType event_type,
bool is_instead,
+ bool is_auto,
bool replace,
List *action)
{
***************
*** 458,466 **** DefineQueryRewrite(char *rulename,
{
ruleId = InsertRule(rulename,
event_type,
! event_relid,
event_attno,
is_instead,
event_qual,
action,
replace);
--- 497,506 ----
{
ruleId = InsertRule(rulename,
event_type,
! event_relation,
event_attno,
is_instead,
+ is_auto,
event_qual,
action,
replace);
*** a/src/backend/rewrite/rewriteHandler.c
--- b/src/backend/rewrite/rewriteHandler.c
***************
*** 1896,1914 **** QueryRewrite(Query *parsetree)
case CMD_INSERT:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot insert into a view"),
errhint("You need an unconditional ON INSERT DO INSTEAD rule.")));
break;
case CMD_UPDATE:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot update a view"),
errhint("You need an unconditional ON UPDATE DO INSTEAD rule.")));
break;
case CMD_DELETE:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("cannot delete from a view"),
errhint("You need an unconditional ON DELETE DO INSTEAD rule.")));
break;
default:
--- 1896,1914 ----
case CMD_INSERT:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("view is not insertable into"),
errhint("You need an unconditional ON INSERT DO INSTEAD rule.")));
break;
case CMD_UPDATE:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("view is not updatable"),
errhint("You need an unconditional ON UPDATE DO INSTEAD rule.")));
break;
case CMD_DELETE:
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("view is not updatable"),
errhint("You need an unconditional ON DELETE DO INSTEAD rule.")));
break;
default:
*** a/src/backend/rewrite/rewriteRemove.c
--- b/src/backend/rewrite/rewriteRemove.c
***************
*** 90,95 **** RemoveRewriteRule(Oid owningRel, const char *ruleName, DropBehavior behavior,
--- 90,139 ----
performDeletion(&object, behavior);
}
+ /*
+ * RemoveAutomaticRulesOnEvent
+ *
+ * This will delete automatic rules, if any exist, on the event in the
+ * relation. You can use exceptOid to exclude a specific automatic
+ * rule from deletion; use InvalidOid if you want to delete all
+ * automatic rules.
+ */
+ void
+ RemoveAutomaticRulesOnEvent(Relation rel, CmdType event_type, Oid exceptOid)
+ {
+ RuleLock *rulelocks = rel->rd_rules;
+ int nlocks;
+ int i;
+
+ /* Select rules are not handled here. */
+ if (event_type == CMD_SELECT)
+ return;
+
+ /* If there are no rules on the relation, 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 automatic.
+ */
+ for (i = 0; i < nlocks; i++)
+ {
+ RewriteRule *oneLock = rulelocks->rules[i];
+
+ if (oneLock->event == event_type
+ && oneLock->is_auto
+ && oneLock->ruleId != exceptOid)
+ {
+ RemoveRewriteRuleById(oneLock->ruleId);
+ elog(DEBUG1, "removing automatic rule with OID %u\n",
+ oneLock->ruleId);
+ deleteDependencyRecordsFor(RewriteRelationId, oneLock->ruleId);
+ }
+ }
+ }
/*
* Guts of rule deletion.
*** /dev/null
--- b/src/backend/rewrite/viewUpdate.c
***************
*** 0 ****
--- 1,1527 ----
+ /*-------------------------------------------------------------------------
+ *
+ * viewUpdate.c
+ * routines for translating a view definition into
+ * INSERT/UPDATE/DELETE rules (i.e. updatable views).
+ *
+ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * ORIGINAL AUTHORS
+ * Bernd Helmle, Jaime Casanova
+ *
+ * IDENTIFICATION
+ * $PostgreSQL$
+ *
+ *-------------------------------------------------------------------------
+ */
+ #include "postgres.h"
+
+ #include "access/heapam.h"
+ #include "access/xact.h"
+ #include "catalog/pg_operator.h"
+ #include "catalog/pg_rewrite.h"
+ #include "nodes/nodeFuncs.h"
+ #include "parser/parse_oper.h"
+ #include "parser/parsetree.h"
+ #include "rewrite/rewriteDefine.h"
+ #include "rewrite/viewUpdate.h"
+ #include "utils/lsyscache.h"
+ #include "utils/memutils.h"
+ #include "utils/syscache.h"
+ #include "utils/rel.h"
+
+ typedef TargetEntry** ViewDefColumnList;
+
+ typedef struct ViewBaseRelation
+ {
+ List *defs; /* List of all base relations (root starts
+ * with only one relation because SQL-92
+ * allows only this) */
+ Oid parentRelation; /* Oid of parent relation, 0 indicates root */
+ } ViewBaseRelation;
+
+ typedef struct ViewBaseRelationItem
+ {
+ Relation rel; /* the Relation itself */
+ Query *rule; /* _RETURN rule of a view relation */
+ TargetEntry **tentries; /* saves order of column target list */
+ } ViewBaseRelationItem;
+
+ typedef struct ViewExprContext
+ {
+ Index newRTE;
+ Index oldRTE;
+ Index baseRTE;
+ Index subQueryLevel;
+ ViewDefColumnList tentries;
+ } ViewExprContext;
+
+ static const char *get_auto_rule_name(CmdType type);
+ static Query *get_return_rule(Relation rel);
+ static void read_rearranged_cols(ViewBaseRelation *tree);
+ static bool checkTree(const Query *query, ViewBaseRelation *tree);
+ static Oid get_reloid_from_select(const Query *select,
+ int *rti, RangeTblEntry **rel_entry);
+ static void create_update_rule(const Query *update,
+ const Relation rel,
+ TargetEntry **tentries,
+ CmdType ruletype);
+ static void get_base_relations(ViewBaseRelation *tree, List **baserelations);
+ static void get_base_base_relations(const Query *view, Oid baserel, List **list);
+ static void copyReversedTargetEntryPtr(List *targetList,
+ ViewDefColumnList targets);
+ static bool check_reltree(ViewBaseRelation *node);
+ static void form_query(const Query *select, Query *query,
+ ViewDefColumnList tentries, bool copyTargetList);
+ static RangeTblEntry *get_relation_RTE(const Query *select,
+ unsigned int *offset);
+ static Index get_rtindex_for_rel(List *rte_list,
+ const char *relname);
+ static bool replace_tlist_varno_walker(Node *node,
+ ViewExprContext *ctxt);
+ static OpExpr *create_opexpr(Var *var_left, Var *var_right);
+ static void form_where_for_updrule(const Query *select, FromExpr **from,
+ const Relation rel, Oid baserel, Index baserti,
+ Index oldrti);
+ static void build_update_target_list(const Query *update, const Query *select,
+ Oid baserel, const Relation rel);
+ static bool view_is_insertable(const Query *select, const Relation rel);
+
+ /*------------------------------------------------------------------------------
+ * Private functions
+ * -----------------------------------------------------------------------------
+ */
+
+ static const char *
+ get_auto_rule_name(CmdType type)
+ {
+ if (type == CMD_INSERT)
+ return "_INSERT";
+ if (type == CMD_UPDATE)
+ return "_UPDATE";
+ if (type == CMD_DELETE)
+ return "_DELETE";
+ return NULL;
+ }
+
+ /*
+ * Returns the range table index for the specified relname.
+ *
+ * XXX This seems pretty grotty ... can't we do this in some other way?
+ */
+ static Index
+ get_rtindex_for_rel(List *rte_list, const char *relname)
+ {
+ ListCell *cell;
+ int index = 0;
+
+ AssertArg(relname);
+
+ foreach(cell, rte_list)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(cell);
+
+ index++;
+
+ if (rte && strncmp(rte->eref->aliasname, relname, NAMEDATALEN) == 0)
+ break;
+ }
+
+ Assert(index > 0);
+
+ return (Index) index;
+ }
+
+ /*
+ * Returns the RangeTblEntry starting at the specified offset. The
+ * function can be used to iterate over the rtable list of the
+ * specified select query tree. Returns NULL if nothing is found.
+ *
+ * NOTE: The function only returns those RangeTblEntry that do not
+ * match a *NEW* or *OLD* RangeTblEntry.
+ *
+ * The offset is incremented as a side effect.
+ */
+ static RangeTblEntry *
+ get_relation_RTE(const Query *select, unsigned int *offset)
+ {
+ AssertArg(offset);
+ AssertArg(select);
+
+ while (*offset <= list_length(select->rtable))
+ {
+ RangeTblEntry *rte = rt_fetch(*offset, select->rtable);
+ (*offset)++;
+
+ /* skip non-table RTEs */
+ if (rte->rtekind != RTE_RELATION)
+ continue;
+
+ /*
+ * Skip RTEs named *NEW* and *OLD*.
+ *
+ * XXX It would be nice to be able to use something else than just
+ * the names here ... However, rtekind does not work as expected :-(
+ */
+ if (strncmp(rte->eref->aliasname, "*NEW*", 6) == 0
+ || strncmp(rte->eref->aliasname, "*OLD*", 6) == 0)
+ continue;
+
+ return rte;
+ }
+
+ return NULL;
+ }
+
+ /*
+ * Rewrite varno's and varattno for the specified Var node if it is in
+ * a reversed order regarding to the underlying relation. The lookup
+ * table tentries holds all TargetEntries which are on a different
+ * location in the view definition. If var isn't on a different
+ * position in the current view than on its original relation, nothing
+ * is done.
+ *
+ * Note: This function assumes that the caller has already checked all
+ * parameters for NULL.
+ */
+ static void
+ adjustVarnoIfReversedCol(Var *var,
+ Index newRTE,
+ ViewDefColumnList tentries)
+ {
+ TargetEntry *entry = tentries[var->varattno - 1];
+
+ /*
+ * tentries holds NULL if given var isn't on a different location
+ * in the view Only replace if column order is reversed.
+ */
+ if (entry && entry->resno != var->varattno)
+ {
+ var->varattno = entry->resno;
+ var->varoattno = entry->resno;
+ }
+
+ /* Finally, make varno point to the *NEW* range table entry. */
+ var->varno = newRTE;
+ var->varnoold = newRTE;
+ }
+
+ /*
+ * Creates an equal operator expression for the specified Vars. They
+ * are assumed to be of the same type.
+ */
+ static OpExpr *
+ create_opexpr(Var *var_left, Var *var_right)
+ {
+ OpExpr *result;
+ HeapTuple tuple;
+ Form_pg_operator operator;
+ Oid eqOid;
+
+ AssertArg(var_left);
+ AssertArg(var_right);
+ Assert(var_left->vartype == var_right->vartype);
+
+ get_sort_group_operators(var_left->vartype, false, true, false,
+ NULL, &eqOid, NULL);
+
+ tuple = SearchSysCache(OPEROID, ObjectIdGetDatum(eqOid), 0, 0, 0);
+
+ operator = (Form_pg_operator) GETSTRUCT(tuple);
+ result = makeNode(OpExpr);
+
+ result->opno = HeapTupleGetOid(tuple);
+ result->opfuncid = operator->oprcode;
+ result->opresulttype = operator->oprresult;
+ result->opretset = false;
+
+ result->args = lappend(result->args, var_left);
+ result->args = lappend(result->args, var_right);
+
+ ReleaseSysCache(tuple);
+
+ return result;
+ }
+
+ /*
+ * Creates an expression tree for a WHERE clause.
+ *
+ * If from is not NULL, assigns the root node to the specified
+ * FromExpr of the target query tree.
+ *
+ * Note that the function appends the specified opExpr op to the
+ * specified anchor (if anchor != NULL) and returns that immediately.
+ * That way this function could be used to add operator nodes to an
+ * existing BoolExpr tree or (if from is given), to create a new Query
+ * qualification list.
+ */
+ static Node *
+ build_expression_tree(FromExpr *from, Node **anchor, BoolExpr *expr, OpExpr *op)
+ {
+ /* Already some nodes there? */
+ if (*anchor)
+ {
+ expr->args = lappend(expr->args, op);
+ ((BoolExpr *)(*anchor))->args = lappend(((BoolExpr *)(*anchor))->args,
+ expr);
+ *anchor = (Node *)expr;
+ }
+ else
+ {
+ /* Currently no nodes ... */
+ BoolExpr *boolexpr = makeNode(BoolExpr);
+ expr->args = lappend(expr->args, op);
+ boolexpr->args = lappend(boolexpr->args, expr);
+
+ *anchor = (Node *) boolexpr;
+
+ if (from)
+ from->quals = *anchor;
+ }
+
+ return *anchor;
+ }
+
+ /*
+ * Forms the WHERE clause for DELETE/UPDATE rules targeted to the
+ * specified view.
+ */
+ static void
+ form_where_for_updrule(const Query *select, /* View retrieve rule */
+ FromExpr **from, /* FromExpr for stmt */
+ const Relation rel, /* base relation of view */
+ Oid baserel, /* Oid of base relation */
+ Index baserti, /* Index of base relation RTE */
+ Index oldrti) /* Index of *OLD* RTE */
+ {
+ BoolExpr *expr = NULL;
+ Node *anchor = NULL;
+ Form_pg_attribute *attrs = rel->rd_att->attrs;
+ ListCell *cell;
+
+ AssertArg(baserti > 0);
+ AssertArg(oldrti > 0);
+ AssertArg(OidIsValid(baserel));
+ AssertArg(*from);
+ AssertArg(rel);
+
+ foreach(cell, select->targetList)
+ {
+ TargetEntry *te = (TargetEntry *) lfirst(cell);
+ Var *var1;
+ Var *var2;
+ OpExpr *op;
+ BoolExpr *null_condition;
+ NullTest *nulltest1;
+ NullTest *nulltest2;
+
+ /* If te->expr holds no Var pointer, continue. */
+ if (!IsA(te->expr, Var))
+ continue;
+
+ null_condition = makeNode(BoolExpr);
+ nulltest1 = makeNode(NullTest);
+ nulltest2 = makeNode(NullTest);
+
+ /*
+ * These are the new operands we had to check for equality.
+ *
+ * For DELETE/UPDATE rules, var1 points to the *OLD* RTE, var2
+ * references the base relation.
+ */
+ var1 = copyObject((Var *) (te->expr));
+
+ /*
+ * Look at varoattno to determine whether this attribute has a different
+ * location in the underlying base table. If that case, retrieve the
+ * attribute from the base table and assign it to var2; otherwise
+ * simply copy it to var1.
+ */
+ if (var1->varoattno > 0)
+ {
+ var2 = makeNode(Var);
+
+ var2->varno = baserti;
+ var2->varnoold = baserti;
+ var2->varattno = attrs[var1->varoattno - 1]->attnum;
+ var2->vartype = attrs[var1->varoattno - 1]->atttypid;
+ var2->vartypmod = attrs[var1->varoattno - 1]->atttypmod;
+ var2->varlevelsup = var1->varlevelsup;
+ var2->varnoold = var2->varno;
+ var2->varoattno = var2->varattno;
+ }
+ else
+ {
+ var2 = copyObject(var1);
+ var2->varno = baserti;
+ var2->varnoold = baserti;
+ }
+
+ var1->varno = oldrti;
+ var1->varnoold = oldrti;
+
+ /*
+ * rewrite varattno of var2 to point to the right column in relation
+ * *OLD* or *NEW*
+ */
+ var2->varattno = te->resorigcol;
+ var2->varoattno = te->resorigcol;
+
+ /*
+ * rewrite varattno of var1 to point to the right column in base
+ * relation
+ */
+ var1->varattno = te->resno;
+ var1->varoattno = te->resno;
+
+ op = create_opexpr(var1, var2);
+ expr = makeNode(BoolExpr);
+ expr->boolop = OR_EXPR;
+ null_condition->boolop = AND_EXPR;
+
+ /*
+ * Finally, create the OpExpr node, as part of a CaseExpr and
+ * include the OpExpr as part of the Case for managing NULL's
+ * we will do this everytime. That way we will have no
+ * problem with:
+ *
+ * ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL;
+ */
+
+ nulltest1->arg = (Expr *)var1;
+ nulltest1->nulltesttype = IS_NULL;
+
+ nulltest2->arg = (Expr *)var2;
+ nulltest2->nulltesttype = IS_NULL;
+
+ null_condition->args = lappend(null_condition->args, nulltest1);
+ null_condition->args = lappend(null_condition->args, nulltest2);
+ expr->args = lappend(expr->args, null_condition);
+
+ anchor = build_expression_tree(*from, (Node **) &anchor, expr, op);
+ }
+ }
+
+ /*
+ * Replaces the varnos for the specified targetlist to rtIndex
+ */
+ static bool
+ replace_tlist_varno_walker(Node *node,
+ ViewExprContext *ctxt)
+ {
+ AssertArg(ctxt);
+
+ if (!node)
+ return false;
+
+ switch(node->type)
+ {
+ case T_Var:
+ elog(DEBUG1, "adjusting varno old %d to new %d",
+ ((Var *)(node))->varno,
+ ctxt->newRTE);
+
+ ((Var *)(node))->varno = ctxt->newRTE;
+ adjustVarnoIfReversedCol((Var *)node,
+ ctxt->newRTE,
+ ctxt->tentries);
+ /* nothing more to do */
+ break;
+
+ case T_ArrayRef:
+ {
+ ArrayRef *array = (ArrayRef *) node;
+
+ /*
+ * Things are getting complicated here. We have found an
+ * array subscripting operation. It's necessary to
+ * examine all varno's found in this operation to make
+ * sure, we're getting right. This covers cases where a
+ * view selects a single index or complete array from a
+ * base table or view.
+ */
+
+ /*
+ * Look at expressions that evaluate upper array
+ * indexes. Make sure all varno's are modified. This is
+ * done by walking the expression tree recursively.
+ */
+ expression_tree_walker((Node *) array->refupperindexpr,
+ replace_tlist_varno_walker,
+ (void *)ctxt);
+
+ expression_tree_walker((Node *) array->reflowerindexpr,
+ replace_tlist_varno_walker,
+ (void *)ctxt);
+
+ expression_tree_walker((Node *) array->refexpr,
+ replace_tlist_varno_walker,
+ (void *)ctxt);
+
+ expression_tree_walker((Node *) array->refassgnexpr,
+ replace_tlist_varno_walker,
+ (void *)ctxt);
+ }
+
+ default:
+ break;
+ }
+
+ return expression_tree_walker(node, replace_tlist_varno_walker, ctxt);
+ }
+
+ /*
+ * Adds RTEs to form a query tree.
+ *
+ * select has to be a valid initialized view definition query tree
+ * (the function assumes that this query has passed the checkTree()
+ * function).
+ */
+ static void
+ form_query(const Query *select, Query *query, ViewDefColumnList tentries,
+ bool copyTargetList)
+ {
+ RangeTblEntry *rte;
+ Oid reloid;
+
+ AssertArg(select);
+ AssertArg(query);
+ AssertArg(tentries);
+
+ /* copy the range table entries */
+ query->rtable = copyObject(select->rtable);
+
+ /* prepare other stuff */
+ query->canSetTag = true;
+ query->jointree = makeNode(FromExpr);
+
+ /*
+ * Set result relation to the base relation.
+ *
+ * Since we currently only support updatable views with one
+ * underlying table, we simply extract the one relation which
+ * isn't labeled as *OLD* or *NEW*.
+ */
+ reloid = get_reloid_from_select(select, &(query->resultRelation), &rte);
+ if (!OidIsValid(reloid))
+ elog(ERROR, "could not retrieve base relation OID");
+
+ Assert(query->resultRelation > 0);
+
+ if (copyTargetList)
+ {
+ ViewExprContext ctxt;
+ ListCell *cell;
+
+ /* Copy all target entries. */
+ query->targetList = copyObject(select->targetList);
+
+ /*
+ * Replace all varnos to point to the *NEW* node in all targetentry
+ * expressions.
+ */
+
+ ctxt.newRTE = PRS2_NEW_VARNO;
+ ctxt.tentries = tentries;
+
+ foreach(cell, query->targetList)
+ {
+ Node *node = (Node *) lfirst(cell);
+ expression_tree_walker(node,
+ replace_tlist_varno_walker,
+ (void *) &ctxt);
+ }
+ }
+ }
+
+ /*
+ * Rewrite a TargetEntry, based on the given arguments to match
+ * the new Query tree of the new DELETE/UPDATE/INSERT rule and/or
+ * its underlying base relation.
+ *
+ * form_te_for_update() needs to carefully reassign Varno's of
+ * all Var expressions assigned to the given TargetEntry and to
+ * adjust all type info values and attribute index locations so
+ * that the rewritten TargetEntry corresponds to the correct
+ * column in the underlying base relation.
+ *
+ * Someone should consider that columns could be in reversed
+ * order in a view definition, so we need to take care to
+ * "restore" the correct order of all columns in the target list
+ * of the new view update rules.
+ *
+ * There's also some additional overhead if we have an array field
+ * involved. In this case we have to loop recursively through the
+ * array expressions to get all target entries right.
+ */
+ static void
+ form_te_for_update(int2 attnum, Form_pg_attribute attrs, Oid baserel,
+ Expr *expr, TargetEntry *te_update)
+ {
+ /*
+ * First, try if this is an array subscripting operation. If true, dive
+ * recursively into the subscripting tree examining all varnos.
+ */
+
+ if (IsA(expr, ArrayRef))
+ {
+ ArrayRef *array = (ArrayRef *) expr;
+
+ if (array->refassgnexpr != NULL)
+ form_te_for_update(attnum, attrs, baserel, array->refassgnexpr,
+ te_update);
+
+ if (array->refupperindexpr != NIL)
+ {
+ ListCell *cell;
+
+ foreach(cell, array->refupperindexpr)
+ form_te_for_update(attnum, attrs, baserel, (Expr *) lfirst(cell), te_update);
+ }
+
+ if (array->reflowerindexpr != NIL)
+ {
+ ListCell *cell;
+
+ foreach(cell, array->reflowerindexpr)
+ form_te_for_update(attnum, attrs, baserel, (Expr *) lfirst(cell), te_update);
+ }
+
+ if (array->refexpr != NULL)
+ form_te_for_update(attnum, attrs, baserel, array->refexpr,
+ te_update);
+ }
+ else if (IsA(expr, Var))
+ {
+ /*
+ * Base case of recursion: actually build the TargetEntry.
+ */
+ Var *upd_var = (Var *) (te_update->expr);
+
+ upd_var->varattno = te_update->resno;
+ upd_var->varoattno = te_update->resno;
+
+ upd_var->vartype = attrs->atttypid;
+ upd_var->vartypmod = attrs->atttypmod;
+
+ upd_var->varnoold = upd_var->varno;
+
+ te_update->resno = attnum;
+ te_update->resname = pstrdup(get_attname(baserel, attnum));
+ te_update->ressortgroupref = 0;
+ te_update->resorigcol = 0;
+ te_update->resorigtbl = 0;
+ te_update->resjunk = false;
+ }
+ }
+
+ /*
+ * Create the returning list for the given query tree. This allows
+ * using RETURING in view update actions. Note that the function
+ * creates the returning list from the target list of the given query
+ * tree if src is set to NULL. This requires to call
+ * build_update_target_list() on that query tree before. If src !=
+ * NULL, the target list is created from this query tree instead.
+ */
+ static void
+ create_rule_returning_list(Query *query, const Query *src, Index newRTE,
+ ViewDefColumnList tentries)
+ {
+ ViewExprContext ctxt;
+ ListCell *cell;
+
+ ctxt.newRTE = newRTE;
+ ctxt.tentries = tentries;
+
+ /* determine target list source */
+ if (src)
+ query->returningList = copyObject(src->targetList);
+ else
+ query->returningList = copyObject(query->targetList);
+
+ foreach(cell, query->returningList)
+ expression_tree_walker((Node *) lfirst(cell),
+ replace_tlist_varno_walker,
+ (void *) &ctxt);
+ }
+
+ /*
+ * Build the target list for a view UPDATE rule.
+ *
+ * Note: The function assumes a Query tree specified by update, which
+ * was copied by form_query(). We need the original Query tree to
+ * adjust the properties of each member of the TargetList of the new
+ * query tree.
+ */
+ static void
+ build_update_target_list(const Query *update, const Query *select,
+ Oid baserel, Relation rel)
+ {
+ ListCell *cell1;
+ ListCell *cell2;
+
+ /*
+ * This Assert() is appropriate, since we rely on a query tree
+ * created by from_query(), which copies the target list from the
+ * original query tree specified by the argument select, which
+ * holds the current view definition. So both target lists have
+ * to be equal in length.
+ */
+ Assert(list_length(update->targetList) == list_length(select->targetList));
+
+ /*
+ * Copy the target list of the view definition to the
+ * returningList. This is required to support RETURNING clauses
+ * in view update actions.
+ */
+ forboth(cell1, select->targetList, cell2, update->targetList)
+ {
+ TargetEntry *entry = (TargetEntry *) lfirst(cell1);
+ TargetEntry *upd_entry = (TargetEntry *) lfirst(cell2);
+ int attindex;
+ Form_pg_attribute attr;
+
+ if (entry->resorigcol > 0)
+ /*
+ * This column seems to have a different order than in the base
+ * table. We get the attribute from the base relation referenced
+ * by rel and create a new resdom. This new result domain is then
+ * assigned instead of the old one.
+ */
+ attindex = entry->resorigcol;
+ else
+ attindex = entry->resno;
+
+ attr = rel->rd_att->attrs[attindex - 1];
+
+ form_te_for_update(attindex, attr, baserel, upd_entry->expr,
+ upd_entry);
+ }
+ }
+
+ /*
+ * Check if we can create the insert rule for this view, i.e., if we
+ * can create DEFAULT values for all columns that need it.
+ *
+ * Up to now the only reason I can see to not create the rule is if
+ * the view does not contain all not null without default fields of
+ * the relation.
+ */
+ static bool
+ view_is_insertable(const Query *select, const Relation rel)
+ {
+ int2 natts = RelationGetNumberOfAttributes(rel);
+ int num_sys_cols = 0;
+ bool attr_ok[natts];
+ ListCell *cell;
+ int i;
+
+ /* Initialize attr_ok array with false values */
+ MemSet(attr_ok, false, sizeof(attr_ok));
+
+ /*
+ * Loop over the targetlist of the querytree and mark the table attributes
+ * that are present in the view definition.
+ */
+ foreach(cell, select->targetList)
+ {
+ TargetEntry *entry = (TargetEntry *) lfirst(cell);
+
+ /*
+ * Count non-Var expressions. We need to check if there are
+ * 'normal' table columns left.
+ */
+ if (!IsA(entry->expr, Var) && !IsA(entry->expr, ArrayRef))
+ num_sys_cols++;
+ else if (entry->resorigcol == 0)
+ continue; /* not a base rel attribute, ignored */
+ else if (entry->resorigcol < 0)
+ {
+ /* system column, ignored */
+ num_sys_cols++;
+ continue;
+ }
+
+ /* Mark the attribute as present in the view definition. */
+ attr_ok[entry->resorigcol - 1] = true;
+ }
+
+ /*
+ * Loop over table attributes to check whether they were all
+ * marked in the previous loop.
+ */
+ for (i = 0; i < natts; i++)
+ {
+ /* ignore dropped columns */
+ if (rel->rd_att->attrs[i]->attisdropped)
+ continue;
+
+ /* If the column is in the view definition, accept it */
+ if (attr_ok[i])
+ continue;
+
+ /*
+ * If there is a NOT NULL attribute which is not in the view definition
+ * and doesn't have a DEFAULT value, then the view is not insertable.
+ */
+ if (rel->rd_att->attrs[i]->attnotnull && !rel->rd_att->attrs[i]->atthasdef)
+ return false;
+ }
+
+ /*
+ * Finally, check if we have an empty target list.
+ */
+ if (list_length(select->targetList) - num_sys_cols <= 0)
+ return false;
+
+ return true;
+ }
+
+ /*
+ * Examines the columns by the current view and initializes the lookup
+ * table for all rearranged columns in base relations. The function
+ * requires a relation tree initialized by get_base_base_relations().
+ */
+ static void
+ read_rearranged_cols(ViewBaseRelation *tree)
+ {
+ AssertArg(tree);
+
+ if (tree->defs)
+ {
+ int num_items = list_length(tree->defs);
+ int i;
+
+ /*
+ * Traverse the relation tree and look on all base relations
+ * for reversed column order in their target lists. We have
+ * to perform a look-ahead-read on the tree, because we need
+ * to know how much columns the next base relation has, to
+ * allocate enough memory in tentries.
+ *
+ * Note that if we only have one base relation (a "real"
+ * table, not a view) exists, we have nothing to do, because
+ * this base relation cannot have a reversed column order
+ * caused by a view definition query.
+ */
+ for (i = num_items - 1; i > 0; i--)
+ {
+ ViewBaseRelationItem *item_current;
+ ViewBaseRelationItem *item_next;
+ ViewBaseRelation *current;
+ ViewBaseRelation *next;
+
+ current = (ViewBaseRelation *) list_nth(tree->defs, i);
+
+ /*
+ * We look ahead for the next base relation. We can do
+ * this here safely, because the loop condition terminates
+ * before reaching the list head.
+ */
+ next = (ViewBaseRelation *) list_nth(tree->defs, i - 1);
+
+ /*
+ * Note that the code currently requires a SQL-92 only
+ * relation tree. This means we handle one base relation
+ * per loop, only.
+ */
+ Assert(next);
+ Assert(current);
+ Assert(list_length(next->defs) == 1);
+ Assert(list_length(current->defs) == 1);
+
+ item_current = (ViewBaseRelationItem *) list_nth(current->defs, 0);
+ item_next = (ViewBaseRelationItem *) list_nth(next->defs, 0);
+
+ /* allocate tentries buffer */
+ item_current->tentries = (ViewDefColumnList) palloc(sizeof(TargetEntry *) * RelationGetNumberOfAttributes(item_next->rel));
+
+ copyReversedTargetEntryPtr(item_current->rule->targetList,
+ item_current->tentries);
+ }
+ }
+ }
+
+ /*
+ * Returns the base table(s) for the specified relation OID. The
+ * result is a list of all possible base table(s) the given view is
+ * based on.
+ */
+ static void
+ get_base_relations(ViewBaseRelation *tree, List **baserelations)
+ {
+ ListCell *acell;
+
+ /* nothing to do? */
+ if (!tree || !tree->defs)
+ return;
+
+ foreach(acell, tree->defs)
+ {
+ ListCell *bcell;
+ ViewBaseRelation *relations = (ViewBaseRelation *) lfirst(acell);
+
+ /* current children holds a base table? */
+ foreach(bcell, relations->defs)
+ {
+ ViewBaseRelationItem *item = (ViewBaseRelationItem *) lfirst(bcell);
+
+ if (item->rel->rd_rel->relkind != RELKIND_VIEW)
+ {
+ elog(DEBUG1, "found base relation %s",
+ RelationGetRelationName(item->rel));
+
+ *baserelations = lappend(*baserelations, item);
+ }
+ }
+ }
+ }
+
+ /*------------------------------------------------------------------------------
+ * Retrieves all relations from the view that can be considered a "base
+ * relation". The function returns a list that holds lists of all relation
+ * OIDs found for the view. The list is filled top down, that means the head of
+ * the list holds the relations for the "highest" view in the tree.
+ *
+ * Consider this view definition tree where each node is a relation the above
+ * node is based on:
+ *
+ * 1
+ * / \
+ * 2 3
+ * / \ \
+ * 4 5 6
+ * /
+ * 7
+ *
+ * The function will then return a list with the following layout:
+ *
+ * Listindex Node(s)
+ * --------------------------
+ * 1 7
+ * 2 4 5 6
+ * 3 2 3
+ *
+ * As you can see in the table, all relations that are "childs" of the
+ * given root relation (the view relation itself) are saved in the
+ * tree, except the root node itself.
+ *------------------------------------------------------------------------------
+ */
+ static void
+ get_base_base_relations(const Query *view, Oid baserel, List **list)
+ {
+ RangeTblEntry *entry;
+ unsigned int offset = 1;
+ ViewBaseRelation *childRel;
+
+ if (!view)
+ return;
+
+ childRel = (ViewBaseRelation *) palloc(sizeof(ViewBaseRelation));
+ childRel->defs = NIL;
+ childRel->parentRelation = baserel;
+
+ /* Get all OIDs from the RTE list of view. */
+ while ((entry = get_relation_RTE(view, &offset)) != NULL)
+ {
+ Relation rel;
+ ViewBaseRelationItem *item;
+
+ /*
+ * Is this really a view or relation?
+ *
+ * XXX -- maybe we don't need the lock here. It may be actively
+ * dangerous, if somewhere else we acquire a stronger lock later ...
+ */
+ rel = relation_open(entry->relid, AccessShareLock);
+
+ if (rel->rd_rel->relkind != RELKIND_RELATION &&
+ rel->rd_rel->relkind != RELKIND_VIEW)
+ {
+ /* don't need this one */
+ relation_close(rel, AccessShareLock);
+ continue;
+ }
+
+ item = (ViewBaseRelationItem *) palloc0(sizeof(ViewBaseRelationItem));
+ item->rel = rel;
+ item->rule = NULL;
+
+ if (rel->rd_rel->relkind == RELKIND_VIEW)
+ /*
+ * Get the rule _RETURN expression tree for the specified
+ * relation OID. We need this to recurse into the view
+ * base relation tree.
+ */
+ item->rule = get_return_rule(rel);
+
+ elog(DEBUG1, "extracted relation %s for relation tree",
+ RelationGetRelationName(rel));
+ childRel->defs = lappend(childRel->defs, item);
+
+ /* recurse to any other child relations */
+ if (item->rule)
+ get_base_base_relations(item->rule, RelationGetRelid(rel), list);
+
+ }
+
+ if (childRel->defs)
+ *list = lappend(*list, childRel);
+ }
+
+ static void
+ copyReversedTargetEntryPtr(List *targetList, ViewDefColumnList targets)
+ {
+ ListCell *cell;
+
+ AssertArg(targets);
+ AssertArg(targetList);
+
+ /* NOTE: We only reassign pointers. */
+ foreach(cell, targetList)
+ {
+ Node *node = (Node *) lfirst(cell);
+
+ if (IsA(node, TargetEntry))
+ {
+ /*
+ * Look at the resdom's resorigcol to determine whether
+ * this is a reversed column (meaning, it has a different
+ * column number than the underlying base table).
+ */
+ TargetEntry *entry = (TargetEntry *) node;
+
+ if (!IsA(entry->expr, Var))
+ /* nothing to do here */
+ continue;
+
+ if (entry->resorigcol > 0 && entry->resno != entry->resorigcol)
+ {
+ /*
+ * Save this TargetEntry to the appropiate place in
+ * the lookup table. Do it only if not already
+ * occupied (this could happen if the column is
+ * specified more than one time in the view
+ * definition).
+ */
+ if (targets[entry->resorigcol - 1] == NULL)
+ targets[entry->resorigcol - 1] = entry;
+ }
+ }
+ }
+ }
+
+ /*
+ * Transforms the specified view definition into an INSERT, UPDATE, or
+ * DELETE rule.
+ *
+ * Note: The function assumes that the specified query tree has passed the
+ * checkTree() function, and view_is_insertable() if CMD_INSERT.
+ */
+ static void
+ create_update_rule(const Query *select, const Relation rel,
+ ViewDefColumnList tentries,
+ CmdType ruletype)
+ {
+ Query *newquery;
+ Oid baserel;
+ Index baserti;
+ RangeTblEntry *entry;
+ RangeTblEntry *viewrte;
+
+ AssertArg(tentries);
+ AssertArg(rel);
+ AssertArg(select);
+ AssertArg(ruletype == CMD_INSERT || ruletype == CMD_UPDATE || ruletype == CMD_DELETE);
+
+ baserel = get_reloid_from_select(select, NULL, &entry);
+ if (!OidIsValid(baserel))
+ elog(ERROR, "could not get the base relation from the view definition");
+
+ newquery = makeNode(Query);
+ newquery->commandType = ruletype;
+
+ /* We don't need a targetlist in DELETE. */
+ form_query(select, newquery, tentries, ruletype != CMD_DELETE);
+
+ /*
+ * form_query() has prepared the jointree of the new UPDATE/DELETE rule.
+ *
+ * Now, our UPDATE rule needs range table references for the *NEW*
+ * and base relation RTEs. A DELETE rule needs range table
+ * references for the *OLD* and base relation RTEs.
+ */
+
+ baserti = get_rtindex_for_rel(newquery->rtable,
+ entry->eref->aliasname);
+ Assert(baserti > 0);
+
+ if (ruletype != CMD_INSERT)
+ {
+ RangeTblRef *oldref;
+ RangeTblRef *baseref;
+
+ oldref = makeNode(RangeTblRef);
+ oldref->rtindex = PRS2_OLD_VARNO;
+
+ baseref = makeNode(RangeTblRef);
+ baseref->rtindex = baserti;
+
+ newquery->jointree->fromlist = list_make2(baseref, oldref);
+
+ /* Create the WHERE condition qualification for the rule action. */
+ form_where_for_updrule(select, &(newquery->jointree),
+ rel, baserel, baserti, PRS2_OLD_VARNO);
+ }
+
+ if (ruletype != CMD_DELETE)
+ /*
+ * We must reorder the columns in the targetlist to match the
+ * underlying table. We do this after calling
+ * form_where_for_updrule() because build_update_target_list()
+ * relies on the original resdoms in the update tree.
+ */
+ build_update_target_list(newquery, select, baserel, rel);
+
+ /*
+ * Create the returning list now that build_update_target_list()
+ * has done the leg work.
+ */
+ if (ruletype == CMD_DELETE)
+ create_rule_returning_list(newquery, select, PRS2_OLD_VARNO, tentries);
+ else
+ create_rule_returning_list(newquery, NULL, PRS2_NEW_VARNO, tentries);
+
+ /*
+ * Get the relation name for the view
+ *
+ * XXX - I think it's safe here to rely on *NEW*, since the
+ * rewriter itself makes heavily use of this pseudorelation and it
+ * needs to be present in all rule events.
+ */
+ viewrte = rt_fetch(PRS2_NEW_VARNO, newquery->rtable);
+
+ /* Set ACL bit */
+ if (ruletype == CMD_INSERT)
+ entry->requiredPerms |= ACL_INSERT;
+ else if (ruletype == CMD_UPDATE)
+ entry->requiredPerms |= ACL_UPDATE;
+ else if (ruletype == CMD_DELETE)
+ entry->requiredPerms |= ACL_DELETE;
+
+ /*
+ * Check out wether we need to replace any existing automatic
+ * rules. This is required if we are going to replace an existing
+ * view with automatic update rules.
+ */
+
+ /* Create system rule */
+ DefineQueryRewrite(pstrdup(get_auto_rule_name(ruletype)),
+ viewrte->relid, /* event_relid */
+ NULL, /* WHERE clause */
+ ruletype,
+ true, /* is_instead */
+ true, /* is_auto */
+ true, /* replace */
+ list_make1(newquery) /* action */);
+ }
+
+ /*
+ * Checks the specified Query for updatability. Currently, SQL-92
+ * rules are implemented.
+ */
+ static bool
+ checkTree(const Query *query, ViewBaseRelation *tree)
+ {
+ ListCell *cell;
+ bool *visited;
+
+ AssertArg(query);
+ AssertArg(tree);
+ AssertArg(query->commandType == CMD_SELECT);
+
+ /*
+ * check for unsupported clauses in the view definition
+ */
+ if (query->hasAggs)
+ {
+ elog(DEBUG1, "view is not updatable because it uses an aggregate function");
+ return false;
+ }
+
+ if (list_length(query->groupClause) >= 1)
+ {
+ elog(DEBUG1, "view is not updatable because it contains a GROUP BY clause");
+ return false;
+ }
+
+ if (list_length(query->distinctClause) >= 1)
+ {
+ elog(DEBUG1, "view is not updatable because it contains a DISTINCT clause");
+ return false;
+ }
+
+ if (query->havingQual != NULL)
+ {
+ elog(DEBUG1, "view is not updatable because it contains a HAVING clause");
+ return false;
+ }
+
+ /*
+ * Test for number of involved relations. Since we assume to
+ * operate on a view definition SELECT query tree, we must count 3
+ * rtable entries. Otherwise this seems not to be a view based on
+ * a single relation.
+ */
+ if (list_length(query->rtable) > 3)
+ {
+ elog(DEBUG1, "view is not updatable because it has more than one underlying table");
+ return false;
+ }
+
+ /* Any rtable entries involved? */
+ if (list_length(query->rtable) < 3)
+ {
+ elog(DEBUG1, "view is not updatable because it has no underlying tables");
+ return false;
+ }
+
+ /*
+ * Walk down the target list and look for nodes that aren't Vars.
+ * SQL-92 doesn't allow functions, host variables, or constant
+ * expressions in the target list.
+ *
+ * Also, check if any of the target list entries are indexed array
+ * expressions, which aren't supported.
+ */
+ visited = (bool *)palloc(list_length(query->targetList) * sizeof(bool));
+ MemSet(visited, false, list_length(query->targetList) * sizeof(bool));
+
+ foreach(cell, query->targetList)
+ {
+ Node *node = (Node *) lfirst(cell);
+
+ if (IsA(node, TargetEntry))
+ {
+ TargetEntry *te = (TargetEntry *) node;
+
+ /*
+ * TODO -- it would be nice to support Const nodes here as well
+ * (but apparently it isn't in the standard)
+ */
+ if (!IsA(te->expr, Var) && !IsA(te->expr, ArrayRef))
+ {
+ elog(DEBUG1, "view is not updatable because select list contains a derived column");
+ return false;
+ }
+
+ /* This is an implementation shortcoming. */
+ if (IsA(te->expr, ArrayRef))
+ {
+ ArrayRef *ref = (ArrayRef *) te->expr;
+
+ if (ref->refupperindexpr != NIL)
+ {
+ elog(DEBUG1, "view is not updatable because select list contains an array element reference");
+ return false;
+ }
+ }
+
+ /* System columns aren't updatable. */
+ if (IsA(te->expr, Var))
+ {
+ Var *var = (Var *) te->expr;
+ if (var->varattno < 0)
+ {
+ elog(DEBUG1, "view is not updatable because select list references a system column");
+ return false;
+ }
+ else
+ {
+ if (visited[var->varattno - 1])
+ {
+ elog(DEBUG1, "view is not updatable because select list references the same column more than once");
+ pfree(visited);
+ return false;
+ }
+ else
+ visited[var->varattno - 1] = true;
+ }
+ }
+ }
+ }
+
+ /* not needed anymore */
+ pfree(visited);
+
+ /*
+ * Finally, check that all RTEs are valid. We have to look
+ * especially for table functions, which cannot be ever updatable.
+ */
+ foreach(cell, query->rtable)
+ {
+ RangeTblEntry *entry = (RangeTblEntry *) lfirst(cell);
+
+ if (entry->rtekind != RTE_RELATION)
+ {
+ elog(DEBUG1,
+ "view is not updatable because correlation \"%s\" is not a table",
+ entry->eref->aliasname);
+ return false;
+ }
+ }
+
+ return true;
+ }
+
+ /*
+ * Traverse the specified relation tree. The function stops at the
+ * base relations at the leafs of the tree. If any of the relations
+ * has more than one base relation, it is considered as a not SQL-92
+ * updatable view and false is returned.
+ */
+ static bool
+ check_reltree(ViewBaseRelation *node)
+ {
+ ListCell *cell;
+
+ AssertArg(node);
+
+ foreach(cell, node->defs)
+ {
+ /* Walk down the tree */
+ ViewBaseRelation *relations = (ViewBaseRelation *) lfirst(cell);
+
+ if (list_length(relations->defs) > 1)
+ {
+ elog(DEBUG1, "possible JOIN/UNION in view definition: %d",
+ list_length(relations->defs));
+ return false;
+ }
+ else if (list_length(relations->defs) == 1) {
+ ViewBaseRelationItem *item = (ViewBaseRelationItem *)
+ linitial(relations->defs);
+
+ /* if the relation found is a view, check its updatability */
+ if (item->rel->rd_rel->relkind == RELKIND_VIEW
+ && !checkTree(item->rule, relations))
+ {
+ elog(DEBUG1, "base view \"%s\" is not updatable",
+ RelationGetRelationName(item->rel));
+ return false;
+ }
+ }
+ }
+
+ return true;
+ }
+
+ /*
+ * Given a SELECT query tree, return the OID of the first RTE_RELATION range
+ * table entry found that is not *NEW* nor *OLD*.
+ *
+ * Also copies the RangeTblEntry into rel_entry, and the range table index
+ * into rti, unless they are NULL.
+ *
+ * This function assumes that the specified query tree was checked by a
+ * previous call to the checkTree() function.
+ */
+ static Oid
+ get_reloid_from_select(const Query *select, int *rti, RangeTblEntry **rel_entry)
+ {
+ ListCell *cell;
+ Oid result = InvalidOid;
+ int index;
+
+ /* Check specified query tree. Return immediately on error. */
+ if (select == NULL || select->commandType != CMD_SELECT)
+ return InvalidOid;
+
+ /*
+ * We loop through the RTEs to get information about all involved
+ * relations. We return the first OID we find in the list that is not
+ * *NEW* nor *OLD*.
+ */
+ index = 0;
+ foreach(cell, select->rtable)
+ {
+ RangeTblEntry *entry = (RangeTblEntry *) lfirst(cell);
+
+ index++;
+
+ if (entry == NULL)
+ elog(ERROR, "null RTE pointer in get_reloid_from_select");
+
+ elog(DEBUG1, "extracted range table entry for %u", entry->relid);
+
+ /* Return the first RELATION rte we find */
+ if (entry->rtekind == RTE_RELATION)
+ {
+ /*
+ * XXX This is ugly. The parser prepends two RTEs with rtekind
+ * RTE_RELATION named *NEW* and *OLD*. We have to exclude them by
+ * name! It would be much better if it used RTE_SPECIAL
+ * instead, but other parts of the system stop working if one
+ * just changes it naively.
+ */
+ if (strncmp(entry->eref->aliasname, "*NEW*", 6) == 0
+ || strncmp(entry->eref->aliasname, "*OLD*", 6) == 0)
+ continue;
+
+ result = entry->relid;
+ if (rti != NULL)
+ *rti = index;
+ if (rel_entry != NULL)
+ *rel_entry = copyObject(entry);
+ break;
+ }
+ }
+
+ return result;
+ }
+
+ /*
+ * get_return_rule: returns the _RETURN rule of a view as a Query node.
+ */
+ static Query *
+ get_return_rule(Relation rel)
+ {
+ Query *query = NULL;
+ int i;
+
+ AssertArg(rel->rd_rel->relkind == RELKIND_VIEW);
+
+ for (i = 0; i < rel->rd_rules->numLocks; i++)
+ {
+ RewriteRule *rule = rel->rd_rules->rules[i];
+
+ if (rule->event == CMD_SELECT)
+ {
+ /* A _RETURN rule should have only one action */
+ if (list_length(rule->actions) != 1)
+ elog(ERROR, "invalid _RETURN rule action specification");
+
+ query = linitial(rule->actions);
+ break;
+ }
+ }
+
+ return query;
+ }
+
+ /*------------------------------------------------------------------------------
+ * Public functions
+ *------------------------------------------------------------------------------
+ */
+
+ /*
+ * CreateViewUpdateRules
+ *
+ * This is the main entry point to creating an updatable view's rules. Given a
+ * rule definition, examine it, and create the rules if appropiate, or return
+ * doing nothing if not.
+ */
+ void
+ CreateViewUpdateRules(const Query *viewDef)
+ {
+ Relation rel;
+ Form_pg_attribute *attrs;
+ ViewDefColumnList tentries;
+ Oid baserel;
+ MemoryContext cxt;
+ MemoryContext oldcxt;
+ ViewBaseRelation *tree;
+ List *baserelations;
+ ListCell *cell;
+ bool is_insertable;
+
+ /*
+ * The routines in this file leak memory like crazy, so make sure we
+ * allocate it all in an appropiate context.
+ */
+ cxt = AllocSetContextCreate(TopTransactionContext,
+ "UpdateRulesContext",
+ ALLOCSET_DEFAULT_MINSIZE,
+ ALLOCSET_DEFAULT_INITSIZE,
+ ALLOCSET_DEFAULT_MAXSIZE);
+ oldcxt = MemoryContextSwitchTo(cxt);
+
+ /*
+ * Create the lookup table for the view definition target columns. We save
+ * the RESDOMS in that manner to look quickly for reversed column orders.
+ */
+
+ baserel = get_reloid_from_select(viewDef, NULL, NULL);
+
+ /* Get relation tree */
+ tree = (ViewBaseRelation *) palloc(sizeof(ViewBaseRelation));
+
+ tree->parentRelation = InvalidOid;
+ tree->defs = NIL;
+ get_base_base_relations(viewDef, baserel, &(tree->defs));
+
+ baserelations = NIL;
+ get_base_relations(tree, &baserelations);
+
+ /* Check the query tree for updatability */
+ if (!check_reltree(tree) || !checkTree(viewDef, tree))
+ {
+ elog(DEBUG1, "view is not updatable");
+ goto finish;
+ }
+
+ rel = heap_open(baserel, AccessExclusiveLock);
+ attrs = rel->rd_att->attrs;
+
+ /*
+ * Copy TargetEntries to match the slot numbers in the target list with
+ * their original column attribute number. Note that only pointers are
+ * copied and they are valid only as long as the specified SELECT query
+ * stays valid!
+ */
+ tentries = (ViewDefColumnList)
+ palloc0(rel->rd_rel->relnatts * sizeof(TargetEntry *));
+
+ copyReversedTargetEntryPtr(viewDef->targetList, tentries);
+
+ /*
+ * Now do the same for the base relation tree. read_rearranged_cols
+ * traverses the relation tree and performs a copyReversedTargetEntry()
+ * call to each base relation.
+ */
+ read_rearranged_cols(tree);
+
+ is_insertable = view_is_insertable(viewDef, rel);
+ if (is_insertable)
+ create_update_rule(viewDef, rel, tentries, CMD_INSERT);
+ create_update_rule(viewDef, rel, tentries, CMD_DELETE);
+ create_update_rule(viewDef, rel, tentries, CMD_UPDATE);
+
+ if (is_insertable)
+ ereport(NOTICE, (errmsg("CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules")));
+ else
+ ereport(NOTICE, (errmsg("CREATE VIEW has created automatic UPDATE and DELETE rules")));
+
+ /* free remaining stuff */
+ heap_close(rel, AccessExclusiveLock);
+
+ finish:
+ /* get_base_base_relations leaves some open relations */
+ foreach(cell, tree->defs)
+ {
+ ListCell *cell2;
+ ViewBaseRelation *vbr = (ViewBaseRelation *) lfirst(cell);
+
+ foreach(cell2, vbr->defs)
+ {
+ ViewBaseRelationItem *vbri = (ViewBaseRelationItem *) lfirst(cell2);
+
+ /* XXX should we keep the locks here? */
+ relation_close(vbri->rel, AccessShareLock);
+ }
+ }
+
+ MemoryContextSwitchTo(oldcxt);
+ MemoryContextDelete(cxt);
+ }
*** 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->is_auto = rewrite_form->is_auto;
/*
* 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->is_auto != rule2->is_auto)
+ return false;
}
}
else if (rlock2 != NULL)
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***************
*** 4003,4009 **** getRules(int *numRules)
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
! if (g_fout->remoteVersion >= 80300)
{
appendPQExpBuffer(query, "SELECT "
"tableoid, oid, rulename, "
--- 4003,4019 ----
/* Make sure we are in proper schema */
selectSourceSchema("pg_catalog");
! if (g_fout->remoteVersion >= 80400)
! {
! appendPQExpBuffer(query, "SELECT "
! "tableoid, oid, rulename, "
! "ev_class as ruletable, ev_type, is_instead, "
! "ev_enabled "
! "FROM pg_rewrite "
! "WHERE NOT is_auto "
! "ORDER BY oid");
! }
! else if (g_fout->remoteVersion >= 80300)
{
appendPQExpBuffer(query, "SELECT "
"tableoid, oid, rulename, "
*** 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,309 ----
*** a/src/include/catalog/pg_rewrite.h
--- b/src/include/catalog/pg_rewrite.h
***************
*** 40,45 **** CATALOG(pg_rewrite,2618)
--- 40,54 ----
char ev_enabled;
bool is_instead;
+ /*
+ * is_auto: True if the rule was automatically generated (update
+ * rules). This is tracked separately from the dependency system,
+ * because we want to allow overwriting the automatic update
+ * rules, so both automatically and manually generated rules have
+ * dependency type AUTO.
+ */
+ bool is_auto;
+
/* NB: remaining fields must be accessed via heap_getattr */
text ev_qual;
text ev_action;
***************
*** 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 */
--- 65,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_is_auto 7
! #define Anum_pg_rewrite_ev_qual 8
! #define Anum_pg_rewrite_ev_action 9
#endif /* PG_REWRITE_H */
*** 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;
+ bool is_auto;
} RewriteRule;
/*
*** a/src/include/rewrite/rewriteDefine.h
--- b/src/include/rewrite/rewriteDefine.h
***************
*** 29,34 **** extern void DefineQueryRewrite(char *rulename,
--- 29,35 ----
Node *event_qual,
CmdType event_type,
bool is_instead,
+ bool is_auto,
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 RemoveAutomaticRulesOnEvent(Relation rel, CmdType event_type,
+ Oid exceptOid);
#endif /* REWRITEREMOVE_H */
*** /dev/null
--- b/src/include/rewrite/viewUpdate.h
***************
*** 0 ****
--- 1,21 ----
+
+ /*-------------------------------------------------------------------------
+ *
+ * viewUpdate.h
+ *
+ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * $PostgreSQL$
+ *
+ *-------------------------------------------------------------------------
+ */
+ #ifndef VIEW_UPDATE_H
+ #define VIEW_UPDATE_H
+
+ #include "nodes/parsenodes.h"
+
+ extern void
+ CreateViewUpdateRules(const Query *viewDef);
+
+ #endif /* VIEW_UPDATE_H */
*** 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and DELETE rules
CREATE OR REPLACE VIEW viewtest AS
SELECT * FROM viewtest_tbl WHERE a > 10;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and DELETE rules
-- should be created in temp object schema
CREATE TEMP VIEW v2_temp AS SELECT * FROM base_table;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
-- should be created in temp_views schema
CREATE VIEW temp_view_test.v2 AS SELECT * FROM base_table;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and DELETE rules
CREATE VIEW v7 AS SELECT * FROM base_table WHERE NOT EXISTS (SELECT 1 FROM base_table2);
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
CREATE VIEW v8 AS SELECT * FROM base_table WHERE EXISTS (SELECT 1);
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic UPDATE and 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 has created automatic UPDATE and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and DELETE rules
/* The next *should* fail, but it's not implemented that way yet. */
CREATE VIEW atestv2 AS SELECT * FROM atest2;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and DELETE rules
create view rtest_vview2 as select a, b from rtest_view1 where v;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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 rtest_view1 x USING 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 rtest_view1 x SET a = new.a, b = new.b FROM 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 new.a, new.b;
+ rtest_vview2 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview2 DO INSTEAD DELETE FROM rtest_view1 USING 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 rtest_view1 SET a = new.a, b = new.b FROM 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 new.a, new.b;
+ rtest_vview3 | _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO rtest_vview3 DO INSTEAD DELETE FROM rtest_vview2 x USING 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 rtest_vview2 x SET a = new.a, b = new.b FROM 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 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 has created automatic INSERT, UPDATE, and 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 has created automatic INSERT, UPDATE, and 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);
*** /dev/null
--- b/src/test/regress/expected/view_update.out
***************
*** 0 ****
--- 1,344 ----
+ CREATE TABLE vutest1 (a integer, b text);
+ INSERT INTO vutest1 VALUES (1, 'one');
+ INSERT INTO vutest1 VALUES (2, 'two');
+ -- simple view updatability conditions
+ CREATE VIEW vutestv1 AS SELECT a, b FROM vutest1;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv2 AS SELECT * FROM vutest1;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv3 AS SELECT b, a FROM vutest1;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv4 AS SELECT a, b FROM vutest1 WHERE a < 5;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv5 AS SELECT sum(a) FROM vutest1; -- not updatable
+ CREATE VIEW vutestv6 AS SELECT b FROM vutest1 GROUP BY b; -- not updatable
+ CREATE VIEW vutestv7 AS SELECT l.b AS x, r.b AS y FROM vutest1 l, vutest1 r WHERE r.a = l.a; -- not updatable
+ CREATE VIEW vutestv8 AS SELECT 42; -- not updatable
+ CREATE VIEW vutestv9 AS SELECT a * 2 AS x, b || b AS y FROM vutest1; -- not updatable
+ CREATE VIEW vutestv10 AS SELECT a AS x, a AS y FROM vutest1; -- FIXME
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv11 AS SELECT * FROM generate_series(1, 5); -- not updatable
+ CREATE VIEW vutestv12 AS SELECT xmin, xmax, a, b FROM vutest1; -- not updatable
+ CREATE VIEW vutestv13 AS SELECT DISTINCT a, b FROM vutest1; -- not updatable
+ INSERT INTO vutestv1 VALUES (3, 'three');
+ INSERT INTO vutestv2 VALUES (4, 'four');
+ INSERT INTO vutestv3 VALUES (5, 'five'); -- fail
+ ERROR: invalid input syntax for integer: "five"
+ LINE 1: INSERT INTO vutestv3 VALUES (5, 'five');
+ ^
+ INSERT INTO vutestv3 VALUES ('five', 5);
+ INSERT INTO vutestv3 (a, b) VALUES (6, 'six');
+ INSERT INTO vutestv4 VALUES (7, 'seven'); -- ok, but would be check option issue
+ INSERT INTO vutestv5 VALUES (8); -- fail
+ ERROR: view is not insertable into
+ HINT: You need an unconditional ON INSERT DO INSTEAD rule.
+ INSERT INTO vutestv10 VALUES (5, 6); -- FIXME
+ ERROR: multiple assignments to same column "a"
+ SELECT * FROM vutest1;
+ a | b
+ ---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ 5 | five
+ 6 | six
+ 7 | seven
+ (7 rows)
+
+ SELECT * FROM vutestv1;
+ a | b
+ ---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ 5 | five
+ 6 | six
+ 7 | seven
+ (7 rows)
+
+ SELECT * FROM vutestv2;
+ a | b
+ ---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ 5 | five
+ 6 | six
+ 7 | seven
+ (7 rows)
+
+ SELECT * FROM vutestv3;
+ b | a
+ -------+---
+ one | 1
+ two | 2
+ three | 3
+ four | 4
+ five | 5
+ six | 6
+ seven | 7
+ (7 rows)
+
+ SELECT * FROM vutestv4;
+ a | b
+ ---+-------
+ 1 | one
+ 2 | two
+ 3 | three
+ 4 | four
+ (4 rows)
+
+ SELECT * FROM vutestv5;
+ sum
+ -----
+ 28
+ (1 row)
+
+ SELECT * FROM vutestv10; -- FIXME
+ x | y
+ ---+---
+ 1 | 1
+ 2 | 2
+ 3 | 3
+ 4 | 4
+ 5 | 5
+ 6 | 6
+ 7 | 7
+ (7 rows)
+
+ UPDATE vutestv1 SET b = 'a lot' WHERE a = 7;
+ DELETE FROM vutestv2 WHERE a = 1;
+ UPDATE vutestv4 SET b = b || '!' WHERE a > 1;
+ DELETE FROM vutestv4 WHERE a > 3;
+ UPDATE vutestv6 SET b = 37; -- fail
+ ERROR: view is not updatable
+ HINT: You need an unconditional ON UPDATE DO INSTEAD rule.
+ DELETE FROM vutestv5; -- fail
+ ERROR: view is not updatable
+ HINT: You need an unconditional ON DELETE DO INSTEAD rule.
+ SELECT * FROM vutest1 ORDER BY a, b;
+ a | b
+ ---+--------
+ 2 | two!
+ 3 | three!
+ 5 | five
+ 6 | six
+ 7 | a lot
+ (5 rows)
+
+ SELECT * FROM vutestv1 ORDER BY a, b;
+ a | b
+ ---+--------
+ 2 | two!
+ 3 | three!
+ 5 | five
+ 6 | six
+ 7 | a lot
+ (5 rows)
+
+ SELECT * FROM vutestv2 ORDER BY a, b;
+ a | b
+ ---+--------
+ 2 | two!
+ 3 | three!
+ 5 | five
+ 6 | six
+ 7 | a lot
+ (5 rows)
+
+ SELECT * FROM vutestv4 ORDER BY a, b;
+ a | b
+ ---+--------
+ 2 | two!
+ 3 | three!
+ (2 rows)
+
+ TRUNCATE TABLE vutest1;
+ -- views on views
+ CREATE VIEW vutestv20 AS SELECT a AS x, b AS y FROM vutestv1;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv21 AS SELECT x AS a FROM vutestv20 WHERE x % 2 = 0;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv22 AS SELECT sum(a) FROM vutestv21; -- not updatable
+ CREATE VIEW vutestv23 AS SELECT * FROM vutestv12; -- not updatable
+ INSERT INTO vutestv20 (x, y) VALUES (1, 'one');
+ INSERT INTO vutestv20 (x, y) VALUES (3, 'three');
+ INSERT INTO vutestv21 VALUES (2);
+ SELECT * FROM vutest1;
+ a | b
+ ---+-------
+ 1 | one
+ 3 | three
+ 2 |
+ (3 rows)
+
+ SELECT * FROM vutestv20;
+ x | y
+ ---+-------
+ 1 | one
+ 3 | three
+ 2 |
+ (3 rows)
+
+ SELECT * FROM vutestv21;
+ a
+ ---
+ 2
+ (1 row)
+
+ UPDATE vutestv20 SET y = 'eins' WHERE x = 1;
+ UPDATE vutestv21 SET a = 222;
+ SELECT * FROM vutest1;
+ a | b
+ -----+-------
+ 3 | three
+ 1 | eins
+ 222 |
+ (3 rows)
+
+ SELECT * FROM vutestv20;
+ x | y
+ -----+-------
+ 3 | three
+ 1 | eins
+ 222 |
+ (3 rows)
+
+ SELECT * FROM vutestv21;
+ a
+ -----
+ 222
+ (1 row)
+
+ DELETE FROM vutestv20 WHERE x = 3;
+ SELECT * FROM vutest1;
+ a | b
+ -----+------
+ 1 | eins
+ 222 |
+ (2 rows)
+
+ SELECT * FROM vutestv20;
+ x | y
+ -----+------
+ 1 | eins
+ 222 |
+ (2 rows)
+
+ SELECT * FROM vutestv21;
+ a
+ -----
+ 222
+ (1 row)
+
+ -- insertability conditions
+ CREATE TABLE vutest2 (a int PRIMARY KEY, b text NOT NULL, c text NOT NULL DEFAULT 'foo');
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vutest2_pkey" for table "vutest2"
+ CREATE VIEW vutestv30 AS SELECT a, b, c FROM vutest2;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv31 AS SELECT a, b FROM vutest2;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv32 AS SELECT a, c FROM vutest2; -- not insertable into
+ NOTICE: CREATE VIEW has created automatic UPDATE and DELETE rules
+ CREATE VIEW vutestv33 AS SELECT b, c FROM vutest2; -- not insertable into
+ NOTICE: CREATE VIEW has created automatic UPDATE and DELETE rules
+ CREATE VIEW vutestv34 AS SELECT a FROM vutest2; -- not insertable into
+ NOTICE: CREATE VIEW has created automatic UPDATE and DELETE rules
+ CREATE VIEW vutestv35 AS SELECT b FROM vutest2; -- not insertable into
+ NOTICE: CREATE VIEW has created automatic UPDATE and DELETE rules
+ CREATE VIEW vutestv36 AS SELECT c FROM vutest2; -- not insertable into
+ NOTICE: CREATE VIEW has created automatic UPDATE and DELETE rules
+ INSERT INTO vutestv30 VALUES (1, 'one', 'eins');
+ INSERT INTO vutestv31 VALUES (2, 'two');
+ INSERT INTO vutestv32 VALUES (3, 'drei'); -- fail
+ ERROR: view is not insertable into
+ HINT: You need an unconditional ON INSERT DO INSTEAD rule.
+ UPDATE vutestv31 SET a = 22 WHERE a = 2;
+ UPDATE vutestv32 SET c = 'drei!' WHERE a = 3; -- ok, despite not insertable into
+ SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv%' ORDER BY tablename, rulename;
+ rulename | definition
+ ----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv1 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv1 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv1 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b FROM vutest1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv10 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.x IS NOT NULL) THEN (old.x = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.y IS NOT NULL) THEN (old.y = vutest1.a) ELSE (vutest1.a IS NULL) END)) RETURNING old.y AS x, old.y;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv10 DO INSTEAD INSERT INTO vutest1 (a, a) VALUES (new.x, new.y) RETURNING new.y AS a, new.y AS a;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv10 DO INSTEAD UPDATE vutest1 SET a = new.x, a = new.y FROM vutest1 WHERE ((CASE WHEN (old.x IS NOT NULL) THEN (old.x = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.y IS NOT NULL) THEN (old.y = vutest1.a) ELSE (vutest1.a IS NULL) END)) RETURNING new.y AS a, new.y AS a;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv2 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv2 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv2 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b FROM vutest1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv20 DO INSTEAD DELETE FROM vutestv1 USING vutestv1 WHERE ((CASE WHEN (old.x IS NOT NULL) THEN (old.x = vutestv1.a) ELSE (vutestv1.a IS NULL) END) AND (CASE WHEN (old.y IS NOT NULL) THEN (old.y = vutestv1.b) ELSE (vutestv1.b IS NULL) END)) RETURNING old.x, old.y;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv20 DO INSTEAD INSERT INTO vutestv1 (a, b) VALUES (new.x, new.y) RETURNING new.x AS a, new.y AS b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv20 DO INSTEAD UPDATE vutestv1 SET a = new.x, b = new.y FROM vutestv1 WHERE ((CASE WHEN (old.x IS NOT NULL) THEN (old.x = vutestv1.a) ELSE (vutestv1.a IS NULL) END) AND (CASE WHEN (old.y IS NOT NULL) THEN (old.y = vutestv1.b) ELSE (vutestv1.b IS NULL) END)) RETURNING new.x AS a, new.y AS b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv21 DO INSTEAD DELETE FROM vutestv20 USING vutestv20 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutestv20.x) ELSE (vutestv20.x IS NULL) END)) RETURNING old.a;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv21 DO INSTEAD INSERT INTO vutestv20 (x) VALUES (new.a) RETURNING new.a AS x;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv21 DO INSTEAD UPDATE vutestv20 SET x = new.a FROM vutestv20 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutestv20.x) ELSE (vutestv20.x IS NULL) END)) RETURNING new.a AS x;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv3 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END) AND (CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END)) RETURNING old.b, old.a;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv3 DO INSTEAD INSERT INTO vutest1 (b, a) VALUES (new.b, new.a) RETURNING new.a AS b, new.b AS a;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv3 DO INSTEAD UPDATE vutest1 SET b = new.b, a = new.a FROM vutest1 WHERE ((CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END) AND (CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END)) RETURNING new.a AS b, new.b AS a;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv30 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END AND (CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END))) RETURNING old.a, old.b, old.c;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv30 DO INSTEAD INSERT INTO vutest2 (a, b, c) VALUES (new.a, new.b, new.c) RETURNING new.a, new.b, new.c;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv30 DO INSTEAD UPDATE vutest2 SET a = new.a, b = new.b, c = new.c FROM vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END AND (CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END))) RETURNING new.a, new.b, new.c;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv31 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END)) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv31 DO INSTEAD INSERT INTO vutest2 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv31 DO INSTEAD UPDATE vutest2 SET a = new.a, b = new.b FROM vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END)) RETURNING new.a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv32 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END)) RETURNING old.a, old.c;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv32 DO INSTEAD UPDATE vutest2 SET a = new.a, c = new.c FROM vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END)) RETURNING new.a, new.c;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv33 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END) AND (CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END)) RETURNING old.b, old.c;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv33 DO INSTEAD UPDATE vutest2 SET b = new.b, c = new.c FROM vutest2 WHERE ((CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END) AND (CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END)) RETURNING new.b, new.b AS c;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv34 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END)) RETURNING old.a;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv34 DO INSTEAD UPDATE vutest2 SET a = new.a FROM vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END)) RETURNING new.a;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv35 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END)) RETURNING old.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv35 DO INSTEAD UPDATE vutest2 SET b = new.b FROM vutest2 WHERE ((CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END)) RETURNING new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv36 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END)) RETURNING old.c;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv36 DO INSTEAD UPDATE vutest2 SET c = new.c FROM vutest2 WHERE ((CASE WHEN (old.c IS NOT NULL) THEN (old.c = vutest2.c) ELSE (vutest2.c IS NULL) END)) RETURNING new.c;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv4 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv4 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv4 DO INSTEAD UPDATE vutest1 SET a = new.a, b = new.b FROM vutest1 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING new.a, new.b;
+ (37 rows)
+
+ -- interaction of manual and automatic rules, view replacement
+ CREATE VIEW vutestv40 AS SELECT a, b FROM vutest1;
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE RULE zmy_update AS ON UPDATE TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+ CREATE RULE "_INSERT" AS ON INSERT TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- replaces automatic _INSERT rule
+ CREATE RULE zmy_delete AS ON DELETE TO vutestv40 DO ALSO DELETE FROM vutest1; -- replaces automatic _DELETE rule FIXME
+ CREATE VIEW vutestv41 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+ CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv41 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+ CREATE OR REPLACE VIEW vutestv41 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, custom _UPDATE rule replaced
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv42 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+ CREATE RULE zmy_update AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+ CREATE OR REPLACE VIEW vutestv42 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, broken FIXME
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE VIEW vutestv43 AS SELECT a AS aa, b FROM vutest1; -- updatable
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE OR REPLACE VIEW vutestv43 AS SELECT a + 1 AS aa, b FROM vutest1; -- no longer updatable, broken FIXME
+ CREATE VIEW vutestv44 AS SELECT a, b FROM vutest1; -- updatable
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ CREATE OR REPLACE VIEW vutestv44 AS SELECT a, b FROM vutest2; -- update rules are updated, broken FIXME
+ NOTICE: CREATE VIEW has created automatic INSERT, UPDATE, and DELETE rules
+ SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv4_' ORDER BY tablename, rulename;
+ rulename | definition
+ ------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv40 DO INSTEAD DELETE FROM vutest1;
+ zmy_delete | CREATE RULE zmy_delete AS ON DELETE TO vutestv40 DO DELETE FROM vutest1;
+ zmy_update | CREATE RULE zmy_update AS ON UPDATE TO vutestv40 DO INSTEAD DELETE FROM vutest1;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv41 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.aa IS NOT NULL) THEN (old.aa = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING old.aa, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv41 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.aa, new.b) RETURNING new.aa AS a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv41 DO INSTEAD UPDATE vutest1 SET a = new.aa, b = new.b FROM vutest1 WHERE ((CASE WHEN (old.aa IS NOT NULL) THEN (old.aa = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING new.aa AS a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv42 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.aa IS NOT NULL) THEN (old.aa = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING old.aa, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv42 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.aa, new.b) RETURNING new.aa AS a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = new.aa, b = new.b FROM vutest1 WHERE ((CASE WHEN (old.aa IS NOT NULL) THEN (old.aa = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING new.aa AS a, new.b;
+ zmy_update | CREATE RULE zmy_update AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = (new.aa - 1), b = new.b WHERE ((vutest1.a = (old.aa - 1)) AND (vutest1.b = old.b));
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv43 DO INSTEAD DELETE FROM vutest1 USING vutest1 WHERE ((CASE WHEN (old.aa IS NOT NULL) THEN (old.aa = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING old.aa, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv43 DO INSTEAD INSERT INTO vutest1 (a, b) VALUES (new.aa, new.b) RETURNING new.aa AS a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv43 DO INSTEAD UPDATE vutest1 SET a = new.aa, b = new.b FROM vutest1 WHERE ((CASE WHEN (old.aa IS NOT NULL) THEN (old.aa = vutest1.a) ELSE (vutest1.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest1.b) ELSE (vutest1.b IS NULL) END)) RETURNING new.aa AS a, new.b;
+ _DELETE | CREATE RULE "_DELETE" AS ON DELETE TO vutestv44 DO INSTEAD DELETE FROM vutest2 USING vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END)) RETURNING old.a, old.b;
+ _INSERT | CREATE RULE "_INSERT" AS ON INSERT TO vutestv44 DO INSTEAD INSERT INTO vutest2 (a, b) VALUES (new.a, new.b) RETURNING new.a, new.b;
+ _UPDATE | CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv44 DO INSTEAD UPDATE vutest2 SET a = new.a, b = new.b FROM vutest2 WHERE ((CASE WHEN (old.a IS NOT NULL) THEN (old.a = vutest2.a) ELSE (vutest2.a IS NULL) END) AND (CASE WHEN (old.b IS NOT NULL) THEN (old.b = vutest2.b) ELSE (vutest2.b IS NULL) END)) RETURNING new.a, new.b;
+ (16 rows)
+
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 79,84 **** test: misc
--- 79,86 ----
# ----------
test: select_views portals_p2 rules foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window
+ test: view_update
+
# ----------
# Another group of parallel tests
# NB: temp.sql does a reconnect which transiently uses 2 connections,
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 100,105 **** test: tsearch
--- 100,106 ----
test: tsdicts
test: foreign_data
test: window
+ test: view_update
test: plancache
test: limit
test: plpgsql
*** /dev/null
--- b/src/test/regress/sql/view_update.sql
***************
*** 0 ****
--- 1,128 ----
+ CREATE TABLE vutest1 (a integer, b text);
+ INSERT INTO vutest1 VALUES (1, 'one');
+ INSERT INTO vutest1 VALUES (2, 'two');
+
+
+ -- simple view updatability conditions
+
+ CREATE VIEW vutestv1 AS SELECT a, b FROM vutest1;
+ CREATE VIEW vutestv2 AS SELECT * FROM vutest1;
+ CREATE VIEW vutestv3 AS SELECT b, a FROM vutest1;
+ CREATE VIEW vutestv4 AS SELECT a, b FROM vutest1 WHERE a < 5;
+ CREATE VIEW vutestv5 AS SELECT sum(a) FROM vutest1; -- not updatable
+ CREATE VIEW vutestv6 AS SELECT b FROM vutest1 GROUP BY b; -- not updatable
+ CREATE VIEW vutestv7 AS SELECT l.b AS x, r.b AS y FROM vutest1 l, vutest1 r WHERE r.a = l.a; -- not updatable
+ CREATE VIEW vutestv8 AS SELECT 42; -- not updatable
+ CREATE VIEW vutestv9 AS SELECT a * 2 AS x, b || b AS y FROM vutest1; -- not updatable
+ CREATE VIEW vutestv10 AS SELECT a AS x, a AS y FROM vutest1; -- FIXME
+ CREATE VIEW vutestv11 AS SELECT * FROM generate_series(1, 5); -- not updatable
+ CREATE VIEW vutestv12 AS SELECT xmin, xmax, a, b FROM vutest1; -- not updatable
+ CREATE VIEW vutestv13 AS SELECT DISTINCT a, b FROM vutest1; -- not updatable
+
+ INSERT INTO vutestv1 VALUES (3, 'three');
+ INSERT INTO vutestv2 VALUES (4, 'four');
+ INSERT INTO vutestv3 VALUES (5, 'five'); -- fail
+ INSERT INTO vutestv3 VALUES ('five', 5);
+ INSERT INTO vutestv3 (a, b) VALUES (6, 'six');
+ INSERT INTO vutestv4 VALUES (7, 'seven'); -- ok, but would be check option issue
+ INSERT INTO vutestv5 VALUES (8); -- fail
+ INSERT INTO vutestv10 VALUES (5, 6); -- FIXME
+
+ SELECT * FROM vutest1;
+ SELECT * FROM vutestv1;
+ SELECT * FROM vutestv2;
+ SELECT * FROM vutestv3;
+ SELECT * FROM vutestv4;
+ SELECT * FROM vutestv5;
+ SELECT * FROM vutestv10; -- FIXME
+
+ UPDATE vutestv1 SET b = 'a lot' WHERE a = 7;
+ DELETE FROM vutestv2 WHERE a = 1;
+ UPDATE vutestv4 SET b = b || '!' WHERE a > 1;
+ DELETE FROM vutestv4 WHERE a > 3;
+ UPDATE vutestv6 SET b = 37; -- fail
+ DELETE FROM vutestv5; -- fail
+
+ SELECT * FROM vutest1 ORDER BY a, b;
+ SELECT * FROM vutestv1 ORDER BY a, b;
+ SELECT * FROM vutestv2 ORDER BY a, b;
+ SELECT * FROM vutestv4 ORDER BY a, b;
+
+ TRUNCATE TABLE vutest1;
+
+
+ -- views on views
+
+ CREATE VIEW vutestv20 AS SELECT a AS x, b AS y FROM vutestv1;
+ CREATE VIEW vutestv21 AS SELECT x AS a FROM vutestv20 WHERE x % 2 = 0;
+ CREATE VIEW vutestv22 AS SELECT sum(a) FROM vutestv21; -- not updatable
+ CREATE VIEW vutestv23 AS SELECT * FROM vutestv12; -- not updatable
+
+ INSERT INTO vutestv20 (x, y) VALUES (1, 'one');
+ INSERT INTO vutestv20 (x, y) VALUES (3, 'three');
+ INSERT INTO vutestv21 VALUES (2);
+
+ SELECT * FROM vutest1;
+ SELECT * FROM vutestv20;
+ SELECT * FROM vutestv21;
+
+ UPDATE vutestv20 SET y = 'eins' WHERE x = 1;
+ UPDATE vutestv21 SET a = 222;
+
+ SELECT * FROM vutest1;
+ SELECT * FROM vutestv20;
+ SELECT * FROM vutestv21;
+
+ DELETE FROM vutestv20 WHERE x = 3;
+
+ SELECT * FROM vutest1;
+ SELECT * FROM vutestv20;
+ SELECT * FROM vutestv21;
+
+
+ -- insertability conditions
+
+ CREATE TABLE vutest2 (a int PRIMARY KEY, b text NOT NULL, c text NOT NULL DEFAULT 'foo');
+
+ CREATE VIEW vutestv30 AS SELECT a, b, c FROM vutest2;
+ CREATE VIEW vutestv31 AS SELECT a, b FROM vutest2;
+ CREATE VIEW vutestv32 AS SELECT a, c FROM vutest2; -- not insertable into
+ CREATE VIEW vutestv33 AS SELECT b, c FROM vutest2; -- not insertable into
+ CREATE VIEW vutestv34 AS SELECT a FROM vutest2; -- not insertable into
+ CREATE VIEW vutestv35 AS SELECT b FROM vutest2; -- not insertable into
+ CREATE VIEW vutestv36 AS SELECT c FROM vutest2; -- not insertable into
+
+ INSERT INTO vutestv30 VALUES (1, 'one', 'eins');
+ INSERT INTO vutestv31 VALUES (2, 'two');
+ INSERT INTO vutestv32 VALUES (3, 'drei'); -- fail
+
+ UPDATE vutestv31 SET a = 22 WHERE a = 2;
+ UPDATE vutestv32 SET c = 'drei!' WHERE a = 3; -- ok, despite not insertable into
+
+
+ SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv%' ORDER BY tablename, rulename;
+
+
+ -- interaction of manual and automatic rules, view replacement
+
+ CREATE VIEW vutestv40 AS SELECT a, b FROM vutest1;
+ CREATE RULE zmy_update AS ON UPDATE TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- drops automatic _UPDATE rule
+ CREATE RULE "_INSERT" AS ON INSERT TO vutestv40 DO INSTEAD DELETE FROM vutest1; -- replaces automatic _INSERT rule
+ CREATE RULE zmy_delete AS ON DELETE TO vutestv40 DO ALSO DELETE FROM vutest1; -- replaces automatic _DELETE rule FIXME
+
+ CREATE VIEW vutestv41 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+ CREATE RULE "_UPDATE" AS ON UPDATE TO vutestv41 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+ CREATE OR REPLACE VIEW vutestv41 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, custom _UPDATE rule replaced
+
+ CREATE VIEW vutestv42 AS SELECT a + 1 AS aa, b FROM vutest1; -- not updatable
+ CREATE RULE zmy_update AS ON UPDATE TO vutestv42 DO INSTEAD UPDATE vutest1 SET a = new.aa - 1, b = new.b WHERE a = old.aa - 1 AND b = old.b;
+ CREATE OR REPLACE VIEW vutestv42 AS SELECT a AS aa, b FROM vutest1; -- *now* updatable, broken FIXME
+
+ CREATE VIEW vutestv43 AS SELECT a AS aa, b FROM vutest1; -- updatable
+ CREATE OR REPLACE VIEW vutestv43 AS SELECT a + 1 AS aa, b FROM vutest1; -- no longer updatable, broken FIXME
+
+ CREATE VIEW vutestv44 AS SELECT a, b FROM vutest1; -- updatable
+ CREATE OR REPLACE VIEW vutestv44 AS SELECT a, b FROM vutest2; -- update rules are updated, broken FIXME
+
+
+ SELECT rulename, definition FROM pg_rules WHERE tablename LIKE 'vutestv4_' ORDER BY tablename, rulename;