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