On Wed, Aug 21, 2013 at 08:52:25PM +0200, Karol Trzcionka wrote:
> W dniu 21.08.2013 19:17, Boszormenyi Zoltan pisze:
> > With this fixed, a more complete review:
> Thanks.
I've done some syntactic and white space cleanup, here attached.
Karol, would you care to help with commenting the sections that need
same?
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
*** a/doc/src/sgml/ref/update.sgml
--- b/doc/src/sgml/ref/update.sgml
***************
*** 194,205 **** 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.
</para>
</listitem>
</varlistentry>
--- 194,220 ----
<term><replaceable
class="PARAMETER">output_expression</replaceable></term>
<listitem>
<para>
! 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,301 **** 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:
<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;
</programlisting>
</para>
<para>
Use the alternative column-list syntax to do the same update:
<programlisting>
--- 302,317 ----
</para>
<para>
! 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 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>
*** a/src/backend/commands/trigger.c
--- b/src/backend/commands/trigger.c
***************
*** 2335,2341 **** ExecASUpdateTriggers(EState *estate, ResultRelInfo *relinfo)
TupleTableSlot *
ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
ResultRelInfo *relinfo,
! ItemPointer tupleid, TupleTableSlot
*slot)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
HeapTuple slottuple = ExecMaterializeSlot(slot);
--- 2335,2341 ----
TupleTableSlot *
ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
ResultRelInfo *relinfo,
! ItemPointer tupleid, TupleTableSlot
*slot, TupleTableSlot **planSlot)
{
TriggerDesc *trigdesc = relinfo->ri_TrigDesc;
HeapTuple slottuple = ExecMaterializeSlot(slot);
***************
*** 2382,2387 **** ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
--- 2382,2388 ----
if (newSlot != NULL)
{
slot = ExecFilterJunk(relinfo->ri_junkFilter, newSlot);
+ *planSlot = newSlot;
slottuple = ExecMaterializeSlot(slot);
newtuple = slottuple;
}
*** a/src/backend/executor/nodeModifyTable.c
--- b/src/backend/executor/nodeModifyTable.c
***************
*** 609,615 **** ExecUpdate(ItemPointer tupleid,
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
slot = ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
!
tupleid, slot);
if (slot == NULL) /* "do nothing" */
return NULL;
--- 609,615 ----
resultRelInfo->ri_TrigDesc->trig_update_before_row)
{
slot = ExecBRUpdateTriggers(estate, epqstate, resultRelInfo,
!
tupleid, slot, &planSlot);
if (slot == NULL) /* "do nothing" */
return NULL;
***************
*** 749,754 **** lreplace:;
--- 749,755 ----
hufd.xmax);
if (!TupIsNull(epqslot))
{
+ planSlot = epqslot;
*tupleid = hufd.ctid;
slot =
ExecFilterJunk(resultRelInfo->ri_junkFilter, epqslot);
tuple =
ExecMaterializeSlot(slot);
*** a/src/backend/nodes/nodeFuncs.c
--- b/src/backend/nodes/nodeFuncs.c
***************
*** 1999,2004 **** range_table_walker(List *rtable,
--- 1999,2005 ----
{
case RTE_RELATION:
case RTE_CTE:
+ case RTE_ALIAS:
/* nothing to do */
break;
case RTE_SUBQUERY:
***************
*** 2725,2730 **** range_table_mutator(List *rtable,
--- 2726,2732 ----
{
case RTE_RELATION:
case RTE_CTE:
+ case RTE_ALIAS:
/* we don't bother to copy eref, aliases, etc;
OK? */
break;
case RTE_SUBQUERY:
*** a/src/backend/nodes/outfuncs.c
--- b/src/backend/nodes/outfuncs.c
***************
*** 2370,2375 **** _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
--- 2370,2376 ----
switch (node->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
WRITE_OID_FIELD(relid);
WRITE_CHAR_FIELD(relkind);
break;
*** a/src/backend/nodes/readfuncs.c
--- b/src/backend/nodes/readfuncs.c
***************
*** 1212,1217 **** _readRangeTblEntry(void)
--- 1212,1218 ----
switch (local_node->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
READ_OID_FIELD(relid);
READ_CHAR_FIELD(relkind);
break;
*** a/src/backend/optimizer/plan/initsplan.c
--- b/src/backend/optimizer/plan/initsplan.c
***************
*** 184,191 **** add_vars_to_targetlist(PlannerInfo *root, List *vars,
if (IsA(node, Var))
{
Var *var = (Var *) node;
! RelOptInfo *rel = find_base_rel(root, var->varno);
int attno = var->varattno;
if (bms_is_subset(where_needed, rel->relids))
continue;
--- 184,201 ----
if (IsA(node, Var))
{
Var *var = (Var *) node;
! 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;
*** a/src/backend/optimizer/plan/planner.c
--- b/src/backend/optimizer/plan/planner.c
***************
*** 2164,2169 **** preprocess_rowmarks(PlannerInfo *root)
--- 2164,2172 ----
if (rte->relkind == RELKIND_FOREIGN_TABLE)
continue;
+ if (rte->rtekind == RTE_ALIAS)
+ continue;
+
rels = bms_del_member(rels, rc->rti);
newrc = makeNode(PlanRowMark);
***************
*** 2203,2208 **** preprocess_rowmarks(PlannerInfo *root)
--- 2206,2214 ----
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);
*** a/src/backend/optimizer/plan/setrefs.c
--- b/src/backend/optimizer/plan/setrefs.c
***************
*** 134,139 **** static List *set_returning_clause_references(PlannerInfo *root,
--- 134,140 ----
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);
/*****************************************************************************
***************
*** 1712,1719 **** 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;
return (Node *) var;
}
--- 1713,1721 ----
{
var = copyVar(var);
var->varno += context->rtoffset;
! if (var->varnoold > 0 &&
! ((RangeTblEntry
*)list_nth(context->root->parse->rtable,var->varnoold-1))->rtekind != RTE_ALIAS)
! var->varnoold += context->rtoffset;
return (Node *) var;
}
***************
*** 1863,1868 **** fix_upper_expr_mutator(Node *node, fix_upper_expr_context
*context)
--- 1865,1913 ----
}
/*
+ * bind_returning_variables
+ * Fix description of BEFORE. and AFTER. variables
+ *
+ * This replaces each variable generated by parser for "BEFORE." and
+ * "AFTER." statements. It binds var to the 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) && (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
*
***************
*** 1898,1904 **** set_returning_clause_references(PlannerInfo *root,
--- 1943,1965 ----
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
***************
*** 1922,1927 **** set_returning_clause_references(PlannerInfo *root,
--- 1983,1989 ----
resultRelation,
rtoffset);
+ bind_returning_variables(rlist, before_index, after_index);
pfree(itlist);
return rlist;
*** a/src/backend/optimizer/prep/prepjointree.c
--- b/src/backend/optimizer/prep/prepjointree.c
***************
*** 649,654 **** pull_up_subqueries_recurse(PlannerInfo *root, Node *jtnode,
--- 649,657 ----
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?
***************
*** 998,1003 **** pull_up_simple_subquery(PlannerInfo *root, Node *jtnode,
RangeTblEntry *rte,
--- 1001,1007 ----
case RTE_RELATION:
case RTE_JOIN:
case RTE_CTE:
+ case RTE_ALIAS:
/* these can't contain any lateral
references */
break;
}
***************
*** 1644,1649 **** replace_vars_in_jointree(Node *jtnode,
--- 1648,1654 ----
case RTE_RELATION:
case RTE_JOIN:
case RTE_CTE:
+ case RTE_ALIAS:
/* these shouldn't be marked
LATERAL */
Assert(false);
break;
***************
*** 1797,1802 **** pullup_replace_vars_callback(Var *var,
--- 1802,1818 ----
/* 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)
{
*** a/src/backend/optimizer/prep/preptlist.c
--- b/src/backend/optimizer/prep/preptlist.c
***************
*** 165,170 **** preprocess_targetlist(PlannerInfo *root, List *tlist)
--- 165,184 ----
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 */
*** a/src/backend/optimizer/util/relnode.c
--- b/src/backend/optimizer/util/relnode.c
***************
*** 136,141 **** build_simple_rel(PlannerInfo *root, int relid, RelOptKind
reloptkind)
--- 136,143 ----
/* 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,492 **** build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
--- 489,495 ----
Var *var = (Var *) lfirst(vars);
RelOptInfo *baserel;
int ndx;
+ RangeTblEntry *rte;
/*
* Ignore PlaceHolderVars in the input tlists; we'll make our
own
***************
*** 504,509 **** build_joinrel_tlist(PlannerInfo *root, RelOptInfo *joinrel,
--- 507,516 ----
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);
*** a/src/backend/optimizer/util/var.c
--- b/src/backend/optimizer/util/var.c
***************
*** 697,702 **** flatten_join_alias_vars_mutator(Node *node,
--- 697,712 ----
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
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 2040,2045 **** transformReturningList(ParseState *pstate, List
*returningList)
--- 2040,2048 ----
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);
*** a/src/backend/parser/parse_clause.c
--- b/src/backend/parser/parse_clause.c
***************
*** 82,88 **** static WindowClause *findWindowClause(List *wclist, const char
*name);
--- 82,134 ----
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,
*** a/src/backend/parser/parse_relation.c
--- b/src/backend/parser/parse_relation.c
***************
*** 1806,1811 **** expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
--- 1806,1812 ----
switch (rte->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
/* Ordinary relation RTE */
expandRelation(rte->relid, rte->eref,
rtindex, sublevels_up,
location,
***************
*** 2335,2340 **** get_rte_attribute_type(RangeTblEntry *rte, AttrNumber attnum,
--- 2336,2342 ----
switch (rte->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
{
/* Plain relation RTE --- get the attribute's
type info */
HeapTuple tp;
***************
*** 2527,2532 **** get_rte_attribute_is_dropped(RangeTblEntry *rte, AttrNumber
attnum)
--- 2529,2535 ----
switch (rte->rtekind)
{
case RTE_RELATION:
+ case RTE_ALIAS:
{
/*
* Plain relation RTE --- get the attribute's
catalog entry
*** a/src/backend/parser/parse_target.c
--- b/src/backend/parser/parse_target.c
***************
*** 317,322 **** markTargetListOrigin(ParseState *pstate, TargetEntry *tle,
--- 317,323 ----
break;
case RTE_FUNCTION:
case RTE_VALUES:
+ case RTE_ALIAS:
/* not a simple relation, leave it unmarked */
break;
case RTE_CTE:
***************
*** 1424,1429 **** expandRecordVariable(ParseState *pstate, Var *var, int
levelsup)
--- 1425,1431 ----
{
case RTE_RELATION:
case RTE_VALUES:
+ case RTE_ALIAS:
/*
* This case should not occur: a column of a table or
values list
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 5709,5714 **** get_name_for_var_field(Var *var, int fieldno,
--- 5709,5715 ----
{
case RTE_RELATION:
case RTE_VALUES:
+ case RTE_ALIAS:
/*
* This case should not occur: a column of a table or
values list
*** a/src/include/commands/trigger.h
--- b/src/include/commands/trigger.h
***************
*** 162,168 **** extern TupleTableSlot *ExecBRUpdateTriggers(EState *estate,
EPQState *epqstate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
! TupleTableSlot *slot);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
--- 162,169 ----
EPQState *epqstate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
! TupleTableSlot *slot,
! TupleTableSlot **planSlot);
extern void ExecARUpdateTriggers(EState *estate,
ResultRelInfo *relinfo,
ItemPointer tupleid,
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 715,721 **** typedef enum RTEKind
RTE_JOIN, /* join */
RTE_FUNCTION, /* function in FROM */
RTE_VALUES, /* VALUES (<exprlist>),
(<exprlist>), ... */
! RTE_CTE /* common table expr
(WITH list element) */
} RTEKind;
typedef struct RangeTblEntry
--- 715,722 ----
RTE_JOIN, /* join */
RTE_FUNCTION, /* function in FROM */
RTE_VALUES, /* VALUES (<exprlist>),
(<exprlist>), ... */
! RTE_CTE, /* common table expr
(WITH list element) */
! RTE_ALIAS /* for before/after
statements */
} RTEKind;
typedef struct RangeTblEntry
*** a/src/include/parser/parse_clause.h
--- b/src/include/parser/parse_clause.h
***************
*** 47,51 **** extern List *addTargetToSortList(ParseState *pstate, TargetEntry
*tle,
--- 47,52 ----
bool resolveUnknown);
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 */
*** /dev/null
--- b/src/test/regress/expected/returning_before_after.out
***************
*** 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;
*** a/src/test/regress/parallel_schedule
--- b/src/test/regress/parallel_schedule
***************
*** 105,111 **** 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
# run stats by itself because its delay may be insufficient under heavy load
test: stats
--- 105,111 ----
# 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 returning_before_after
# run stats by itself because its delay may be insufficient under heavy load
test: stats
*** a/src/test/regress/serial_schedule
--- b/src/test/regress/serial_schedule
***************
*** 138,143 **** test: sequence
--- 138,144 ----
test: polymorphism
test: rowtypes
test: returning
+ test: returning_before_after
test: largeobject
test: with
test: xml
*** /dev/null
--- b/src/test/regress/sql/returning_before_after.sql
***************
*** 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
