This is only the current patch updated to apply cleanly on cvs... it's not ready for -patches yet as I still haven't spent much time looking through it and testing it. This is just for anyone to play with and find issues.
My focus for the next couple days is on getting INS/UPD/DEL RETURNING ready for 8.2... all comments, suggestions, issues would be appreciated.
--
Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
732.331.1324
diff -cr pgsql/src/backend/access/common/printtup.c pgsql-iudret/src/backend/access/common/printtup.c *** pgsql/src/backend/access/common/printtup.c 2005-11-03 12:11:30.000000000 -0500 --- pgsql-iudret/src/backend/access/common/printtup.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 19,24 **** --- 19,25 ---- #include "access/printtup.h" #include "libpq/libpq.h" #include "libpq/pqformat.h" + #include "executor/executor.h" #include "tcop/pquery.h" #include "utils/lsyscache.h" #include "utils/portal.h" *************** *** 112,117 **** --- 113,120 ---- { DR_printtup *myState = (DR_printtup *) self; Portal portal = myState->portal; + List *returning = ((Query *) linitial(portal->parseTrees))->returning; + bool withReturning = (returning != NIL); if (PG_PROTOCOL_MAJOR(FrontendProtocol) < 3) { *************** *** 136,142 **** SendRowDescriptionMessage(typeinfo, FetchPortalTargetList(portal), portal->formats); ! /* ---------------- * We could set up the derived attr info at this time, but we postpone it * until the first call of printtup, for 2 reasons: --- 139,149 ---- SendRowDescriptionMessage(typeinfo, FetchPortalTargetList(portal), portal->formats); ! else if (withReturning) ! SendRowDescriptionMessage(ExecTypeFromTL(returning, false), ! returning, ! portal->formats); ! /* ---------------- * We could set up the derived attr info at this time, but we postpone it * until the first call of printtup, for 2 reasons: *************** *** 305,311 **** /* * send the attributes of this tuple */ ! for (i = 0; i < natts; ++i) { PrinttupAttrInfo *thisState = myState->myinfo + i; Datum origattr = slot->tts_values[i], --- 312,318 ---- /* * send the attributes of this tuple */ ! for (i = 0; i < natts; i++) { PrinttupAttrInfo *thisState = myState->myinfo + i; Datum origattr = slot->tts_values[i], diff -cr pgsql/src/backend/executor/execMain.c pgsql-iudret/src/backend/executor/execMain.c *** pgsql/src/backend/executor/execMain.c 2006-02-27 23:10:27.000000000 -0500 --- pgsql-iudret/src/backend/executor/execMain.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 77,88 **** static void ExecSelect(TupleTableSlot *slot, DestReceiver *dest, EState *estate); ! static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid, ! EState *estate); ! static void ExecDelete(TupleTableSlot *slot, ItemPointer tupleid, ! EState *estate); ! static void ExecUpdate(TupleTableSlot *slot, ItemPointer tupleid, ! EState *estate); static TupleTableSlot *EvalPlanQualNext(EState *estate); static void EndEvalPlanQual(EState *estate); static void ExecCheckRTEPerms(RangeTblEntry *rte); --- 77,88 ---- static void ExecSelect(TupleTableSlot *slot, DestReceiver *dest, EState *estate); ! static void ExecInsert(TupleTableSlot *slot, DestReceiver *dest, ! ItemPointer tupleid, EState *estate); ! static void ExecDelete(TupleTableSlot *slot, DestReceiver *dest, ! ItemPointer tupleid, EState *estate); ! static void ExecUpdate(TupleTableSlot *slot, DestReceiver *dest, ! ItemPointer tupleid, EState *estate); static TupleTableSlot *EvalPlanQualNext(EState *estate); static void EndEvalPlanQual(EState *estate); static void ExecCheckRTEPerms(RangeTblEntry *rte); *************** *** 151,156 **** --- 151,159 ---- estate->es_snapshot = queryDesc->snapshot; estate->es_crosscheck_snapshot = queryDesc->crosscheck_snapshot; estate->es_instrument = queryDesc->doInstrument; + estate->es_returning = + ExecTransformReturning(queryDesc->parsetree->returning, + estate); /* * Initialize the plan state tree *************** *** 1299,1315 **** break; case CMD_INSERT: ! ExecInsert(slot, tupleid, estate); result = NULL; break; case CMD_DELETE: ! ExecDelete(slot, tupleid, estate); result = NULL; break; case CMD_UPDATE: ! ExecUpdate(slot, tupleid, estate); result = NULL; break; --- 1302,1318 ---- break; case CMD_INSERT: ! ExecInsert(slot, dest, tupleid, estate); result = NULL; break; case CMD_DELETE: ! ExecDelete(slot, dest, tupleid, estate); result = NULL; break; case CMD_UPDATE: ! ExecUpdate(slot, dest, tupleid, estate); result = NULL; break; *************** *** 1408,1413 **** --- 1411,1417 ---- */ static void ExecInsert(TupleTableSlot *slot, + DestReceiver *dest, ItemPointer tupleid, EState *estate) { *************** *** 1475,1480 **** --- 1479,1494 ---- estate->es_snapshot->curcid, true, true); + if (estate->es_returning != NULL) + { + TupleTableSlot *retSlot = ExecReturning(slot, estate); + /* + * send the tuple to the destination + */ + (*dest->receiveSlot) (retSlot, dest); + ExecClearTuple(retSlot); + } + IncrAppended(); (estate->es_processed)++; estate->es_lastoid = newId; *************** *** 1499,1504 **** --- 1513,1519 ---- */ static void ExecDelete(TupleTableSlot *slot, + DestReceiver *dest, ItemPointer tupleid, EState *estate) { *************** *** 1578,1583 **** --- 1593,1613 ---- return; } + if (estate->es_returning != NULL) + { + TupleTableSlot *deletedSlot; + TupleTableSlot *retSlot; + + deletedSlot = ExecGetDeletedSlot(tupleid, estate); + retSlot = ExecReturning(deletedSlot, estate); + /* + * send the tuple to the destination + */ + (*dest->receiveSlot) (retSlot, dest); + ExecClearTuple(retSlot); + ExecClearTuple(deletedSlot); + } + IncrDeleted(); (estate->es_processed)++; *************** *** 1607,1612 **** --- 1637,1643 ---- */ static void ExecUpdate(TupleTableSlot *slot, + DestReceiver *dest, ItemPointer tupleid, EState *estate) { *************** *** 1733,1738 **** --- 1764,1779 ---- return; } + if (estate->es_returning != NULL) + { + TupleTableSlot *retSlot = ExecReturning(slot, estate); + /* + * send the tuple to the destination + */ + (*dest->receiveSlot) (retSlot, dest); + ExecClearTuple(retSlot); + } + IncrReplaced(); (estate->es_processed)++; diff -cr pgsql/src/backend/executor/execUtils.c pgsql-iudret/src/backend/executor/execUtils.c *** pgsql/src/backend/executor/execUtils.c 2006-01-14 17:03:35.000000000 -0500 --- pgsql-iudret/src/backend/executor/execUtils.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 1158,1160 **** --- 1158,1245 ---- MemoryContextSwitchTo(oldcontext); } + + TupleTableSlot * + ExecReturning(TupleTableSlot *slot, + EState *estate) + { + TupleTableSlot *retSlot, + *scanTupleSave; + ExprContext *returningExprContext; + ProjectionInfo *retProject; + + returningExprContext = (ExprContext *) linitial(estate->es_exprcontexts); + + scanTupleSave = returningExprContext->ecxt_scantuple; + returningExprContext->ecxt_scantuple = slot; + + retProject = ExecBuildProjectionInfo(estate->es_returning->retExprs, + returningExprContext, + estate->es_returning->retSlot); + + retSlot = ExecProject(retProject, NULL); + returningExprContext->ecxt_scantuple = scanTupleSave; + return retSlot; + } + + ReturningState * + ExecTransformReturning(List *returning, + EState *estate) + { + ReturningState *retState; + List *retExprs = NIL; + ListCell *retElem; + int i = 1; + + if (returning == NIL) + return NULL; + + retState = palloc(1 * sizeof(ReturningState)); + + foreach (retElem, returning) + { + TargetEntry *tle; + GenericExprState *gstate; + + tle = (TargetEntry *) lfirst(retElem); + tle->resno = i++; + gstate = makeNode(GenericExprState); + gstate->xprstate.expr = (Expr *) tle; + gstate->xprstate.evalfunc = NULL; + gstate->arg = ExecPrepareExpr(tle->expr, estate); + + retExprs = lappend(retExprs, gstate); + } + + retState->retTupleDesc = ExecTypeFromTL(returning, false); + retState->retExprs = retExprs; + retState->retSlot = MakeSingleTupleTableSlot(retState->retTupleDesc); + + return retState; + } + + TupleTableSlot * + ExecGetDeletedSlot(ItemPointer tupleid, + EState *estate) + { + TupleTableSlot *retSlot = NULL; + HeapTupleData retTuple; + Buffer buffer; + + retTuple.t_self = *tupleid; + + if (heap_fetch(estate->es_result_relation_info->ri_RelationDesc, + SnapshotNow, + &retTuple, + &buffer, + false, + NULL)) + { + retSlot = MakeSingleTupleTableSlot(estate->es_result_relation_info->ri_RelationDesc->rd_att); + ExecStoreTuple(&retTuple, retSlot, InvalidBuffer, false); + slot_getallattrs(retSlot); + ReleaseBuffer(buffer); + } + + return retSlot; + } diff -cr pgsql/src/backend/nodes/copyfuncs.c pgsql-iudret/src/backend/nodes/copyfuncs.c *** pgsql/src/backend/nodes/copyfuncs.c 2006-02-18 19:04:26.000000000 -0500 --- pgsql-iudret/src/backend/nodes/copyfuncs.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 1672,1677 **** --- 1672,1678 ---- COPY_SCALAR_FIELD(forUpdate); COPY_SCALAR_FIELD(rowNoWait); COPY_NODE_FIELD(targetList); + COPY_NODE_FIELD(returning); COPY_NODE_FIELD(groupClause); COPY_NODE_FIELD(havingQual); COPY_NODE_FIELD(distinctClause); *************** *** 1693,1698 **** --- 1694,1700 ---- COPY_NODE_FIELD(cols); COPY_NODE_FIELD(targetList); COPY_NODE_FIELD(selectStmt); + COPY_NODE_FIELD(returning); return newnode; } *************** *** 1705,1710 **** --- 1707,1713 ---- COPY_NODE_FIELD(relation); COPY_NODE_FIELD(whereClause); COPY_NODE_FIELD(usingClause); + COPY_NODE_FIELD(returning); return newnode; } *************** *** 1718,1723 **** --- 1721,1727 ---- COPY_NODE_FIELD(targetList); COPY_NODE_FIELD(whereClause); COPY_NODE_FIELD(fromClause); + COPY_NODE_FIELD(returning); return newnode; } diff -cr pgsql/src/backend/nodes/equalfuncs.c pgsql-iudret/src/backend/nodes/equalfuncs.c *** pgsql/src/backend/nodes/equalfuncs.c 2006-02-18 19:04:26.000000000 -0500 --- pgsql-iudret/src/backend/nodes/equalfuncs.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 683,688 **** --- 683,689 ---- COMPARE_SCALAR_FIELD(forUpdate); COMPARE_SCALAR_FIELD(rowNoWait); COMPARE_NODE_FIELD(targetList); + COMPARE_NODE_FIELD(returning); COMPARE_NODE_FIELD(groupClause); COMPARE_NODE_FIELD(havingQual); COMPARE_NODE_FIELD(distinctClause); *************** *** 702,707 **** --- 703,709 ---- COMPARE_NODE_FIELD(cols); COMPARE_NODE_FIELD(targetList); COMPARE_NODE_FIELD(selectStmt); + COMPARE_NODE_FIELD(returning); return true; } *************** *** 712,717 **** --- 714,720 ---- COMPARE_NODE_FIELD(relation); COMPARE_NODE_FIELD(whereClause); COMPARE_NODE_FIELD(usingClause); + COMPARE_NODE_FIELD(returning); return true; } *************** *** 723,728 **** --- 726,732 ---- COMPARE_NODE_FIELD(targetList); COMPARE_NODE_FIELD(whereClause); COMPARE_NODE_FIELD(fromClause); + COMPARE_NODE_FIELD(returning); return true; } diff -cr pgsql/src/backend/nodes/outfuncs.c pgsql-iudret/src/backend/nodes/outfuncs.c *** pgsql/src/backend/nodes/outfuncs.c 2006-02-18 19:04:26.000000000 -0500 --- pgsql-iudret/src/backend/nodes/outfuncs.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 1517,1522 **** --- 1517,1523 ---- WRITE_BOOL_FIELD(forUpdate); WRITE_BOOL_FIELD(rowNoWait); WRITE_NODE_FIELD(targetList); + WRITE_NODE_FIELD(returning); WRITE_NODE_FIELD(groupClause); WRITE_NODE_FIELD(havingQual); WRITE_NODE_FIELD(distinctClause); diff -cr pgsql/src/backend/nodes/readfuncs.c pgsql-iudret/src/backend/nodes/readfuncs.c *** pgsql/src/backend/nodes/readfuncs.c 2006-02-18 19:04:26.000000000 -0500 --- pgsql-iudret/src/backend/nodes/readfuncs.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 151,156 **** --- 151,157 ---- READ_BOOL_FIELD(forUpdate); READ_BOOL_FIELD(rowNoWait); READ_NODE_FIELD(targetList); + READ_NODE_FIELD(returning); READ_NODE_FIELD(groupClause); READ_NODE_FIELD(havingQual); READ_NODE_FIELD(distinctClause); diff -cr pgsql/src/backend/parser/analyze.c pgsql-iudret/src/backend/parser/analyze.c *** pgsql/src/backend/parser/analyze.c 2006-02-18 19:04:26.000000000 -0500 --- pgsql-iudret/src/backend/parser/analyze.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 102,107 **** --- 102,109 ---- static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt); static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt, List **extras_before, List **extras_after); + static List *transformReturningList(ParseState *pstate, RangeVar *relation, List *returning); + static Query *transformIndexStmt(ParseState *pstate, IndexStmt *stmt); static Query *transformRuleStmt(ParseState *query, RuleStmt *stmt, List **extras_before, List **extras_after); *************** *** 486,491 **** --- 488,500 ---- /* fix where clause */ qual = transformWhereClause(pstate, stmt->whereClause, "WHERE"); + /* + * Transform any RETURNING values to form a targetlist. + */ + + qry->returning = transformReturningList(pstate, stmt->relation, + stmt->returning); + /* done building the range table and jointree */ qry->rtable = pstate->p_rtable; qry->jointree = makeFromExpr(pstate->p_joinlist, qual); *************** *** 662,667 **** --- 671,683 ---- } /* + * Transform any RETURNING values to form a targetlist. + */ + + qry->returning = transformReturningList(pstate, stmt->relation, + stmt->returning); + + /* * Now we are done with SELECT-like processing, and can get on with * transforming the target list to match the INSERT target columns. */ *************** *** 721,726 **** --- 737,769 ---- return qry; } + static List * + transformReturningList(ParseState *pstate, RangeVar *relation, List *returning) + { + List *ret = NIL; + RangeTblEntry *retrte; + + if (returning != NIL) + { + /* + * Add the RTE to the pstate if we don't have any already. + * This will usually happen for INSERT. + */ + if (pstate->p_varnamespace == NIL) + { + retrte = addRangeTableEntry(pstate, relation, + makeAlias("*RETURNING*", NIL), + false, false); + addRTEtoQuery(pstate, retrte, false, true, true); + } + + ret = transformTargetList(pstate, returning); + if (ret != NIL) + markTargetListOrigins(pstate, ret); + } + return ret; + } + /* * transformCreateStmt - * transforms the "create table" statement *************** *** 2329,2334 **** --- 2372,2384 ---- qry->targetList = transformTargetList(pstate, stmt->targetList); + /* + * Transform any RETURNING values to form a targetlist. + */ + + qry->returning = transformReturningList(pstate, stmt->relation, + stmt->returning); + qual = transformWhereClause(pstate, stmt->whereClause, "WHERE"); qry->rtable = pstate->p_rtable; diff -cr pgsql/src/backend/parser/gram.y pgsql-iudret/src/backend/parser/gram.y *** pgsql/src/backend/parser/gram.y 2006-02-28 17:37:26.000000000 -0500 --- pgsql-iudret/src/backend/parser/gram.y 2006-03-02 12:07:43.000000000 -0500 *************** *** 257,262 **** --- 257,263 ---- %type <boolean> index_opt_unique opt_verbose opt_full %type <boolean> opt_freeze opt_default opt_recheck %type <defelt> opt_binary opt_oids copy_delimiter + %type <list> opt_returning_list %type <boolean> copy_from opt_hold *************** *** 393,399 **** QUOTE READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME ! REPEATABLE REPLACE RESET RESTART RESTRICT RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE --- 394,400 ---- QUOTE READ REAL REASSIGN RECHECK REFERENCES REINDEX RELATIVE_P RELEASE RENAME ! REPEATABLE REPLACE RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROW ROWS RULE SAVEPOINT SCHEMA SCROLL SECOND_P SECURITY SELECT SEQUENCE *************** *** 5120,5128 **** *****************************************************************************/ InsertStmt: ! INSERT INTO qualified_name insert_rest { $4->relation = $3; $$ = (Node *) $4; } ; --- 5121,5130 ---- *****************************************************************************/ InsertStmt: ! INSERT INTO qualified_name insert_rest opt_returning_list { $4->relation = $3; + $4->returning = $5; $$ = (Node *) $4; } ; *************** *** 5182,5187 **** --- 5184,5192 ---- } ; + opt_returning_list: + RETURNING target_list { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } /***************************************************************************** * *************** *** 5191,5202 **** *****************************************************************************/ DeleteStmt: DELETE_P FROM relation_expr_opt_alias ! using_clause where_clause { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $3; n->usingClause = $4; n->whereClause = $5; $$ = (Node *)n; } ; --- 5196,5208 ---- *****************************************************************************/ DeleteStmt: DELETE_P FROM relation_expr_opt_alias ! using_clause where_clause opt_returning_list { DeleteStmt *n = makeNode(DeleteStmt); n->relation = $3; n->usingClause = $4; n->whereClause = $5; + n->returning = $6; $$ = (Node *)n; } ; *************** *** 5247,5258 **** --- 5253,5266 ---- SET update_target_list from_clause where_clause + opt_returning_list { UpdateStmt *n = makeNode(UpdateStmt); n->relation = $2; n->targetList = $4; n->fromClause = $5; n->whereClause = $6; + n->returning = $7; $$ = (Node *)n; } ; *************** *** 7946,7952 **** } ; - /***************************************************************************** * * Names and constants --- 7954,7959 ---- *************** *** 8561,8566 **** --- 8568,8574 ---- | PLACING | PRIMARY | REFERENCES + | RETURNING | SELECT | SESSION_USER | SOME diff -cr pgsql/src/backend/parser/keywords.c pgsql-iudret/src/backend/parser/keywords.c *** pgsql/src/backend/parser/keywords.c 2005-12-26 23:00:07.000000000 -0500 --- pgsql-iudret/src/backend/parser/keywords.c 2006-03-02 12:07:43.000000000 -0500 *************** *** 282,287 **** --- 282,288 ---- {"reset", RESET}, {"restart", RESTART}, {"restrict", RESTRICT}, + {"returning", RETURNING}, {"returns", RETURNS}, {"revoke", REVOKE}, {"right", RIGHT}, diff -cr pgsql/src/include/executor/executor.h pgsql-iudret/src/include/executor/executor.h *** pgsql/src/include/executor/executor.h 2006-02-27 23:10:28.000000000 -0500 --- pgsql-iudret/src/include/executor/executor.h 2006-03-02 12:07:43.000000000 -0500 *************** *** 280,283 **** --- 280,290 ---- ExprContextCallbackFunction function, Datum arg); + extern TupleTableSlot *ExecReturning(TupleTableSlot *slot, + EState *estate); + extern ReturningState *ExecTransformReturning(List *returning, + EState *estate); + extern TupleTableSlot *ExecGetDeletedSlot(ItemPointer tupleid, + EState *estate); + #endif /* EXECUTOR_H */ diff -cr pgsql/src/include/nodes/execnodes.h pgsql-iudret/src/include/nodes/execnodes.h *** pgsql/src/include/nodes/execnodes.h 2006-02-28 00:48:44.000000000 -0500 --- pgsql-iudret/src/include/nodes/execnodes.h 2006-03-02 12:07:43.000000000 -0500 *************** *** 282,287 **** --- 282,294 ---- JunkFilter *ri_junkFilter; } ResultRelInfo; + typedef struct ReturningState + { + TupleDesc retTupleDesc; + List *retExprs; + TupleTableSlot *retSlot; + } ReturningState; + /* ---------------- * EState information * *************** *** 327,332 **** --- 334,340 ---- bool es_instrument; /* true requests runtime instrumentation */ bool es_select_into; /* true if doing SELECT INTO */ bool es_into_oids; /* true to generate OIDs in SELECT INTO */ + ReturningState *es_returning; /* list of expressions to return */ List *es_exprcontexts; /* List of ExprContexts within EState */ diff -cr pgsql/src/include/nodes/parsenodes.h pgsql-iudret/src/include/nodes/parsenodes.h *** pgsql/src/include/nodes/parsenodes.h 2006-02-18 19:04:27.000000000 -0500 --- pgsql-iudret/src/include/nodes/parsenodes.h 2006-03-02 12:07:43.000000000 -0500 *************** *** 110,115 **** --- 110,117 ---- List *targetList; /* target list (of TargetEntry) */ + List *returning; /* the list of columns to return */ + List *groupClause; /* a list of GroupClause's */ Node *havingQual; /* qualifications applied to groups */ *************** *** 622,627 **** --- 624,630 ---- */ List *targetList; /* the target list (of ResTarget) */ Node *selectStmt; /* the source SELECT */ + List *returning; /* the list of columns to return */ } InsertStmt; /* ---------------------- *************** *** 634,639 **** --- 637,643 ---- RangeVar *relation; /* relation to delete from */ Node *whereClause; /* qualifications */ List *usingClause; /* optional using clause for more tables */ + List *returning; /* the list of columns to return */ } DeleteStmt; /* ---------------------- *************** *** 647,652 **** --- 651,657 ---- List *targetList; /* the target list (of ResTarget) */ Node *whereClause; /* qualifications */ List *fromClause; /* optional from clause for more tables */ + List *returning; /* the list of columns to return */ } UpdateStmt; /* ---------------------- diff -cr pgsql/src/test/regress/expected/insert.out pgsql-iudret/src/test/regress/expected/insert.out *** pgsql/src/test/regress/expected/insert.out 2003-09-25 02:58:06.000000000 -0400 --- pgsql-iudret/src/test/regress/expected/insert.out 2006-03-02 12:07:43.000000000 -0500 *************** *** 8,13 **** --- 8,19 ---- insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); insert into inserttest values (DEFAULT, 5, 'test'); insert into inserttest values (DEFAULT, 7); + insert into inserttest (col2, col3) values (3, DEFAULT) returning col3, col1, col2, col2 * 5, least(col2, col2 * 5); + col3 | col1 | col2 | ?column? | least + ---------+------+------+----------+------- + testing | | 3 | 15 | 3 + (1 row) + select * from inserttest; col1 | col2 | col3 ------+------+--------- *************** *** 15,21 **** | 5 | testing | 5 | test | 7 | testing ! (4 rows) -- -- insert with similar expression / target_list values (all fail) --- 21,28 ---- | 5 | testing | 5 | test | 7 | testing ! | 3 | testing ! (5 rows) -- -- insert with similar expression / target_list values (all fail) *************** *** 35,40 **** | 5 | testing | 5 | test | 7 | testing ! (4 rows) drop table inserttest; --- 42,48 ---- | 5 | testing | 5 | test | 7 | testing ! | 3 | testing ! (5 rows) drop table inserttest; diff -cr pgsql/src/test/regress/expected/join.out pgsql-iudret/src/test/regress/expected/join.out *** pgsql/src/test/regress/expected/join.out 2005-07-22 15:12:02.000000000 -0400 --- pgsql-iudret/src/test/regress/expected/join.out 2006-03-02 12:07:43.000000000 -0500 *************** *** 2184,2186 **** --- 2184,2204 ---- ---+--- (0 rows) + INSERT INTO t3 VALUES (5, 20); + INSERT INTO t3 VALUES (6, 7); + INSERT INTO t3 VALUES (7, 8); + INSERT INTO t3 VALUES (500, 100); + DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y RETURNING t3.y, t3.x, t3.y + t3_other.x AS sum; + y | x | sum + -----+-----+----- + 7 | 6 | 13 + 8 | 7 | 15 + 20 | 5 | 25 + 100 | 500 | 600 + (4 rows) + + SELECT * FROM t3; + x | y + ---+--- + (0 rows) + diff -cr pgsql/src/test/regress/expected/update.out pgsql-iudret/src/test/regress/expected/update.out *** pgsql/src/test/regress/expected/update.out 2006-01-22 00:20:35.000000000 -0500 --- pgsql-iudret/src/test/regress/expected/update.out 2006-03-02 12:07:43.000000000 -0500 *************** *** 47,50 **** --- 47,66 ---- ERROR: invalid reference to FROM-clause entry for table "update_test" HINT: Perhaps you meant to reference the table alias "t". ROLLBACK; + + -- Test UPDATE RETURNING + UPDATE update_test SET a = 5, b = 10 RETURNING b, a, a * 2 + b, greatest(a, b); + b | a | ?column? | greatest + ----+---+----------+---------- + 10 | 5 | 20 | 10 + 10 | 5 | 20 | 10 + (2 rows) + + SELECT * FROM update_test; + a | b + ---+---- + 5 | 10 + 5 | 10 + (2 rows) + DROP TABLE update_test; diff -cr pgsql/src/test/regress/sql/insert.sql pgsql-iudret/src/test/regress/sql/insert.sql *** pgsql/src/test/regress/sql/insert.sql 2002-04-23 22:22:54.000000000 -0400 --- pgsql-iudret/src/test/regress/sql/insert.sql 2006-03-02 12:07:43.000000000 -0500 *************** *** 7,12 **** --- 7,13 ---- insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); insert into inserttest values (DEFAULT, 5, 'test'); insert into inserttest values (DEFAULT, 7); + insert into inserttest (col2, col3) values (3, DEFAULT) returning col3, col1, col2, col2 * 5, least(col2, col2 * 5); select * from inserttest; diff -cr pgsql/src/test/regress/sql/join.sql pgsql-iudret/src/test/regress/sql/join.sql *** pgsql/src/test/regress/sql/join.sql 2005-04-07 11:23:06.000000000 -0400 --- pgsql-iudret/src/test/regress/sql/join.sql 2006-03-02 12:07:43.000000000 -0500 *************** *** 373,375 **** --- 373,383 ---- SELECT * FROM t3; DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y; SELECT * FROM t3; + + INSERT INTO t3 VALUES (5, 20); + INSERT INTO t3 VALUES (6, 7); + INSERT INTO t3 VALUES (7, 8); + INSERT INTO t3 VALUES (500, 100); + + DELETE FROM t3 USING t3 t3_other WHERE t3.x = t3_other.x AND t3.y = t3_other.y RETURNING t3.y, t3.x, t3.y + t3_other.x AS sum; + SELECT * FROM t3; diff -cr pgsql/src/test/regress/sql/update.sql pgsql-iudret/src/test/regress/sql/update.sql *** pgsql/src/test/regress/sql/update.sql 2006-01-22 00:20:35.000000000 -0500 --- pgsql-iudret/src/test/regress/sql/update.sql 2006-03-02 12:07:43.000000000 -0500 *************** *** 32,35 **** --- 32,40 ---- UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10; ROLLBACK; + -- Test UPDATE RETURNING + UPDATE update_test SET a = 5, b = 10 RETURNING b, a, a * 2 + b, greatest(a, b); + + SELECT * FROM update_test; + DROP TABLE update_test;
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings