W dniu 21.08.2013 19:17, Boszormenyi Zoltan pisze:
> With this fixed, a more complete review:
Thanks.
> There is a new regression test (returning_before_after.sql) covering
> this feature. However, I think it should be added to the group
> where "returning.sql" resides currently. There is a value in running it
> in parallel to other tests. Sometimes a subtle bug is uncovered
> because of this and your v2 patch fixed such a bug already.
Modified to work correct in parallel testing
> doc/src/sgml/ref/update.sgml describes this feature.
>
> doc/src/sgml/dml.sgml should also be touched to cover this feature.
I don't think so, there is not any info about returning feature, I think
it shouldn't be part of my patch.
> In the src/test/regress/parallel_schedule contains an extra
> new line at the end, it shouldn't.
Fixed
>
> In b/src/backend/optimizer/plan/setrefs.c:
>
> +static void bind_returning_variables(List *rlist, int bef, int aft);
>
> but later it becomes not public:
>
> + */
> +void bind_returning_variables(List *rlist, int bef, int aft)
> +{
I've change to static in the definition.
>
> +extern void addAliases(ParseState *pstate);
>
> +void addAliases(ParseState *pstate)
>
> This external declaration is not needed since it is already
> in src/include/parser/parse_clause.h
Removed.
>
> In setrefs.c, bind_returning_variables() I would also rename
> the function arguments, so "before" and "after" are spelled out.
> These are not C keywords.
Changed.
> Some assignments, like:
>
> + var=(Var*)tle;
> and
> + int index_rel=1;
>
> in setrefs.c need spaces.
>
> "if()" statements need a space before the "(" and not after.
>
> Add spaces in the {} list in addAliases():
> + char *aliases[] = {"before","after"};
> like this: { "before", "after" }
>
> Spaces are needed here, too:
> + for(i=0 ; i<noal; i++)
>
> This "noal" should be "naliases" or "n_aliases" if you really want
> a variable. I would simply use the constant "2" for the two for()
> loops in addAliases() instead, its purpose is obvious enough.
Added some whitespaces.
> In setrefs.c, bind_returning_variables():
> + Var *var = NULL;
> + foreach(temp, rlist){
> Add an empty line after the declaration block.
Added.
> Other comments:
>
> This #define in pg_class:
>
> 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 */
>
RELKIND_BEFORE removed - it probably left over previous work and/or I
needed it because RTE_BEFORE wasn't available (not included?).
> Speaking of which, RTE_BEFORE is more properly named
> RTE_RETURNING_ALIAS or something like that because it
> covers both "before" and "after". Someone may have a better
> idea for naming this symbol.
Renamed to RTE_ALIAS - similar to addAliases (not addReturningAliases)
> One question, though, about this part:
>
> ----------------------------------------
> @@ -1900,7 +1954,27 @@ set_returning_clause_references(PlannerInfo *root,
> int
> rtoffset)
> {
> indexed_tlist *itlist;
> + int after_index=0, before_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;
> + }
> + if(strcmp(bef->eref->aliasname,"before") == 0 &&
> bef->rtekind == RTE_BEFORE )
> + {
> + before_index = index_rel;
> + }
> + 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 +1998,7 @@ set_returning_clause_references(PlannerInfo *root,
> resultRelation,
> rtoffset);
>
> + bind_returning_variables(rlist, before_index, after_index);
> pfree(itlist);
>
> return rlist;
> ----------------------------------------
>
> Why is it enough to keep the last before_index and after_index values?
> What if there are more than one matching RangeTblEntry for "before"
> and/or for "after"? Is it an error condition or of them should be fixed?
I think it is safe, it is the first and the last index. On each level of
statement there can be (at most) the only one "before" and one "after"
alias.
Regards,
Karol Trzcionka
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 908f397..3485bd7 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1987,6 +1987,7 @@ range_table_walker(List *rtable,
{
case RTE_RELATION:
case RTE_CTE:
+ case RTE_ALIAS:
/* nothing to do */
break;
case RTE_SUBQUERY:
@@ -2701,6 +2702,7 @@ range_table_mutator(List *rtable,
{
case RTE_RELATION:
case RTE_CTE:
+ case RTE_ALIAS:
/* 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 cff4734..09138ff 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2368,6 +2368,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
switch (node->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
WRITE_OID_FIELD(relid);
WRITE_CHAR_FIELD(relkind);
break;
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index aad63e5..7e05db6 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_ALIAS:
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 c5998b9..905b718 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -184,8 +184,18 @@ 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, varno-1));
+ if(rte->rtekind == RTE_ALIAS)
+ continue;
+ }
+ rel = find_base_rel(root, varno);
if (bms_is_subset(where_needed, rel->relids))
continue;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index bcc0d45..66b1658 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2157,6 +2157,9 @@ preprocess_rowmarks(PlannerInfo *root)
if (rte->relkind == RELKIND_FOREIGN_TABLE)
continue;
+ if (rte->rtekind == RTE_ALIAS)
+ continue;
+
rels = bms_del_member(rels, rc->rti);
newrc = makeNode(PlanRowMark);
@@ -2196,6 +2199,9 @@ preprocess_rowmarks(PlannerInfo *root)
if (!bms_is_member(i, rels))
continue;
+ if (rte->rtekind == RTE_ALIAS)
+ 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..6bf510a 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 bind_returning_variables(List *rlist, int before, int after);
/*****************************************************************************
@@ -1715,7 +1716,10 @@ fix_join_expr_mutator(Node *node, fix_join_expr_context *context)
var = copyVar(var);
var->varno += context->rtoffset;
if (var->varnoold > 0)
- var->varnoold += context->rtoffset;
+ {
+ if(((RangeTblEntry *)list_nth(context->root->parse->rtable,var->varnoold-1))->rtekind != RTE_ALIAS)
+ var->varnoold += context->rtoffset;
+ }
return (Node *) var;
}
@@ -1865,6 +1869,58 @@ fix_upper_expr_mutator(Node *node, fix_upper_expr_context *context)
}
/*
+ * bind_returning_variables
+ * Fix description of BEFORE. and AFTER. variables
+ *
+ * It replaces each variable generated by parser for
+ * BEFORE. and AFTER. statements. It binds var to proper
+ * places in slot.
+ *
+ * 'rlist': the RETURNING targetlist to be fixed
+ * 'before': index of RTE_ALIAS "before" in rtable
+ * value 2 in most cases
+ * 'after': index of RTE_ALIAS "after" in rtable
+ * value 3 in most cases
+ */
+static void
+bind_returning_variables(List *rlist, int before, int after)
+{
+ 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 == after || var->varnoold == before)
+ {
+ var->varno = OUTER_VAR;
+ var->varattno = var->varoattno;
+ }
+ }
+ else if (IsA(var, OpExpr))
+ {
+ bind_returning_variables(((OpExpr*)var)->args, before, after);
+ }
+ else if (IsA(var, FuncExpr))
+ {
+ bind_returning_variables(((FuncExpr*)var)->args, before, after);
+ }
+ }
+ }
+}
+
+/*
* set_returning_clause_references
* Perform setrefs.c's work on a RETURNING targetlist
*
@@ -1900,7 +1956,27 @@ set_returning_clause_references(PlannerInfo *root,
int rtoffset)
{
indexed_tlist *itlist;
+ int after_index=0, before_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_ALIAS )
+ {
+ after_index = index_rel;
+ }
+ if (strcmp(bef->eref->aliasname,"before") == 0 && bef->rtekind == RTE_ALIAS )
+ {
+ before_index = index_rel;
+ }
+ 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);
+ bind_returning_variables(rlist, before_index, after_index);
pfree(itlist);
return rlist;
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index c742cc9..aff6538 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -652,6 +652,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_ALIAS)
+ return NULL;
+
/*
* Is this a subquery RTE, and if so, is the subquery simple enough to
* pull up?
@@ -989,6 +992,7 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte,
case RTE_RELATION:
case RTE_JOIN:
case RTE_CTE:
+ case RTE_ALIAS:
/* these can't contain any lateral references */
break;
}
@@ -1635,6 +1639,7 @@ replace_vars_in_jointree(Node *jtnode,
case RTE_RELATION:
case RTE_JOIN:
case RTE_CTE:
+ case RTE_ALIAS:
/* these shouldn't be marked LATERAL */
Assert(false);
break;
@@ -1788,6 +1793,17 @@ 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 &&
+ var->varno <= list_length(rcon->root->parse->rtable) )
+ {
+ RangeTblEntry *rte = rt_fetch(((Var*)var)->varnoold, rcon->root->parse->rtable);
+ if(rte->rtekind == RTE_ALIAS)
+ {
+ ((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/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index fb67f9e..268ae79 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -165,6 +165,20 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
var->varno == result_relation)
continue; /* don't need it */
+ if (command_type == CMD_UPDATE)
+ {
+ RangeTblEntry *rte = ((RangeTblEntry *) list_nth(root->parse->rtable, (var->varno)-1));
+
+ if(rte->rtekind == RTE_ALIAS)
+ {
+ var->varno = result_relation;
+ if(strcmp(rte->eref->aliasname,"before") == 0)
+ var->varoattno = list_length(tlist) + 1;
+ else
+ continue;
+ }
+ }
+
if (tlist_member((Node *) var, tlist))
continue; /* already got it */
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 2ac215f..a298edb 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -136,6 +136,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_ALIAS:
+ break;
case RTE_SUBQUERY:
case RTE_FUNCTION:
case RTE_VALUES:
@@ -487,6 +489,7 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
Var *var = (Var *) lfirst(vars);
RelOptInfo *baserel;
int ndx;
+ RangeTblEntry *rte;
/*
* Ignore PlaceHolderVars in the input tlists; we'll make our own
@@ -504,6 +507,10 @@ build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
elog(ERROR, "unexpected node type in reltargetlist: %d",
(int) nodeTag(var));
+ rte = ((RangeTblEntry *) list_nth(root->parse->rtable, (var->varno)-1));
+ if(rte->rtekind == RTE_ALIAS)
+ continue;
+
/* Get the Var's original base rel */
baserel = find_base_rel(root, var->varno);
diff --git a/src/backend/optimizer/util/var.c b/src/backend/optimizer/util/var.c
index 4a3d5c8..ea9330a 100644
--- a/src/backend/optimizer/util/var.c
+++ b/src/backend/optimizer/util/var.c
@@ -697,6 +697,16 @@ flatten_join_alias_vars_mutator(Node *node,
newvar = (Node *) list_nth(rte->joinaliasvars, var->varattno - 1);
Assert(newvar != NULL);
newvar = copyObject(newvar);
+ if(IsA(newvar,Var) && context->root->parse->commandType == CMD_UPDATE &&
+ var->varno <= list_length(context->root->parse->rtable))
+ {
+ RangeTblEntry *rt = rt_fetch(var->varno, context->root->parse->rtable);
+ if(rt->rtekind == RTE_ALIAS)
+ {
+ ((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 a9d1fec..1e73f12 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -2015,6 +2015,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 ea90e58..e1e1829 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -84,7 +84,55 @@ static WindowClause *findWindowClause(List *wclist, const char *name);
static Node *transformFrameOffset(ParseState *pstate, int frameOptions,
Node *clause);
+void
+addAliases(ParseState *pstate)
+{
+ const int n_aliases = 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 < n_aliases; i++)
+ {
+ if (aliases[i] && strcmp(rte->eref->aliasname, aliases[i]) == 0)
+ {
+ aliases[i] = NULL;
+ }
+ }
+ }
+
+ l = pstate->p_namespace->head;
+ nsitem = (ParseNamespaceItem *) lfirst(l);
+
+ for (i=0 ; i < n_aliases; i++)
+ {
+ if (aliases[i])
+ {
+ rte = makeNode(RangeTblEntry);
+ rte->eref = makeAlias(aliases[i], nsitem->p_rte->eref->colnames);
+ rte->inh = INH_NO;
+ rte->rtekind = RTE_ALIAS;
+ rte->relkind = RELKIND_RELATION;
+ 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 39922d3..1485bdc 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -1710,6 +1710,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
switch (rte->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
/* Ordinary relation RTE */
expandRelation(rte->relid, rte->eref,
rtindex, sublevels_up, location,
@@ -2211,6 +2212,7 @@ get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum,
switch (rte->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
{
/* Plain relation RTE --- get the attribute's type info */
HeapTuple tp;
@@ -2389,6 +2391,7 @@ get_rte_attribute_is_dropped(RangeTblEntry *rte, AttrNumber attnum)
switch (rte->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
{
/*
* 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 9c6c202..3a1f959 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -317,6 +317,7 @@ markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
break;
case RTE_FUNCTION:
case RTE_VALUES:
+ case RTE_ALIAS:
/* not a simple relation, leave it unmarked */
break;
case RTE_CTE:
@@ -1422,6 +1423,7 @@ expandRecordVariable(ParseState *pstate, Var *var, int levelsup)
{
case RTE_RELATION:
case RTE_VALUES:
+ case RTE_ALIAS:
/*
* 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 2b005d6..c3c5519 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5603,6 +5603,7 @@ get_name_for_var_field(Var *var, int fieldno,
{
case RTE_RELATION:
case RTE_VALUES:
+ case RTE_ALIAS:
/*
* This case should not occur: a column of a table or values list
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 51fef68..3c80585 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -707,7 +707,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_ALIAS /* 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..be175b6
--- /dev/null
+++ b/src/test/regress/expected/returning_before_after.out
@@ -0,0 +1,152 @@
+--
+-- Test BEFORE/AFTER feature in RETURNING statements
+CREATE TABLE foo_ret (
+ bar1 INTEGER,
+ bar2 TEXT
+ );
+INSERT INTO foo_ret VALUES (1, 'x'),(2,'y');
+UPDATE foo_ret SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+------
+ 1 | x | 2 | x
+ 2 | y | 3 | y
+(2 rows)
+
+UPDATE foo_ret SET bar1=bar1-1 RETURNING after.bar1, before.bar1*2;
+ bar1 | ?column?
+------+----------
+ 1 | 4
+ 2 | 6
+(2 rows)
+
+UPDATE foo_ret 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_ret SET bar1=bar1+1, bar2=bar2 || 'a' RETURNING after.*;
+ bar1 | bar2
+------+------
+ 3 | xza
+ 4 | yza
+(2 rows)
+
+-- check single before
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'b' RETURNING before.*;
+ bar1 | bar2
+------+------
+ 3 | xza
+ 4 | yza
+(2 rows)
+
+-- it should fail
+UPDATE foo_ret SET bar1=bar1+before.bar1 RETURNING before.*;
+ERROR: missing FROM-clause entry for table "before"
+LINE 1: UPDATE foo_ret SET bar1=bar1+before.bar1 RETURNING before.*;
+ ^
+UPDATE foo_ret SET bar1=bar1+after.bar1 RETURNING after.*;
+ERROR: missing FROM-clause entry for table "after"
+LINE 1: UPDATE foo_ret SET bar1=bar1+after.bar1 RETURNING after.*;
+ ^
+-- test before/after aliases
+UPDATE foo_ret 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_ret 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 foo_ret2_ret (bar INTEGER) INHERITS(foo_ret);
+INSERT INTO foo_ret2_ret VALUES (1,'b',5);
+UPDATE foo_ret2_ret 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)
+
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*;
+ bar1 | bar2 | bar1 | bar2
+------+------+------+-------
+ 4 | xzab | 5 | xzabz
+ 5 | yzab | 6 | yzabz
+ 2 | 15 | 3 | 15z
+(3 rows)
+
+-- check views
+CREATE VIEW view_foo_ret AS SELECT * FROM foo_ret;
+UPDATE view_foo_ret SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+ bar1 | bar2 | bar1 | bar2
+------+-------+------+-------
+ 5 | xzabz | 6 | xzabz
+ 6 | yzabz | 7 | yzabz
+ 3 | 15z | 4 | 15z
+(3 rows)
+
+CREATE TABLE foo_ret3 (bar1 INTEGER, bar4 FLOAT);
+INSERT INTO foo_ret2_ret VALUES (2, 'asdf', 33);
+INSERT INTO foo_ret3 VALUES (2, 7.77);
+CREATE VIEW view_join AS SELECT f2.*, f3.bar1 AS f1bar1, f3.bar4 FROM foo_ret2_ret f2
+JOIN foo_ret3 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 enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
+-- check triggers
+CREATE FUNCTION returning_trig() returns trigger as $$
+BEGIN
+NEW.bar1 = NEW.bar1*NEW.bar1;
+RETURN NEW;
+END; $$ language plpgsql;
+DROP TABLE foo_ret2_ret CASCADE;
+NOTICE: drop cascades to view view_join
+CREATE TRIGGER bef_foo_ret BEFORE UPDATE ON foo_ret FOR EACH ROW EXECUTE PROCEDURE returning_trig();
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*, *;
+ bar1 | bar2 | bar1 | bar2 | bar1 | bar2
+------+-------+------+--------+------+--------
+ 6 | xzabz | 7 | xzabzz | 49 | xzabzz
+ 7 | yzabz | 8 | yzabzz | 64 | yzabzz
+(2 rows)
+
+DROP TABLE foo_ret CASCADE;
+NOTICE: drop cascades to view view_foo_ret
+DROP TABLE foo_ret3 CASCADE;
+CREATE TABLE t1_ret (id serial, x int, y int, z int);
+CREATE TABLE t2_ret (id serial, x int, y int, z int);
+INSERT INTO t1_ret VALUES (DEFAULT,1,2,3);
+INSERT INTO t1_ret VALUES (DEFAULT,4,5,6);
+-- check WITH statement
+WITH foo_ret AS (UPDATE t1_ret SET x=x*2, y=y+1, z=x+y+z RETURNING BEFORE.x, BEFORE.y, AFTER.z) INSERT INTO t2_ret (x,y,z) SELECT x, y, z FROM foo_ret RETURNING *;
+ id | x | y | z
+----+---+---+----
+ 1 | 1 | 2 | 6
+ 2 | 4 | 5 | 15
+(2 rows)
+
+-- check UPDATE ... FROM statement
+UPDATE t2_ret SET x = t1_ret.x+2 FROM t1_ret WHERE t2_ret.id=t1_ret.id RETURNING after.x, before.x;
+ x | x
+----+---
+ 4 | 1
+ 10 | 4
+(2 rows)
+
+UPDATE t2_ret SET x = t1_ret.x*2 FROM t1_ret WHERE t2_ret.id=t1_ret.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_ret;
+DROP TABLE t2_ret;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index fd08e8d..af39d1d 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -105,7 +105,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# NB: temp.sql does a reconnect which transiently uses 2 connections,
# so keep this parallel group to at most 19 tests
# ----------
-test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml 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..193ffd9
--- /dev/null
+++ b/src/test/regress/sql/returning_before_after.sql
@@ -0,0 +1,86 @@
+--
+-- Test BEFORE/AFTER feature in RETURNING statements
+
+CREATE TABLE foo_ret (
+ bar1 INTEGER,
+ bar2 TEXT
+ );
+
+INSERT INTO foo_ret VALUES (1, 'x'),(2,'y');
+
+UPDATE foo_ret SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+
+UPDATE foo_ret SET bar1=bar1-1 RETURNING after.bar1, before.bar1*2;
+
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*;
+
+-- check single after
+
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'a' RETURNING after.*;
+
+-- check single before
+
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'b' RETURNING before.*;
+
+-- it should fail
+UPDATE foo_ret SET bar1=bar1+before.bar1 RETURNING before.*;
+UPDATE foo_ret SET bar1=bar1+after.bar1 RETURNING after.*;
+
+-- test before/after aliases
+UPDATE foo_ret AS before SET bar1=bar1+1 RETURNING before.*,after.*;
+UPDATE foo_ret AS after SET bar1=bar1-1 RETURNING before.*,after.*;
+
+-- test inheritance
+CREATE TABLE foo_ret2_ret (bar INTEGER) INHERITS(foo_ret);
+
+INSERT INTO foo_ret2_ret VALUES (1,'b',5);
+
+UPDATE foo_ret2_ret SET bar1=bar1*2, bar=bar1+5, bar2=bar1::text || bar::text RETURNING before.*, after.*, *;
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*;
+
+-- check views
+
+CREATE VIEW view_foo_ret AS SELECT * FROM foo_ret;
+
+UPDATE view_foo_ret SET bar1=bar1+1 RETURNING before.*, bar1, bar2;
+
+CREATE TABLE foo_ret3 (bar1 INTEGER, bar4 FLOAT);
+
+INSERT INTO foo_ret2_ret VALUES (2, 'asdf', 33);
+INSERT INTO foo_ret3 VALUES (2, 7.77);
+
+CREATE VIEW view_join AS SELECT f2.*, f3.bar1 AS f1bar1, f3.bar4 FROM foo_ret2_ret f2
+JOIN foo_ret3 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 foo_ret2_ret CASCADE;
+CREATE TRIGGER bef_foo_ret BEFORE UPDATE ON foo_ret FOR EACH ROW EXECUTE PROCEDURE returning_trig();
+
+UPDATE foo_ret SET bar1=bar1+1, bar2=bar2 || 'z' RETURNING before.*, after.*, *;
+
+DROP TABLE foo_ret CASCADE;
+DROP TABLE foo_ret3 CASCADE;
+
+CREATE TABLE t1_ret (id serial, x int, y int, z int);
+CREATE TABLE t2_ret (id serial, x int, y int, z int);
+
+INSERT INTO t1_ret VALUES (DEFAULT,1,2,3);
+INSERT INTO t1_ret VALUES (DEFAULT,4,5,6);
+
+-- check WITH statement
+WITH foo_ret AS (UPDATE t1_ret SET x=x*2, y=y+1, z=x+y+z RETURNING BEFORE.x, BEFORE.y, AFTER.z) INSERT INTO t2_ret (x,y,z) SELECT x, y, z FROM foo_ret RETURNING *;
+
+-- check UPDATE ... FROM statement
+UPDATE t2_ret SET x = t1_ret.x+2 FROM t1_ret WHERE t2_ret.id=t1_ret.id RETURNING after.x, before.x;
+UPDATE t2_ret SET x = t1_ret.x*2 FROM t1_ret WHERE t2_ret.id=t1_ret.id RETURNING after.*, before.*;
+
+DROP TABLE t1_ret;
+DROP TABLE t2_ret;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
