Patch for core and PL/pgSQL to support the
INSERT/UPDATE/DELETE RETURNING syntax in 8.2
INSERT/UPDATE/DELETE seem to work fine in normal operation but there is an error with DELETE RETURNING when used through PL/pgSQL.
Here's an example PL/pgSQL test:
CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL
DEFAULT nextval('test_id_seq'),
test_name VARCHAR(64) NOT NULL,
PRIMARY KEY (test_id));
CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
RETURNS VOID AS $$
DECLARE
current_rec RECORD;
BEGIN
-- Test INSERT RETURNING
INSERT INTO test_tbl (test_name) VALUES (test_nm)
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test UPDATE RETURNING
UPDATE test_tbl SET test_name = 'Uncle Bob'
WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test DELETE RETURNING
DELETE FROM test_tbl WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
-- This DOES NOT WORK
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
RETURN;
END;
$$ LANGUAGE plpgsql;
INSERT/UPDATE/DELETE seem to work fine in normal operation but there is an error with DELETE RETURNING when used through PL/pgSQL.
Here's an example PL/pgSQL test:
CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL
DEFAULT nextval('test_id_seq'),
test_name VARCHAR(64) NOT NULL,
PRIMARY KEY (test_id));
CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
RETURNS VOID AS $$
DECLARE
current_rec RECORD;
BEGIN
-- Test INSERT RETURNING
INSERT INTO test_tbl (test_name) VALUES (test_nm)
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test UPDATE RETURNING
UPDATE test_tbl SET test_name = 'Uncle Bob'
WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test DELETE RETURNING
DELETE FROM test_tbl WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
-- This DOES NOT WORK
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
RETURN;
END;
$$ LANGUAGE plpgsql;
--
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/pl/plpgsql/src/gram.y pgsql-iudret/src/pl/plpgsql/src/gram.y
*** pgsql/src/pl/plpgsql/src/gram.y 2006-02-12 01:37:05.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/gram.y 2006-03-02 17:08:47.000000000 -0500
***************
*** 50,55 ****
--- 50,56 ----
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
static PLpgSQL_stmt *make_select_stmt(void);
+ static PLpgSQL_stmt *make_returning_stmt(char *command);
static PLpgSQL_stmt *make_fetch_stmt(void);
static void check_assignable(PLpgSQL_datum *datum);
static PLpgSQL_row *read_into_scalar_list(const char *initial_name,
***************
*** 141,146 ****
--- 142,148 ----
%type <stmt> stmt_for stmt_select stmt_perform
%type <stmt> stmt_dynexecute stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
+ %type <stmt> stmt_insert stmt_update stmt_delete
%type <list> proc_exceptions
%type <exception_block> exception_sect
***************
*** 170,175 ****
--- 172,178 ----
%token K_DEBUG
%token K_DECLARE
%token K_DEFAULT
+ %token K_DELETE
%token K_DIAGNOSTICS
%token K_DOTDOT
%token K_ELSE
***************
*** 185,190 ****
--- 188,194 ----
%token K_IF
%token K_IN
%token K_INFO
+ %token K_INSERT
%token K_INTO
%token K_IS
%token K_LOG
***************
*** 201,212 ****
--- 205,218 ----
%token K_RENAME
%token K_RESULT_OID
%token K_RETURN
+ %token K_RETURNING
%token K_RETURN_NEXT
%token K_REVERSE
%token K_SELECT
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_UPDATE
%token K_WARNING
%token K_WHEN
%token K_WHILE
***************
*** 605,610 ****
--- 611,622 ----
{ $$ = $1; }
| stmt_select
{ $$ = $1; }
+ | stmt_insert
+ { $$ = $1; }
+ | stmt_update
+ { $$ = $1; }
+ | stmt_delete
+ { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
***************
*** 1119,1124 ****
--- 1131,1157 ----
}
;
+ stmt_insert : K_INSERT lno
+ {
+ $$ = make_returning_stmt("INSERT");
+ $$->lineno = $2;
+ }
+ ;
+
+ stmt_update : K_UPDATE lno
+ {
+ $$ = make_returning_stmt("UPDATE");
+ $$->lineno = $2;
+ }
+ ;
+
+ stmt_delete : K_DELETE lno
+ {
+ $$ = make_returning_stmt("DELETE");
+ $$->lineno = $2;
+ }
+ ;
+
stmt_exit : exit_type lno opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
***************
*** 2077,2087 ****
--- 2110,2260 ----
execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
execsql->sqlstmt = expr;
+ execsql->rec = rec;
+ execsql->row = row;
return (PLpgSQL_stmt *)execsql;
}
}
+ static PLpgSQL_stmt *
+ make_returning_stmt(char *command)
+ {
+ PLpgSQL_dstring ds;
+ int nparams = 0;
+ int params[1024];
+ char buf[32];
+ PLpgSQL_expr *expr;
+ PLpgSQL_row *row = NULL;
+ PLpgSQL_rec *rec = NULL;
+ int tok;
+ bool have_returning = false;
+ bool have_into = false;
+ PLpgSQL_stmt_execsql *execsql;
+
+ plpgsql_dstring_init(&ds);
+ plpgsql_dstring_append(&ds, command);
+ plpgsql_dstring_append(&ds, " ");
+
+ while (1)
+ {
+ tok = yylex();
+
+ if (tok == ';')
+ break;
+ if (tok == 0)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unexpected end of function definition")));
+ }
+ if (tok == K_RETURNING)
+ {
+ if (have_returning)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("RETURNING specified more than once")));
+ }
+ have_returning = true;
+ }
+ if (tok == K_INTO && have_returning)
+ {
+ if (have_into)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("RETURNING INTO specified more than once")));
+ }
+ tok = yylex();
+ switch (tok)
+ {
+ case T_ROW:
+ row = yylval.row;
+ check_assignable((PLpgSQL_datum *) row);
+ have_into = true;
+ break;
+
+ case T_RECORD:
+ rec = yylval.rec;
+ check_assignable((PLpgSQL_datum *) rec);
+ have_into = true;
+ break;
+
+ case T_SCALAR:
+ row = read_into_scalar_list(yytext, yylval.scalar);
+ have_into = true;
+ break;
+
+ default:
+ /* Treat the INTO as non-special */
+ plpgsql_dstring_append(&ds, " INTO ");
+ plpgsql_push_back_token(tok);
+ break;
+ }
+ continue;
+ }
+
+ if (plpgsql_SpaceScanned)
+ plpgsql_dstring_append(&ds, " ");
+
+ /* Check for array overflow */
+ if (nparams >= 1024)
+ {
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("too many parameters specified in SQL statement")));
+ }
+
+ switch (tok)
+ {
+ case T_SCALAR:
+ params[nparams] = yylval.scalar->dno;
+ snprintf(buf, sizeof(buf), " $%d ", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+ break;
+
+ case T_ROW:
+ params[nparams] = yylval.row->rowno;
+ snprintf(buf, sizeof(buf), " $%d ", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+ break;
+
+ case T_RECORD:
+ params[nparams] = yylval.rec->recno;
+ snprintf(buf, sizeof(buf), " $%d ", ++nparams);
+ plpgsql_dstring_append(&ds, buf);
+ break;
+
+ default:
+ plpgsql_dstring_append(&ds, yytext);
+ break;
+ }
+ }
+
+ expr = palloc(sizeof(PLpgSQL_expr) + sizeof(int) * nparams - sizeof(int));
+ expr->dtype = PLPGSQL_DTYPE_EXPR;
+ expr->query = pstrdup(plpgsql_dstring_get(&ds));
+ expr->plan = NULL;
+ expr->nparams = nparams;
+ while(nparams-- > 0)
+ expr->params[nparams] = params[nparams];
+ plpgsql_dstring_free(&ds);
+
+ check_sql_expr(expr->query);
+
+ execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
+ execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
+ execsql->sqlstmt = expr;
+ execsql->rec = rec;
+ execsql->row = row;
+
+ return (PLpgSQL_stmt *)execsql;
+ }
static PLpgSQL_stmt *
make_fetch_stmt(void)
diff -cr pgsql/src/pl/plpgsql/src/pl_exec.c pgsql-iudret/src/pl/plpgsql/src/pl_exec.c
*** pgsql/src/pl/plpgsql/src/pl_exec.c 2006-03-02 00:34:12.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/pl_exec.c 2006-03-02 16:36:36.000000000 -0500
***************
*** 2266,2273 ****
/* ----------
! * exec_stmt_execsql Execute an SQL statement not
! * returning any data.
* ----------
*/
static int
--- 2266,2273 ----
/* ----------
! * exec_stmt_execsql Execute an SQL statement which
! * may return data.
* ----------
*/
static int
***************
*** 2279,2284 ****
--- 2279,2286 ----
char *nulls;
int rc;
PLpgSQL_expr *expr = stmt->sqlstmt;
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
/*
* On the first call for this expression generate the plan
***************
*** 2307,2322 ****
}
/*
* Execute the plan
*/
rc = SPI_execute_plan(expr->plan, values, nulls,
estate->readonly_func, 0);
switch (rc)
{
case SPI_OK_UTILITY:
case SPI_OK_SELINTO:
break;
-
case SPI_OK_INSERT:
case SPI_OK_DELETE:
case SPI_OK_UPDATE:
--- 2309,2343 ----
}
/*
+ * If the user has selected the RETURNING option, we're going to
+ * determine how to return it.
+ */
+ if (stmt->rec != NULL)
+ rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]);
+ else if (stmt->row != NULL)
+ row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]);
+
+ /*
* Execute the plan
*/
rc = SPI_execute_plan(expr->plan, values, nulls,
estate->readonly_func, 0);
+
+ /* Assign to INTO variable */
+ if (rec || row)
+ {
+ if (SPI_processed == 0)
+ exec_move_row(estate, rec, row, NULL, SPI_tuptable->tupdesc);
+ else
+ exec_move_row(estate, rec, row,
+ SPI_tuptable->vals[0], SPI_tuptable->tupdesc);
+ }
+
switch (rc)
{
case SPI_OK_UTILITY:
case SPI_OK_SELINTO:
break;
case SPI_OK_INSERT:
case SPI_OK_DELETE:
case SPI_OK_UPDATE:
***************
*** 2340,2357 ****
expr->query, SPI_result_code_string(rc));
}
! /*
! * Release any result tuples from SPI_execute_plan (probably shouldn't be
! * any)
! */
SPI_freetuptable(SPI_tuptable);
/* Save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
estate->eval_lastoid = SPI_lastoid;
! pfree(values);
! pfree(nulls);
return PLPGSQL_RC_OK;
}
--- 2361,2375 ----
expr->query, SPI_result_code_string(rc));
}
! /* Release any result tuples from SPI_execute_plan */
SPI_freetuptable(SPI_tuptable);
/* Save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
estate->eval_lastoid = SPI_lastoid;
! pfree(values);
! pfree(nulls);
return PLPGSQL_RC_OK;
}
diff -cr pgsql/src/pl/plpgsql/src/plpgsql.h pgsql-iudret/src/pl/plpgsql/src/plpgsql.h
*** pgsql/src/pl/plpgsql/src/plpgsql.h 2006-03-02 00:34:12.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/plpgsql.h 2006-03-02 15:11:56.000000000 -0500
***************
*** 528,533 ****
--- 528,535 ----
{ /* Generic SQL statement to execute */
int cmd_type;
int lineno;
+ PLpgSQL_rec *rec; /* INTO record or row variable */
+ PLpgSQL_row *row;
PLpgSQL_expr *sqlstmt;
} PLpgSQL_stmt_execsql;
diff -cr pgsql/src/pl/plpgsql/src/scan.l pgsql-iudret/src/pl/plpgsql/src/scan.l
*** pgsql/src/pl/plpgsql/src/scan.l 2006-02-27 11:09:50.000000000 -0500
--- pgsql-iudret/src/pl/plpgsql/src/scan.l 2006-03-02 16:24:38.000000000 -0500
***************
*** 144,149 ****
--- 144,150 ----
debug { return K_DEBUG; }
declare { return K_DECLARE; }
default { return K_DEFAULT; }
+ delete { return K_DELETE; }
diagnostics { return K_DIAGNOSTICS; }
else { return K_ELSE; }
elseif { return K_ELSIF; }
***************
*** 159,164 ****
--- 160,166 ----
if { return K_IF; }
in { return K_IN; }
info { return K_INFO; }
+ insert { return K_INSERT; }
into { return K_INTO; }
is { return K_IS; }
log { return K_LOG; }
***************
*** 174,185 ****
--- 176,189 ----
rename { return K_RENAME; }
result_oid { return K_RESULT_OID; }
return { return K_RETURN; }
+ returning { return K_RETURNING; }
reverse { return K_REVERSE; }
row_count { return K_ROW_COUNT; }
select { return K_SELECT; }
then { return K_THEN; }
to { return K_TO; }
type { return K_TYPE; }
+ update { return K_UPDATE; }
warning { return K_WARNING; }
when { return K_WHEN; }
while { return K_WHILE; }
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 6: explain analyze is your friend
