Updated patch: - include sgml - fix all compiler warnings - some cleanup - fix correctness of feature Regards, Karol
diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 90b9208..eba35f0 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -194,12 +194,27 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ] [ <term><replaceable class="PARAMETER">output_expression</replaceable></term> <listitem> <para> - An expression to be computed and returned by the <command>UPDATE</> - command after each row is updated. The expression can use any - column names of the table named by <replaceable class="PARAMETER">table_name</replaceable> - or table(s) listed in <literal>FROM</>. - Write <literal>*</> to return all columns. + An expression to be computed and returned by the + <command>UPDATE</> command either before or after (prefixed with + <literal>BEFORE.</literal> and <literal>AFTER.</literal>, + respectively) each row is updated. The expression can use any + column names of the table named by <replaceable + class="PARAMETER">table_name</replaceable> or table(s) listed in + <literal>FROM</>. Write <literal>AFTER.*</literal> to return all + columns after the update. Write <literal>BEFORE.*</literal> for all + columns before the update. Write <literal>*</literal> to return all + columns after update and all triggers fired (these values are in table + after command). You may combine BEFORE, AFTER and raw columns in the + expression. </para> + <warning><para> + Mixing table names or aliases named before or after with the + above will result in confusion and suffering. If you happen to + have a table called <literal>before</literal> or + <literal>after</literal>, alias it to something else when using + RETURNING. + </para></warning> + </listitem> </varlistentry> @@ -287,15 +302,16 @@ UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT </para> <para> - Perform the same operation and return the updated entries: + Perform the same operation and return information on the changed entries: <programlisting> UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03' - RETURNING temp_lo, temp_hi, prcp; + RETURNING temp_lo AS new_low, temp_hi AS new_high, BEFORE.temp_hi/BEFORE.temp_low AS old_ratio, AFTER.temp_hi/AFTER.temp_low AS new_ratio prcp; </programlisting> </para> + <para> Use the alternative column-list syntax to do the same update: <programlisting> diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 42d6621..cc68568 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -1920,6 +1920,7 @@ range_table_walker(List *rtable, { case RTE_RELATION: case RTE_CTE: + case RTE_BEFORE: /* nothing to do */ break; case RTE_SUBQUERY: @@ -2622,6 +2623,7 @@ range_table_mutator(List *rtable, { case RTE_RELATION: case RTE_CTE: + case RTE_BEFORE: /* we don't bother to copy eref, aliases, etc; OK? */ break; case RTE_SUBQUERY: diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index b2183f4..2698535 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2351,6 +2351,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node) switch (node->rtekind) { case RTE_RELATION: + case RTE_BEFORE: WRITE_OID_FIELD(relid); WRITE_CHAR_FIELD(relkind); break; diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 3a16e9d..04931ee 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -1189,6 +1189,7 @@ _readRangeTblEntry(void) switch (local_node->rtekind) { case RTE_RELATION: + case RTE_BEFORE: READ_OID_FIELD(relid); READ_CHAR_FIELD(relkind); break; diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c index 839ed9d..7fc6ea1 100644 --- a/src/backend/optimizer/plan/initsplan.c +++ b/src/backend/optimizer/plan/initsplan.c @@ -174,9 +174,16 @@ add_vars_to_targetlist(PlannerInfo *root, List *vars, if (IsA(node, Var)) { Var *var = (Var *) node; - RelOptInfo *rel = find_base_rel(root, var->varno); + RelOptInfo *rel; int attno = var->varattno; + RangeTblEntry *rte; + if (root->parse->commandType == CMD_UPDATE){ + rte = ((RangeTblEntry *) list_nth(root->parse->rtable, (var->varno)-1)); + if(rte->rtekind == RTE_BEFORE) + continue; + } + rel = find_base_rel(root, var->varno); Assert(attno >= rel->min_attr && attno <= rel->max_attr); attno -= rel->min_attr; if (rel->attr_needed[attno] == NULL) diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index d80c264..77b0c38 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -1989,6 +1989,9 @@ preprocess_rowmarks(PlannerInfo *root) if (rte->relkind == RELKIND_FOREIGN_TABLE) continue; + if (rte->relkind == RELKIND_BEFORE) + continue; + rels = bms_del_member(rels, rc->rti); newrc = makeNode(PlanRowMark); @@ -2028,6 +2031,9 @@ preprocess_rowmarks(PlannerInfo *root) if (!bms_is_member(i, rels)) continue; + if (rte->relkind == RELKIND_BEFORE) + continue; + newrc = makeNode(PlanRowMark); newrc->rti = newrc->prti = i; newrc->rowmarkId = ++(root->glob->lastRowMarkId); diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index b78d727..b30c6f8 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -134,6 +134,7 @@ static List *set_returning_clause_references(PlannerInfo *root, static bool fix_opfuncids_walker(Node *node, void *context); static bool extract_query_dependencies_walker(Node *node, PlannerInfo *context); +static void fix_varno_varattno(List *rlist, int aft); /***************************************************************************** @@ -1691,6 +1692,12 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context) if (IsA(node, Var)) { Var *var = (Var *) node; + if (var->varno<=list_length(context->root->parse->rtable) && var->varno>1 && context->root->parse->commandType == CMD_UPDATE){ + RangeTblEntry *rte_a,*rte_b; + rte_a = (RangeTblEntry *)list_nth(context->root->parse->rtable,var->varno-1); + rte_b = (RangeTblEntry *)list_nth(context->root->parse->rtable,var->varno-2); + if (rte_a->rtekind == RTE_BEFORE && rte_b->rtekind == RTE_BEFORE) var->varno-=1; + } /* First look for the var in the input tlists */ newvar = search_indexed_tlist_for_var(var, @@ -1892,6 +1899,35 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context) * * Note: resultRelation is not yet adjusted by rtoffset. */ + +void fix_varno_varattno(List *rlist, int aft){ + ListCell *temp; + Var *var = NULL; + foreach(temp, rlist){ + TargetEntry *tle = (TargetEntry *)lfirst(temp); + + var = NULL; + if(IsA(tle, TargetEntry)){ + var = (Var*)tle->expr; + }else if(IsA(tle, Var)) var=(Var*)tle; + if(var){ + if( IsA(var, Var) ){ + if(var->varnoold == aft) + { + var->varno = OUTER_VAR; + var->varattno = var->varoattno; + } + } + else if( IsA(var, OpExpr )){ + fix_varno_varattno(((OpExpr*)var)->args, aft); + } + else if( IsA(var, FuncExpr )){ + fix_varno_varattno(((FuncExpr*)var)->args, aft); + } + } + } +} + static List * set_returning_clause_references(PlannerInfo *root, List *rlist, @@ -1900,7 +1936,24 @@ set_returning_clause_references(PlannerInfo *root, int rtoffset) { indexed_tlist *itlist; + int after_index=0; + Query *parse = root->parse; + + ListCell *rt; + RangeTblEntry *bef; + int index_rel=1; + + foreach(rt,parse->rtable) + { + bef = (RangeTblEntry *)lfirst(rt); + if(strcmp(bef->eref->aliasname,"after") == 0 && bef->rtekind == RTE_BEFORE ) + { + after_index = index_rel; + break; + } + index_rel++; + } /* * We can perform the desired Var fixup by abusing the fix_join_expr * machinery that formerly handled inner indexscan fixup. We search the @@ -1924,6 +1977,7 @@ set_returning_clause_references(PlannerInfo *root, resultRelation, rtoffset); + fix_varno_varattno(rlist, after_index); pfree(itlist); return rlist; diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 5284293..9a50987 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -103,6 +103,7 @@ static void substitute_multiple_relids(Node *node, static void fix_append_rel_relids(List *append_rel_list, int varno, Relids subrelids); static Node *find_jointree_node_for_rel(Node *jtnode, int relid); +static void prepare_returning_before(PlannerInfo *root, List *ret, int varno); /* @@ -648,6 +649,9 @@ pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode, int varno = ((RangeTblRef *) jtnode)->rtindex; RangeTblEntry *rte = rt_fetch(varno, root->parse->rtable); + if (rte->rtekind == RTE_BEFORE) + return NULL; + /* * Is this a subquery RTE, and if so, is the subquery simple enough to * pull up? @@ -753,6 +757,35 @@ pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode, return jtnode; } +void prepare_returning_before(PlannerInfo *root, List *ret, int varno) +{ + ListCell *v; + Var *var; + List *rtable = root->parse->rtable; + RangeTblEntry *rte; + TargetEntry *target; + foreach(v,ret) + { + target = (TargetEntry*)lfirst(v); + if(IsA(target,TargetEntry)) + { + var = (Var*)target->expr; + if(IsA(var,Var)) + { + if (var->varno <= list_length(rtable)) + { + rte = (RangeTblEntry*)list_nth(rtable,var->varno-1); + if(rte->rtekind == RTE_BEFORE) + { + var->varno=varno; + } + } + + } + } + } +} + /* * pull_up_simple_subquery * Attempt to pull up a single simple subquery. @@ -912,6 +945,8 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, */ parse->targetList = (List *) pullup_replace_vars((Node *) parse->targetList, &rvcontext); + + prepare_returning_before(root,parse->returningList,varno); parse->returningList = (List *) pullup_replace_vars((Node *) parse->returningList, &rvcontext); replace_vars_in_jointree((Node *) parse->jointree, &rvcontext, @@ -980,6 +1015,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, case RTE_RELATION: case RTE_JOIN: case RTE_CTE: + case RTE_BEFORE: /* these can't contain any lateral references */ break; } @@ -1513,6 +1549,7 @@ replace_vars_in_jointree(Node *jtnode, case RTE_RELATION: case RTE_JOIN: case RTE_CTE: + case RTE_BEFORE: /* these shouldn't be marked LATERAL */ Assert(false); break; @@ -1666,6 +1703,14 @@ pullup_replace_vars_callback(Var *var, /* Make a copy of the tlist item to return */ newnode = copyObject(tle->expr); + if(IsA(newnode,Var) && rcon->root->parse->commandType == CMD_UPDATE){ + RangeTblEntry *rte = (RangeTblEntry*)list_nth(rcon->root->parse->rtable, ((Var*)var)->varnoold-1); + if(rte->rtekind == RTE_BEFORE){ + ((Var*)newnode)->varoattno = ((Var*)var)->varoattno; + ((Var*)newnode)->varnoold = ((Var*)var)->varnoold; + } + } + /* Insert PlaceHolderVar if needed */ if (rcon->need_phvs) { diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index 8ee5671..28c5ff7 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -135,6 +135,8 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptKind reloptkind) /* Table --- retrieve statistics from the system catalogs */ get_relation_info(root, rte->relid, rte->inh, rel); break; + case RTE_BEFORE: + break; case RTE_SUBQUERY: case RTE_FUNCTION: case RTE_VALUES: diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c index 7eaf8d2..7d686bf 100644 --- a/src/backend/optimizer/util/var.c +++ b/src/backend/optimizer/util/var.c @@ -688,6 +688,16 @@ flatten_join_alias_vars_mutator(Node *node, Assert(var->varattno > 0); newvar = (Node *) list_nth(rte->joinaliasvars, var->varattno - 1); newvar = copyObject(newvar); + if(IsA(newvar,Var) && context->root->parse->commandType == CMD_UPDATE){ + if(var->varno <= list_length(context->root->parse->rtable)){ + RangeTblEntry *rt = rt_fetch(var->varno, context->root->parse->rtable); + if(rt->rtekind == RTE_BEFORE) + { + ((Var*)newvar)->varoattno = ((Var*)var)->varoattno; + ((Var*)newvar)->varnoold = ((Var*)var)->varnoold; + } + } + } /* * If we are expanding an alias carried down from an upper query, must diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 16ff234..fd51a0e 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -2003,6 +2003,9 @@ transformReturningList(ParseState *pstate, List *returningList) save_next_resno = pstate->p_next_resno; pstate->p_next_resno = 1; + if (pstate->p_is_update) + addAliases(pstate); + /* transform RETURNING identically to a SELECT targetlist */ rlist = transformTargetList(pstate, returningList, EXPR_KIND_RETURNING); diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index cbfb431..bcc3f91 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -85,7 +85,54 @@ static WindowClause *findWindowClause(List *wclist, const char *name); static Node *transformFrameOffset(ParseState *pstate, int frameOptions, Node *clause); +extern void addAliases(ParseState *pstate); +void addAliases(ParseState *pstate){ + const int noal = 2; + char *aliases[] = {"before","after"}; + int i; + ListCell *l; + ParseNamespaceItem *nsitem; + RangeTblEntry *rte = NULL; + + foreach(l, pstate->p_namespace) + { + nsitem = (ParseNamespaceItem *) lfirst(l); + rte = nsitem->p_rte; + + /* Ignore columns-only items */ + if (!nsitem->p_rel_visible) + continue; + /* If not inside LATERAL, ignore lateral-only items */ + if (nsitem->p_lateral_only && !pstate->p_lateral_active) + continue; + + for(i=0 ; i<noal; i++){ + if (aliases[i]) + if (strcmp(rte->eref->aliasname, aliases[i]) == 0) + { + aliases[i] = NULL; + } + } + } + + l = pstate->p_namespace->head; + nsitem = (ParseNamespaceItem *) lfirst(l); + + for(i=0 ; i<noal; i++){ + if (aliases[i]) + { + rte = makeNode(RangeTblEntry); + rte->eref = makeAlias(aliases[i], nsitem->p_rte->eref->colnames); + rte->inh = INH_NO; + rte->rtekind = RTE_BEFORE; + rte->relkind = RELKIND_BEFORE; + rte->relid = nsitem->p_rte->relid; + pstate->p_rtable = lappend(pstate->p_rtable, rte); + addRTEtoQuery(pstate, rte, true, true, false); + } + } +} /* * transformFromClause - * Process the FROM clause and add items to the query's range table, diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index a9254c8..e57fccf 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -1658,6 +1658,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up, switch (rte->rtekind) { case RTE_RELATION: + case RTE_BEFORE: /* Ordinary relation RTE */ expandRelation(rte->relid, rte->eref, rtindex, sublevels_up, location, @@ -2113,6 +2114,7 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum, switch (rte->rtekind) { case RTE_RELATION: + case RTE_BEFORE: { /* Plain relation RTE --- get the attribute's type info */ HeapTuple tp; @@ -2273,6 +2275,7 @@ get_rte_attribute_is_dropped(RangeTblEntry *rte, AttrNumber attnum) switch (rte->rtekind) { case RTE_RELATION: + case RTE_BEFORE: { /* * Plain relation RTE --- get the attribute's catalog entry diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index ca20e77..b8e08e6 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -316,6 +316,7 @@ markTargetListOrigin(ParseState *pstate, TargetEntry *tle, break; case RTE_FUNCTION: case RTE_VALUES: + case RTE_BEFORE: /* not a simple relation, leave it unmarked */ break; case RTE_CTE: @@ -1421,6 +1422,7 @@ expandRecordVariable(ParseState *pstate, Var *var, int levelsup) { case RTE_RELATION: case RTE_VALUES: + case RTE_BEFORE: /* * This case should not occur: a column of a table or values list diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index cf9ce3f..da17f98 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5587,6 +5587,7 @@ get_name_for_var_field(Var *var, int fieldno, { case RTE_RELATION: case RTE_VALUES: + case RTE_BEFORE: /* * This case should not occur: a column of a table or values list diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index 49c4f6f..1b09994 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -154,6 +154,7 @@ DESCR(""); #define RELKIND_COMPOSITE_TYPE 'c' /* composite type */ #define RELKIND_FOREIGN_TABLE 'f' /* foreign table */ #define RELKIND_MATVIEW 'm' /* materialized view */ +#define RELKIND_BEFORE 'b' /* virtual table for before/after statements */ #define RELPERSISTENCE_PERMANENT 'p' /* regular table */ #define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index de22dff..fa5083c 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -698,7 +698,8 @@ typedef enum RTEKind RTE_JOIN, /* join */ RTE_FUNCTION, /* function in FROM */ RTE_VALUES, /* VALUES (<exprlist>), (<exprlist>), ... */ - RTE_CTE /* common table expr (WITH list element) */ + RTE_CTE, /* common table expr (WITH list element) */ + RTE_BEFORE /* for before/after statements */ } RTEKind; typedef struct RangeTblEntry diff --git a/src/include/parser/parse_clause.h b/src/include/parser/parse_clause.h index 9bdb033..67cbbb2 100644 --- a/src/include/parser/parse_clause.h +++ b/src/include/parser/parse_clause.h @@ -44,5 +44,6 @@ extern List *transformDistinctOnClause(ParseState *pstate, List *distinctlist, extern Index assignSortGroupRef(TargetEntry *tle, List *tlist); extern bool targetIsInSortList(TargetEntry *tle, Oid sortop, List *sortList); +extern void addAliases(ParseState *pstate); #endif /* PARSE_CLAUSE_H */
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers