I've noticed problem with "UPDATE ... FROM" statement. Fix in new version.
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/commands/trigger.c b/src/backend/commands/trigger.c
index d86e9ad..fafd311 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2335,7 +2335,7 @@ ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
TupleTableSlot *
ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
ResultRelInfo *relinfo,
- ItemPointer tupleid, TupleTableSlot *slot)
+ ItemPointer tupleid, TupleTableSlot *slot, TupleTableSlot **planSlot)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
HeapTuple slottuple = ExecMaterializeSlot(slot);
@@ -2381,6 +2381,7 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
if (newSlot != NULL)
{
slot = ExecFilterJunk(relinfo->ri_junkFilter, newSlot);
+ *planSlot = newSlot;
slottuple = ExecMaterializeSlot(slot);
newtuple = slottuple;
}
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 15f5dcc..06ebaf3 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -603,7 +603,7 @@ ExecUpdate(ItemPointer tupleid,
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
slot = ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
- tupleid, slot);
+ tupleid, slot, &planSlot);
if (slot == NULL) /* "do nothing" */
return NULL;
@@ -737,6 +737,7 @@ lreplace:;
hufd.xmax);
if (!TupIsNull(epqslot))
{
+ planSlot = epqslot;
*tupleid = hufd.ctid;
slot = ExecFilterJunk(resultRelInfo->ri_junkFilter, epqslot);
tuple = ExecMaterializeSlot(slot);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index a896d76..409b4d1 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1928,6 +1928,7 @@ range_table_walker(List *rtable,
{
case RTE_RELATION:
case RTE_CTE:
+ case RTE_BEFORE:
/* nothing to do */
break;
case RTE_SUBQUERY:
@@ -2642,6 +2643,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 48cd9dc..79b03af 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2366,6 +2366,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 dc9cb3e..2ca29da 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1207,6 +1207,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..aeae8a2 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -174,17 +174,52 @@ 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;
+ Index varno = var->varno;
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)
+ {
+ if(strcmp(rte->eref->aliasname,"after") == 0)
+ {
+ RangeTblEntry *rte_b;
+ ListCell *rte_c;
+ varno = 1;
+ foreach(rte_c, root->parse->rtable)
+ {
+ rte_b = (RangeTblEntry *)lfirst(rte_c);
+ if(rte_b->rtekind == RTE_RELATION && rte_b->relid == rte->relid)
+ {
+ break;
+ }
+ varno++;
+ }
+ }
+ else
+ {
+ Var *var2 = copyObject(var);
+ var2->varno = varno;
+ rel->reltargetlist = lappend(rel->reltargetlist,
+ var2);
+ continue;
+ }
+ }
+ }
+ rel = find_base_rel(root, varno);
Assert(attno >= rel->min_attr && attno <= rel->max_attr);
attno -= rel->min_attr;
if (rel->attr_needed[attno] == NULL)
{
/* Variable not yet requested, so add to reltargetlist */
/* XXX is copyObject necessary here? */
+ Var *var2 = copyObject(var);
+ var2->varno = varno;
rel->reltargetlist = lappend(rel->reltargetlist,
- copyObject(var));
+ var2);
}
rel->attr_needed[attno] = bms_add_members(rel->attr_needed[attno],
where_needed);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 01e2fa3..65e4a75 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2015,6 +2015,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);
@@ -2054,6 +2057,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..ef95791 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,27 @@ 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_r;
+ ListCell *rte_c;
+ rte_a = (RangeTblEntry *)list_nth(context->root->parse->rtable,var->varno-1);
+ if (rte_a->rtekind == RTE_BEFORE && strcmp(rte_a->eref->aliasname,"after") == 0)
+ {
+ var->varno = 1;
+ foreach(rte_c, context->root->parse->rtable)
+ {
+ rte_r = (RangeTblEntry *)lfirst(rte_c);
+ if(rte_r->rtekind == RTE_RELATION && rte_r->relid == rte_a->relid)
+ {
+ break;
+ }
+ var->varno++;
+ }
+ }
+ }
/* First look for the var in the input tlists */
newvar = search_indexed_tlist_for_var(var,
@@ -1892,6 +1914,43 @@ 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 +1959,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 +2000,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..8c452ad 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,19 @@ 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)
+ {
+ if(var->varno <= list_length(rcon->root->parse->rtable))
+ {
+ RangeTblEntry *rte = rt_fetch(((Var*)var)->varnoold, rcon->root->parse->rtable);
+ 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..5da4e78 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:
@@ -503,6 +505,12 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
(int) nodeTag(var));
/* Get the Var's original base rel */
+ {
+ RangeTblEntry *rte;
+ rte = ((RangeTblEntry *) list_nth(root->parse->rtable, (var->varno)-1));
+ if(rte->rtekind == RTE_BEFORE)
+ continue;
+ }
baserel = find_base_rel(root, var->varno);
/* Is it still needed above this joinrel? */
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 7eaf8d2..97fb970 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -688,6 +688,18 @@ 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..2297045 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -85,7 +85,57 @@ 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 40b565a..da6e15d 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/commands/trigger.h b/src/include/commands/trigger.h
index 411a66d..e4be684 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -162,7 +162,8 @@ extern TupleTableSlot *ExecBRUpdateTriggers(EState *estate,
EPQState *epqstate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
- TupleTableSlot *slot);
+ TupleTableSlot *slot,
+ TupleTableSlot **planSlot);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9415e2c..9dac4ca 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -701,7 +701,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 */
diff --git a/src/test/regress/expected/returning_before_after.out b/src/test/regress/expected/returning_before_after.out
new file mode 100644
index 0000000..bc85bce
--- /dev/null
+++ b/src/test/regress/expected/returning_before_after.out
@@ -0,0 +1,144 @@
+--
+-- Test BEFORE/AFTER feature in RETURNING statements
+CREATE TABLE foo (
+ bar1 INTEGER,
+ bar2 TEXT
+ );
+INSERT INTO foo VALUES (1, 'x'),(2,'y');
+UPDATE foo SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+------
+ 1 | x | 2 | x
+ 2 | y | 3 | y
+(2 rows)
+
+UPDATE foo SET bar1=bar1-1 RETURNING after.bar1, before.bar1*2;
+ bar1 | ?column?
+------+----------
+ 1 | 4
+ 2 | 6
+(2 rows)
+
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+------
+ 1 | x | 2 | xz
+ 2 | y | 3 | yz
+(2 rows)
+
+-- check single after
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'a' RETURNING after.*;
+ bar1 | bar2
+------+------
+ 3 | xza
+ 4 | yza
+(2 rows)
+
+-- check single before
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'b' RETURNING before.*;
+ bar1 | bar2
+------+------
+ 3 | xza
+ 4 | yza
+(2 rows)
+
+-- it should fail
+UPDATE foo SET bar1=bar1+before.bar1 RETURNING before.*;
+ERROR: missing FROM-clause entry for table "before"
+LINE 1: UPDATE foo SET bar1=bar1+before.bar1 RETURNING before.*;
+ ^
+UPDATE foo SET bar1=bar1+after.bar1 RETURNING after.*;
+ERROR: missing FROM-clause entry for table "after"
+LINE 1: UPDATE foo SET bar1=bar1+after.bar1 RETURNING after.*;
+ ^
+-- test before/after aliases
+UPDATE foo AS before SET bar1=bar1+1 RETURNING before.*,after.*;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+------
+ 5 | xzab | 5 | xzab
+ 6 | yzab | 6 | yzab
+(2 rows)
+
+UPDATE foo AS after SET bar1=bar1-1 RETURNING before.*,after.*;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+------
+ 5 | xzab | 4 | xzab
+ 6 | yzab | 5 | yzab
+(2 rows)
+
+-- test inheritance
+CREATE TABLE foo2 (bar INTEGER) INHERITS(foo);
+INSERT INTO foo2 VALUES (1,'b',5);
+UPDATE foo2 SET bar1=bar1*2, bar=bar1+5, bar2=bar1::text || bar::text RETURNING before.*, after.*, *;
+ bar1 | bar2 | bar | bar1 | bar2 | bar | bar1 | bar2 | bar
+------+------+-----+------+------+-----+------+------+-----
+ 1 | b | 5 | 2 | 15 | 6 | 2 | 15 | 6
+(1 row)
+
+-- check views
+CREATE VIEW view_foo AS SELECT * FROM foo;
+UPDATE foo SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+------
+ 4 | xzab | 5 | xzab
+ 5 | yzab | 6 | yzab
+ 2 | 15 | 3 | 15
+(3 rows)
+
+CREATE TABLE foo3 (bar1 INTEGER, bar4 FLOAT);
+INSERT INTO foo2 VALUES (2, 'asdf', 33);
+INSERT INTO foo3 VALUES (2, 7.77);
+CREATE VIEW view_join AS SELECT f2.*, f3.bar1 AS f1bar1, f3.bar4 FROM foo2 f2
+JOIN foo3 f3 ON f2.bar1 = f3.bar1;
+UPDATE view_join SET bar1=bar1+5, bar2=bar2||'join', bar=bar1*2, bar4=7 RETURNING before.*, after.*;
+ERROR: cannot update view "view_join"
+DETAIL: Views that do not select from a single table or view are not automatically updatable.
+HINT: To make the view updatable, provide an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.
+-- check triggers
+CREATE FUNCTION returning_trig() returns trigger as $$
+BEGIN
+NEW.bar1 = NEW.bar1*NEW.bar1;
+RETURN NEW;
+END; $$ language plpgsql;
+DROP TABLE foo2 CASCADE;
+NOTICE: drop cascades to view view_join
+CREATE TRIGGER bef_foo BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE returning_trig();
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*, *;
+ bar1 | bar2 | bar1 | bar2 | bar1 | bar2
+------+------+------+-------+------+-------
+ 5 | xzab | 6 | xzabz | 36 | xzabz
+ 6 | yzab | 7 | yzabz | 49 | yzabz
+(2 rows)
+
+DROP TABLE foo CASCADE;
+NOTICE: drop cascades to view view_foo
+DROP TABLE foo3 CASCADE;
+CREATE TABLE t1 (id serial, x int, y int, z int);
+CREATE TABLE t2 (id serial, x int, y int, z int);
+INSERT INTO t1 VALUES (DEFAULT,1,2,3);
+INSERT INTO t1 VALUES (DEFAULT,4,5,6);
+-- check WITH statement
+WITH foo AS (UPDATE t1 SET x=x*2, y=y+1, z=x+y+z RETURNING BEFORE.x, BEFORE.y, AFTER.z) INSERT INTO t2 (x,y,z) SELECT x, y, z FROM foo RETURNING *;
+ id | x | y | z
+----+---+---+----
+ 1 | 1 | 2 | 6
+ 2 | 4 | 5 | 15
+(2 rows)
+
+-- check UPDATE ... FROM statement
+UPDATE t2 SET x = t1.x+2 FROM t1 WHERE t2.id=t1.id RETURNING after.x, before.x;
+ x | x
+----+---
+ 4 | 1
+ 10 | 4
+(2 rows)
+
+UPDATE t2 SET x = t1.x*2 FROM t1 WHERE t2.id=t1.id RETURNING after.*, before.*;
+ id | x | y | z | id | x | y | z
+----+----+---+----+----+----+---+----
+ 1 | 4 | 2 | 6 | 1 | 4 | 2 | 6
+ 2 | 16 | 5 | 15 | 2 | 10 | 5 | 15
+(2 rows)
+
+DROP TABLE t1;
+DROP TABLE t2;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index fd08e8d..1169d27 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -107,5 +107,8 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# ----------
test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: returning_before_after
+
# run stats by itself because its delay may be insufficient under heavy load
test: stats
+
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 1ed059b..8aa6243 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -136,6 +136,7 @@ test: sequence
test: polymorphism
test: rowtypes
test: returning
+test: returning_before_after
test: largeobject
test: with
test: xml
diff --git a/src/test/regress/sql/returning_before_after.sql b/src/test/regress/sql/returning_before_after.sql
new file mode 100644
index 0000000..e1b5955
--- /dev/null
+++ b/src/test/regress/sql/returning_before_after.sql
@@ -0,0 +1,85 @@
+--
+-- Test BEFORE/AFTER feature in RETURNING statements
+
+CREATE TABLE foo (
+ bar1 INTEGER,
+ bar2 TEXT
+ );
+
+INSERT INTO foo VALUES (1, 'x'),(2,'y');
+
+UPDATE foo SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+
+UPDATE foo SET bar1=bar1-1 RETURNING after.bar1, before.bar1*2;
+
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*;
+
+-- check single after
+
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'a' RETURNING after.*;
+
+-- check single before
+
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'b' RETURNING before.*;
+
+-- it should fail
+UPDATE foo SET bar1=bar1+before.bar1 RETURNING before.*;
+UPDATE foo SET bar1=bar1+after.bar1 RETURNING after.*;
+
+-- test before/after aliases
+UPDATE foo AS before SET bar1=bar1+1 RETURNING before.*,after.*;
+UPDATE foo AS after SET bar1=bar1-1 RETURNING before.*,after.*;
+
+-- test inheritance
+CREATE TABLE foo2 (bar INTEGER) INHERITS(foo);
+
+INSERT INTO foo2 VALUES (1,'b',5);
+
+UPDATE foo2 SET bar1=bar1*2, bar=bar1+5, bar2=bar1::text || bar::text RETURNING before.*, after.*, *;
+
+-- check views
+
+CREATE VIEW view_foo AS SELECT * FROM foo;
+
+UPDATE foo SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+
+CREATE TABLE foo3 (bar1 INTEGER, bar4 FLOAT);
+
+INSERT INTO foo2 VALUES (2, 'asdf', 33);
+INSERT INTO foo3 VALUES (2, 7.77);
+
+CREATE VIEW view_join AS SELECT f2.*, f3.bar1 AS f1bar1, f3.bar4 FROM foo2 f2
+JOIN foo3 f3 ON f2.bar1 = f3.bar1;
+
+UPDATE view_join SET bar1=bar1+5, bar2=bar2||'join', bar=bar1*2, bar4=7 RETURNING before.*, after.*;
+
+-- check triggers
+CREATE FUNCTION returning_trig() returns trigger as $$
+BEGIN
+NEW.bar1 = NEW.bar1*NEW.bar1;
+RETURN NEW;
+END; $$ language plpgsql;
+
+DROP TABLE foo2 CASCADE;
+CREATE TRIGGER bef_foo BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE returning_trig();
+
+UPDATE foo SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*, *;
+
+DROP TABLE foo CASCADE;
+DROP TABLE foo3 CASCADE;
+
+CREATE TABLE t1 (id serial, x int, y int, z int);
+CREATE TABLE t2 (id serial, x int, y int, z int);
+
+INSERT INTO t1 VALUES (DEFAULT,1,2,3);
+INSERT INTO t1 VALUES (DEFAULT,4,5,6);
+
+-- check WITH statement
+WITH foo AS (UPDATE t1 SET x=x*2, y=y+1, z=x+y+z RETURNING BEFORE.x, BEFORE.y, AFTER.z) INSERT INTO t2 (x,y,z) SELECT x, y, z FROM foo RETURNING *;
+
+-- check UPDATE ... FROM statement
+UPDATE t2 SET x = t1.x+2 FROM t1 WHERE t2.id=t1.id RETURNING after.x, before.x;
+UPDATE t2 SET x = t1.x*2 FROM t1 WHERE t2.id=t1.id RETURNING after.*, before.*;
+
+DROP TABLE t1;
+DROP TABLE t2;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
