Updated INSERT/UPDATE RETURNING
Started by Jonah H. Harrisover 19 years ago6 messages
Here's the updated patch with DELETE RETURNING removed. This isn't
really an issue because no one wanted DELETE RETURNING to begin with.
It is important to note that this patch is not yet ready to be
committed. I still need to go through and run some more tests on it
but wanted to put it in the queue again and let ya know I've been
given time to make sure it gets in.
This patch includes:
- Code changes to core
- Code changes to PL/pgSQL
- Preliminary Documentation Updates (need to add to PL/pgSQL docs)
- Preliminary Regression Tests (need to add PL/pgSQL regressions)
There were a couple suggestions for sorta-kewl features like being
able to use INSERT/UPDATE RETURNING in a FOR loop, etc. I may be able
to get those in if people really want it but I looked into it after
Neil mentioned it and IIRC, there are quite a few changes required to
support it.
Suggestions requested.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Attachments:
pg82-iuret-073106.patchapplication/octet-stream; name=pg82-iuret-073106.patchDownload
Only in pgsql-iuret: config.log
Only in pgsql-iuret: config.status
diff -cr pgsql/doc/src/sgml/ref/insert.sgml pgsql-iuret/doc/src/sgml/ref/insert.sgml
*** pgsql/doc/src/sgml/ref/insert.sgml 2005-11-17 17:14:51.000000000 -0500
--- pgsql-iuret/doc/src/sgml/ref/insert.sgml 2006-07-31 21:55:19.000000000 -0400
***************
*** 21,27 ****
<refsynopsisdiv>
<synopsis>
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
</synopsis>
</refsynopsisdiv>
--- 21,27 ----
<refsynopsisdiv>
<synopsis>
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> } [ RETURNING ( * | <replaceable class="PARAMETER">column</replaceable> [, ...] | <replaceable class="PARAMETER">expression</replaceable> ) ]
</synopsis>
</refsynopsisdiv>
***************
*** 56,61 ****
--- 56,69 ----
</para>
<para>
+ The <literal>RETURNING</> clause allows you to return values and
+ expressions from the <literal>INSERT</> statement. This eliminates
+ the requirement of executing a subsequent <literal>SELECT</> statement
+ to retrieve values such as the next generated sequence number in a
+ <literal>DEFAULT</> field.
+ </para>
+
+ <para>
You must have <literal>INSERT</literal> privilege to a table in
order to insert into it. If you use the <replaceable
class="PARAMETER">query</replaceable> clause to insert rows from a
***************
*** 135,142 ****
<title>Outputs</title>
<para>
! On successful completion, an <command>INSERT</> command returns a command
! tag of the form
<screen>
INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
</screen>
--- 143,150 ----
<title>Outputs</title>
<para>
! On successful completion, an <command>INSERT</> command without a <literal>RETURNING</>
! clause will return a command tag of the form
<screen>
INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
</screen>
***************
*** 147,152 ****
--- 155,167 ----
<acronym>OID</acronym> assigned to the inserted row. Otherwise
<replaceable class="parameter">oid</replaceable> is zero.
</para>
+
+ <para>
+ If an <command>INSERT</> command contained a <literal>RETURNING</>
+ clause and the command was successful, the result will be similar to that
+ of a SELECT statement containing the columns and values defined in the
+ <literal>RETURNING</> list.
+ </para>
</refsect1>
<refsect1>
***************
*** 213,218 ****
--- 228,243 ----
VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
</programlisting>
</para>
+
+ <para>
+ Insert a single row into table <literal>distributors</literal> returning
+ the sequence number generated by the <literal>DEFAULT</literal> clause:
+
+ <programlisting>
+ INSERT INTO distributors VALUES
+ (DEFAULT, 'XYZ Widgets') RETURNING did;
+ </programlisting>
+ </para>
</refsect1>
<refsect1>
diff -cr pgsql/doc/src/sgml/ref/update.sgml pgsql-iuret/doc/src/sgml/ref/update.sgml
*** pgsql/doc/src/sgml/ref/update.sgml 2006-03-08 17:59:09.000000000 -0500
--- pgsql-iuret/doc/src/sgml/ref/update.sgml 2006-07-31 22:04:59.000000000 -0400
***************
*** 24,29 ****
--- 24,30 ----
SET <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
[ FROM <replaceable class="PARAMETER">fromlist</replaceable> ]
[ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
+ [ RETURNING ( * | <replaceable class="PARAMETER">column</replaceable> [, ...] | <replaceable class="PARAMETER">expression</replaceable> ) ]
</synopsis>
</refsynopsisdiv>
***************
*** 53,58 ****
--- 54,67 ----
</para>
<para>
+ The <literal>RETURNING</> clause allows you to return values and
+ expressions affected by the <command>UPDATE</command> command.
+ Use of the <literal>RETURNING</literal> clause eliminates the
+ need of performing <literal>SELECT FOR UPDATE</literal> in cases
+ where you need the after-update value such as balance calculation.
+ </para>
+
+ <para>
You must have the <literal>UPDATE</literal> privilege on the table
to update it, as well as the <literal>SELECT</literal>
privilege to any table whose values are read in the
***************
*** 213,218 ****
--- 222,238 ----
</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>
Increment the sales count of the salesperson who manages the
account for Acme Corporation, using the <literal>FROM</literal>
clause syntax:
Only in pgsql-iuret: GNUmakefile
Only in pgsql-iuret: install
Only in pgsql-iuret: patch.log
diff -cr pgsql/src/backend/access/common/printtup.c pgsql-iuret/src/backend/access/common/printtup.c
*** pgsql/src/backend/access/common/printtup.c 2006-07-14 10:52:16.000000000 -0400
--- pgsql-iuret/src/backend/access/common/printtup.c 2006-07-31 14:25:49.000000000 -0400
***************
*** 18,23 ****
--- 18,24 ----
#include "access/printtup.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
+ #include "executor/executor.h"
#include "tcop/pquery.h"
#include "utils/lsyscache.h"
***************
*** 110,115 ****
--- 111,118 ----
{
DR_printtup *myState = (DR_printtup *) self;
Portal portal = myState->portal;
+ List *returningList = ((Query *) linitial(portal->parseTrees))->returningList;
+ bool withReturning = (returningList != NIL);
if (PG_PROTOCOL_MAJOR(FrontendProtocol) < 3)
{
***************
*** 134,140 ****
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:
--- 137,147 ----
SendRowDescriptionMessage(typeinfo,
FetchPortalTargetList(portal),
portal->formats);
! else if (withReturning)
! SendRowDescriptionMessage(ExecTypeFromTL(returningList, false),
! returningList,
! 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:
***************
*** 297,303 ****
/*
* send the attributes of this tuple
*/
! for (i = 0; i < natts; ++i)
{
PrinttupAttrInfo *thisState = myState->myinfo + i;
Datum origattr = slot->tts_values[i],
--- 304,310 ----
/*
* send the attributes of this tuple
*/
! for (i = 0; i < natts; i++)
{
PrinttupAttrInfo *thisState = myState->myinfo + i;
Datum origattr = slot->tts_values[i],
Only in pgsql-iuret/src/backend/bootstrap: bootparse.c
Only in pgsql-iuret/src/backend/bootstrap: bootscanner.c
Only in pgsql-iuret/src/backend/bootstrap: bootstrap_tokens.h
diff -cr pgsql/src/backend/executor/execMain.c pgsql-iuret/src/backend/executor/execMain.c
*** pgsql/src/backend/executor/execMain.c 2006-07-30 21:16:37.000000000 -0400
--- pgsql-iuret/src/backend/executor/execMain.c 2006-07-31 14:24:24.000000000 -0400
***************
*** 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, 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->returningList,
+ estate);
/*
* Initialize the plan state tree
***************
*** 187,192 ****
--- 190,196 ----
DestReceiver *dest;
TupleTableSlot *result;
MemoryContext oldcontext;
+ TupleDesc tupDesc = NULL;
/* sanity checks */
Assert(queryDesc != NULL);
***************
*** 212,218 ****
estate->es_processed = 0;
estate->es_lastoid = InvalidOid;
! (*dest->rStartup) (dest, operation, queryDesc->tupDesc);
/*
* run plan
--- 216,232 ----
estate->es_processed = 0;
estate->es_lastoid = InvalidOid;
! if (estate->es_returning)
! tupDesc = estate->es_returning->retTupleDesc;
! else
! tupDesc = queryDesc->tupDesc;
!
! if(!tupDesc || !OidIsValid(tupDesc->tdtypeid))
! ereport(ERROR,
! (errcode(ERRCODE_UNDEFINED_TABLE),
! errmsg("relation does not exist")));
!
! (*dest->rStartup) (dest, operation, tupDesc);
/*
* run plan
***************
*** 1303,1309 ****
break;
case CMD_INSERT:
! ExecInsert(slot, tupleid, estate);
result = NULL;
break;
--- 1317,1323 ----
break;
case CMD_INSERT:
! ExecInsert(slot, dest, tupleid, estate);
result = NULL;
break;
***************
*** 1313,1319 ****
break;
case CMD_UPDATE:
! ExecUpdate(slot, tupleid, estate);
result = NULL;
break;
--- 1327,1333 ----
break;
case CMD_UPDATE:
! ExecUpdate(slot, dest, tupleid, estate);
result = NULL;
break;
***************
*** 1412,1417 ****
--- 1426,1432 ----
*/
static void
ExecInsert(TupleTableSlot *slot,
+ DestReceiver *dest,
ItemPointer tupleid,
EState *estate)
{
***************
*** 1477,1482 ****
--- 1492,1507 ----
estate->es_snapshot->curcid,
true, true);
+ if (estate->es_returning != NULL)
+ {
+ /*
+ * send the tuple to the destination
+ */
+ TupleTableSlot *retSlot = ExecReturning(slot, estate);
+ (*dest->receiveSlot) (retSlot, dest);
+ ExecClearTuple(retSlot);
+ }
+
IncrAppended();
(estate->es_processed)++;
estate->es_lastoid = newId;
***************
*** 1609,1614 ****
--- 1634,1640 ----
*/
static void
ExecUpdate(TupleTableSlot *slot,
+ DestReceiver *dest,
ItemPointer tupleid,
EState *estate)
{
***************
*** 1733,1738 ****
--- 1759,1774 ----
return;
}
+ if (estate->es_returning != NULL)
+ {
+ /*
+ * send the tuple to the destination
+ */
+ TupleTableSlot *retSlot = ExecReturning(slot, estate);
+ (*dest->receiveSlot) (retSlot, dest);
+ ExecClearTuple(retSlot);
+ }
+
IncrReplaced();
(estate->es_processed)++;
diff -cr pgsql/src/backend/executor/execUtils.c pgsql-iuret/src/backend/executor/execUtils.c
*** pgsql/src/backend/executor/execUtils.c 2006-07-14 10:52:19.000000000 -0400
--- pgsql-iuret/src/backend/executor/execUtils.c 2006-07-31 14:23:22.000000000 -0400
***************
*** 1148,1150 ****
--- 1148,1209 ----
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 *returningList,
+ EState *estate)
+ {
+ ReturningState *retState;
+ List *retExprs = NIL;
+ ListCell *retElem;
+ int i = 1;
+
+ if (returningList == NIL)
+ return NULL;
+
+ retState = palloc(1 * sizeof(ReturningState));
+
+ foreach (retElem, returningList)
+ {
+ 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(returningList, false);
+ retState->retExprs = retExprs;
+ retState->retSlot = MakeSingleTupleTableSlot(retState->retTupleDesc);
+
+ return retState;
+ }
diff -cr pgsql/src/backend/nodes/copyfuncs.c pgsql-iuret/src/backend/nodes/copyfuncs.c
*** pgsql/src/backend/nodes/copyfuncs.c 2006-07-27 15:52:05.000000000 -0400
--- pgsql-iuret/src/backend/nodes/copyfuncs.c 2006-07-31 14:18:14.000000000 -0400
***************
*** 1687,1692 ****
--- 1687,1693 ----
COPY_NODE_FIELD(rtable);
COPY_NODE_FIELD(jointree);
COPY_NODE_FIELD(targetList);
+ COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(groupClause);
COPY_NODE_FIELD(havingQual);
COPY_NODE_FIELD(distinctClause);
***************
*** 1709,1714 ****
--- 1710,1716 ----
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(targetList);
COPY_NODE_FIELD(selectStmt);
+ COPY_NODE_FIELD(returningList);
return newnode;
}
***************
*** 1734,1739 ****
--- 1736,1742 ----
COPY_NODE_FIELD(targetList);
COPY_NODE_FIELD(whereClause);
COPY_NODE_FIELD(fromClause);
+ COPY_NODE_FIELD(returningList);
return newnode;
}
diff -cr pgsql/src/backend/nodes/equalfuncs.c pgsql-iuret/src/backend/nodes/equalfuncs.c
*** pgsql/src/backend/nodes/equalfuncs.c 2006-07-27 15:52:05.000000000 -0400
--- pgsql-iuret/src/backend/nodes/equalfuncs.c 2006-07-31 14:18:36.000000000 -0400
***************
*** 664,669 ****
--- 664,670 ----
COMPARE_NODE_FIELD(rtable);
COMPARE_NODE_FIELD(jointree);
COMPARE_NODE_FIELD(targetList);
+ COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(groupClause);
COMPARE_NODE_FIELD(havingQual);
COMPARE_NODE_FIELD(distinctClause);
***************
*** 684,689 ****
--- 685,691 ----
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(targetList);
COMPARE_NODE_FIELD(selectStmt);
+ COMPARE_NODE_FIELD(returningList);
return true;
}
***************
*** 705,710 ****
--- 707,713 ----
COMPARE_NODE_FIELD(targetList);
COMPARE_NODE_FIELD(whereClause);
COMPARE_NODE_FIELD(fromClause);
+ COMPARE_NODE_FIELD(returningList);
return true;
}
diff -cr pgsql/src/backend/nodes/outfuncs.c pgsql-iuret/src/backend/nodes/outfuncs.c
*** pgsql/src/backend/nodes/outfuncs.c 2006-07-27 15:52:05.000000000 -0400
--- pgsql-iuret/src/backend/nodes/outfuncs.c 2006-07-31 14:18:44.000000000 -0400
***************
*** 1516,1521 ****
--- 1516,1522 ----
WRITE_NODE_FIELD(rtable);
WRITE_NODE_FIELD(jointree);
WRITE_NODE_FIELD(targetList);
+ WRITE_NODE_FIELD(returningList);
WRITE_NODE_FIELD(groupClause);
WRITE_NODE_FIELD(havingQual);
WRITE_NODE_FIELD(distinctClause);
diff -cr pgsql/src/backend/nodes/readfuncs.c pgsql-iuret/src/backend/nodes/readfuncs.c
*** pgsql/src/backend/nodes/readfuncs.c 2006-07-27 15:52:05.000000000 -0400
--- pgsql-iuret/src/backend/nodes/readfuncs.c 2006-07-31 14:18:52.000000000 -0400
***************
*** 148,153 ****
--- 148,154 ----
READ_NODE_FIELD(rtable);
READ_NODE_FIELD(jointree);
READ_NODE_FIELD(targetList);
+ READ_NODE_FIELD(returningList);
READ_NODE_FIELD(groupClause);
READ_NODE_FIELD(havingQual);
READ_NODE_FIELD(distinctClause);
diff -cr pgsql/src/backend/parser/analyze.c pgsql-iuret/src/backend/parser/analyze.c
*** pgsql/src/backend/parser/analyze.c 2006-07-14 10:52:21.000000000 -0400
--- pgsql-iuret/src/backend/parser/analyze.c 2006-07-31 14:22:24.000000000 -0400
***************
*** 98,103 ****
--- 98,105 ----
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 *returningList);
static Query *transformIndexStmt(ParseState *pstate, IndexStmt *stmt);
static Query *transformRuleStmt(ParseState *query, RuleStmt *stmt,
List **extras_before, List **extras_after);
***************
*** 665,670 ****
--- 667,678 ----
}
/*
+ * Transform any RETURNING values to form a targetlist.
+ */
+ qry->returningList = transformReturningList(pstate, stmt->relation,
+ stmt->returningList);
+
+ /*
* Now we are done with SELECT-like processing, and can get on with
* transforming the target list to match the INSERT target columns.
*/
***************
*** 724,729 ****
--- 732,764 ----
return qry;
}
+ static List *
+ transformReturningList(ParseState *pstate, RangeVar *relation, List *returningList)
+ {
+ List *ret = NIL;
+ RangeTblEntry *retrte;
+
+ if (returningList != 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, returningList);
+ if (ret != NIL)
+ markTargetListOrigins(pstate, ret);
+ }
+ return ret;
+ }
+
/*
* transformCreateStmt -
* transforms the "create table" statement
***************
*** 2394,2399 ****
--- 2429,2440 ----
qry->targetList = transformTargetList(pstate, stmt->targetList);
+ /*
+ * Transform any RETURNING values to form a targetlist.
+ */
+ qry->returningList = transformReturningList(pstate, stmt->relation,
+ stmt->returningList);
+
qual = transformWhereClause(pstate, stmt->whereClause, "WHERE");
qry->rtable = pstate->p_rtable;
Only in pgsql-iuret/src/backend/parser: gram.c
diff -cr pgsql/src/backend/parser/gram.y pgsql-iuret/src/backend/parser/gram.y
*** pgsql/src/backend/parser/gram.y 2006-07-30 21:16:37.000000000 -0400
--- pgsql-iuret/src/backend/parser/gram.y 2006-07-31 14:20:37.000000000 -0400
***************
*** 278,283 ****
--- 278,284 ----
%type <ival> opt_column event cursor_options
%type <objtype> reindex_type drop_type comment_type
+ %type <list> opt_returning_list
%type <node> fetch_direction select_limit_value select_offset_value
***************
*** 412,418 ****
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
--- 413,419 ----
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
***************
*** 5334,5342 ****
*****************************************************************************/
InsertStmt:
! INSERT INTO qualified_name insert_rest
{
$4->relation = $3;
$$ = (Node *) $4;
}
;
--- 5335,5344 ----
*****************************************************************************/
InsertStmt:
! INSERT INTO qualified_name insert_rest opt_returning_list
{
$4->relation = $3;
+ $4->returningList = $5;
$$ = (Node *) $4;
}
;
***************
*** 5397,5402 ****
--- 5399,5408 ----
}
;
+ opt_returning_list:
+ RETURNING target_list { $$ = $2; }
+ | /* EMPTY */ { $$ = NIL; }
+ ;
/*****************************************************************************
*
***************
*** 5462,5473 ****
--- 5468,5481 ----
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->returningList = $7;
$$ = (Node *)n;
}
;
***************
*** 8200,8206 ****
}
;
-
/*****************************************************************************
*
* Names and constants
--- 8208,8213 ----
***************
*** 8816,8821 ****
--- 8823,8829 ----
| PLACING
| PRIMARY
| REFERENCES
+ | RETURNING
| SELECT
| SESSION_USER
| SOME
diff -cr pgsql/src/backend/parser/keywords.c pgsql-iuret/src/backend/parser/keywords.c
*** pgsql/src/backend/parser/keywords.c 2006-07-30 21:16:37.000000000 -0400
--- pgsql-iuret/src/backend/parser/keywords.c 2006-07-31 13:01:08.000000000 -0400
***************
*** 284,289 ****
--- 284,290 ----
{"reset", RESET},
{"restart", RESTART},
{"restrict", RESTRICT},
+ {"returning", RETURNING},
{"returns", RETURNS},
{"revoke", REVOKE},
{"right", RIGHT},
Only in pgsql-iuret/src/backend/parser: parse.h
Only in pgsql-iuret/src/backend/parser: scan.c
Only in pgsql-iuret/src/backend/port: dynloader.c
Only in pgsql-iuret/src/backend/port: pg_sema.c
Only in pgsql-iuret/src/backend/port: pg_shmem.c
Only in pgsql-iuret/src/backend/port: tas.s
Only in pgsql-iuret/src/backend/utils/misc: guc-file.c
Only in pgsql-iuret/src/bin/psql: psqlscan.c
Only in pgsql-iuret/src/bin/psql: sql_help.h
Only in pgsql-iuret/src/include: dynloader.h
diff -cr pgsql/src/include/executor/executor.h pgsql-iuret/src/include/executor/executor.h
*** pgsql/src/include/executor/executor.h 2006-06-16 14:42:23.000000000 -0400
--- pgsql-iuret/src/include/executor/executor.h 2006-07-31 13:48:35.000000000 -0400
***************
*** 226,231 ****
--- 226,235 ----
extern ExprContext *CreateExprContext(EState *estate);
extern void FreeExprContext(ExprContext *econtext);
extern void ReScanExprContext(ExprContext *econtext);
+ extern TupleTableSlot *ExecReturning(TupleTableSlot *slot,
+ EState *estate);
+ extern ReturningState *ExecTransformReturning(List *returning,
+ EState *estate);
#define ResetExprContext(econtext) \
MemoryContextReset((econtext)->ecxt_per_tuple_memory)
diff -cr pgsql/src/include/nodes/execnodes.h pgsql-iuret/src/include/nodes/execnodes.h
*** pgsql/src/include/nodes/execnodes.h 2006-07-27 15:52:07.000000000 -0400
--- pgsql-iuret/src/include/nodes/execnodes.h 2006-07-31 14:16:56.000000000 -0400
***************
*** 279,284 ****
--- 279,291 ----
JunkFilter *ri_junkFilter;
} ResultRelInfo;
+ typedef struct ReturningState
+ {
+ TupleDesc retTupleDesc;
+ List *retExprs;
+ TupleTableSlot *retSlot;
+ } ReturningState;
+
/* ----------------
* EState information
*
***************
*** 322,327 ****
--- 329,335 ----
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-iuret/src/include/nodes/parsenodes.h
*** pgsql/src/include/nodes/parsenodes.h 2006-07-30 21:16:38.000000000 -0400
--- pgsql-iuret/src/include/nodes/parsenodes.h 2006-07-31 14:17:41.000000000 -0400
***************
*** 104,109 ****
--- 104,111 ----
List *targetList; /* target list (of TargetEntry) */
+ List *returningList; /* the list of columns to return */
+
List *groupClause; /* a list of GroupClause's */
Node *havingQual; /* qualifications applied to groups */
***************
*** 645,650 ****
--- 647,653 ----
*/
List *targetList; /* the target list (of ResTarget) */
Node *selectStmt; /* the source SELECT */
+ List *returningList; /* the list of columns to return */
} InsertStmt;
/* ----------------------
***************
*** 670,675 ****
--- 673,679 ----
List *targetList; /* the target list (of ResTarget) */
Node *whereClause; /* qualifications */
List *fromClause; /* optional from clause for more tables */
+ List *returningList; /* the list of columns to return */
} UpdateStmt;
/* ----------------------
Only in pgsql-iuret/src/include: pg_config.h
Only in pgsql-iuret/src/include: pg_config_os.h
Only in pgsql-iuret/src/include: stamp-h
Only in pgsql-iuret/src/interfaces/ecpg/preproc: pgc.c
Only in pgsql-iuret/src/interfaces/ecpg/preproc: preproc.c
Only in pgsql-iuret/src/interfaces/ecpg/preproc: preproc.h
Only in pgsql-iuret/src/interfaces/libpq: blibpqdll.def
Only in pgsql-iuret/src/interfaces/libpq: libpqddll.def
Only in pgsql-iuret/src/interfaces/libpq: libpqdll.def
Only in pgsql-iuret/src/interfaces/libpq: libpq.rc
Only in pgsql-iuret/src: Makefile.global
Only in pgsql-iuret/src: Makefile.port
diff -cr pgsql/src/pl/plpgsql/src/gram.y pgsql-iuret/src/pl/plpgsql/src/gram.y
*** pgsql/src/pl/plpgsql/src/gram.y 2006-06-16 19:29:26.000000000 -0400
--- pgsql-iuret/src/pl/plpgsql/src/gram.y 2006-07-31 22:35:32.000000000 -0400
***************
*** 29,34 ****
--- 29,35 ----
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,
***************
*** 122,127 ****
--- 123,129 ----
%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
%type <list> proc_exceptions
%type <exception_block> exception_sect
***************
*** 167,172 ****
--- 169,175 ----
%token K_IF
%token K_IN
%token K_INFO
+ %token K_INSERT
%token K_INTO
%token K_IS
%token K_LOG
***************
*** 184,195 ****
--- 187,200 ----
%token K_RESULT_OID
%token K_RETURN
%token K_RETURN_NEXT
+ %token K_RETURNING
%token K_REVERSE
%token K_SELECT
%token K_STRICT
%token K_THEN
%token K_TO
%token K_TYPE
+ %token K_UPDATE
%token K_WARNING
%token K_WHEN
%token K_WHILE
***************
*** 592,597 ****
--- 597,606 ----
{ $$ = $1; }
| stmt_select
{ $$ = $1; }
+ | stmt_insert
+ { $$ = $1; }
+ | stmt_update
+ { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
***************
*** 1135,1140 ****
--- 1144,1163 ----
}
;
+ stmt_insert : K_INSERT lno
+ {
+ $$ = make_returning_stmt("INSERT");
+ $$->lineno = $2;
+ }
+ ;
+
+ stmt_update : K_UPDATE lno
+ {
+ $$ = make_returning_stmt("UPDATE");
+ $$->lineno = $2;
+ }
+ ;
+
stmt_exit : exit_type lno opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
***************
*** 1358,1363 ****
--- 1381,1388 ----
new->cmd_type = PLPGSQL_STMT_EXECSQL;
new->lineno = $2;
new->sqlstmt = read_sql_stmt($1);
+ new->rec = NULL;
+ new->row = NULL;
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 2126,2136 ****
--- 2151,2301 ----
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-iuret/src/pl/plpgsql/src/pl_exec.c
*** pgsql/src/pl/plpgsql/src/pl_exec.c 2006-07-13 12:49:20.000000000 -0400
--- pgsql-iuret/src/pl/plpgsql/src/pl_exec.c 2006-07-31 13:01:08.000000000 -0400
***************
*** 2296,2303 ****
/* ----------
! * exec_stmt_execsql Execute an SQL statement not
! * returning any data.
* ----------
*/
static int
--- 2296,2303 ----
/* ----------
! * exec_stmt_execsql Execute an SQL statement which
! * may return data.
* ----------
*/
static int
***************
*** 2309,2314 ****
--- 2309,2316 ----
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
***************
*** 2337,2352 ****
}
/*
* 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:
--- 2339,2373 ----
}
/*
+ * 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:
***************
*** 2370,2387 ****
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;
}
--- 2391,2405 ----
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;
}
Only in pgsql-iuret/src/pl/plpgsql/src: pl_gram.c
diff -cr pgsql/src/pl/plpgsql/src/plpgsql.h pgsql-iuret/src/pl/plpgsql/src/plpgsql.h
*** pgsql/src/pl/plpgsql/src/plpgsql.h 2006-07-11 13:26:59.000000000 -0400
--- pgsql-iuret/src/pl/plpgsql/src/plpgsql.h 2006-07-31 13:01:08.000000000 -0400
***************
*** 509,514 ****
--- 509,516 ----
{ /* 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;
Only in pgsql-iuret/src/pl/plpgsql/src: pl_scan.c
Only in pgsql-iuret/src/pl/plpgsql/src: pl.tab.h
diff -cr pgsql/src/pl/plpgsql/src/scan.l pgsql-iuret/src/pl/plpgsql/src/scan.l
*** pgsql/src/pl/plpgsql/src/scan.l 2006-06-16 19:29:27.000000000 -0400
--- pgsql-iuret/src/pl/plpgsql/src/scan.l 2006-07-31 14:41:24.000000000 -0400
***************
*** 139,144 ****
--- 139,145 ----
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; }
***************
*** 154,159 ****
--- 155,161 ----
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; }
***************
*** 161,166 ****
--- 163,169 ----
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-iuret/src/test/regress/expected/insert.out
*** pgsql/src/test/regress/expected/insert.out 2003-09-25 02:58:06.000000000 -0400
--- pgsql-iuret/src/test/regress/expected/insert.out 2006-07-31 13:01:08.000000000 -0400
***************
*** 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/update.out pgsql-iuret/src/test/regress/expected/update.out
*** pgsql/src/test/regress/expected/update.out 2006-03-14 17:48:25.000000000 -0500
--- pgsql-iuret/src/test/regress/expected/update.out 2006-07-31 22:10:23.000000000 -0400
***************
*** 49,52 ****
--- 49,67 ----
^
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-iuret/src/test/regress/sql/insert.sql
*** pgsql/src/test/regress/sql/insert.sql 2002-04-23 22:22:54.000000000 -0400
--- pgsql-iuret/src/test/regress/sql/insert.sql 2006-07-31 13:01:08.000000000 -0400
***************
*** 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/update.sql pgsql-iuret/src/test/regress/sql/update.sql
*** pgsql/src/test/regress/sql/update.sql 2006-01-22 00:20:35.000000000 -0500
--- pgsql-iuret/src/test/regress/sql/update.sql 2006-07-31 13:01:08.000000000 -0400
***************
*** 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;
Re: Updated INSERT/UPDATE RETURNING
This is a great patch. I was hoping to get this into 8.2 as a major
feature.
---------------------------------------------------------------------------
Jonah H. Harris wrote:
Here's the updated patch with DELETE RETURNING removed. This isn't
really an issue because no one wanted DELETE RETURNING to begin with.It is important to note that this patch is not yet ready to be
committed. I still need to go through and run some more tests on it
but wanted to put it in the queue again and let ya know I've been
given time to make sure it gets in.This patch includes:
- Code changes to core
- Code changes to PL/pgSQL
- Preliminary Documentation Updates (need to add to PL/pgSQL docs)
- Preliminary Regression Tests (need to add PL/pgSQL regressions)There were a couple suggestions for sorta-kewl features like being
able to use INSERT/UPDATE RETURNING in a FOR loop, etc. I may be able
to get those in if people really want it but I looked into it after
Neil mentioned it and IIRC, there are quite a few changes required to
support it.Suggestions requested.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
[ Attachment, skipping... ]
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Re: Updated INSERT/UPDATE RETURNING
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
Here's the updated patch with DELETE RETURNING removed. This isn't
really an issue because no one wanted DELETE RETURNING to begin with.
Huh? Why'd you remove it? I can't imagine it makes things
significantly simpler to omit that case, and even if you can't
think of uses for it, I can (taking jobs from a to-do queue for
instance).
BTW, it occurs to me to wonder whether we've picked a good choice
of syntax. I don't remember where the suggestion to use "RETURNING"
came from (did we borrow it from another DBMS?). But AFAICS this
syntax will require the introducing keyword to be a fully reserved
word, and since RETURNING is not listed as a reserved word in the
SQL spec, reserving it is arguably a spec violation.
The simplest alternative that comes to mind is to use RETURNS instead of
RETURNING; since RETURNS is listed as reserved, this doesn't violate
spec. OTOH we currently treat RETURNS as an unreserved keyword, and
we'd have to promote it to fully reserved. It could be argued that
"returns" is a more likely name for a table column than "returning";
if so we'd actually be more likely to break existing apps this way.
I don't have a strong feeling either way, but now is the time to
decide.
It is important to note that this patch is not yet ready to be
committed.
OK, but we need a final version soon.
regards, tom lane
Re: Updated INSERT/UPDATE RETURNING
On 8/5/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Huh? Why'd you remove it? I can't imagine it makes things
significantly simpler to omit that case, and even if you can't
think of uses for it, I can (taking jobs from a to-do queue for
instance).
It can be added back. Dequeing is a good use-case idea though :)
BTW, it occurs to me to wonder whether we've picked a good choice
of syntax. I don't remember where the suggestion to use "RETURNING"
came from (did we borrow it from another DBMS?).
Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... );
But AFAICS this syntax will require the introducing keyword to be a fully reserved
word, and since RETURNING is not listed as a reserved word in the
SQL spec, reserving it is arguably a spec violation.
True.
The simplest alternative that comes to mind is to use RETURNS instead
I don't have a strong feeling either way, but now is the time to
decide.
I don't care either way, RETURNS is fine I guess.
OK, but we need a final version soon.
Sure thing.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/
Re: Updated INSERT/UPDATE RETURNING
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
On 8/5/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
BTW, it occurs to me to wonder whether we've picked a good choice
of syntax. I don't remember where the suggestion to use "RETURNING"
came from (did we borrow it from another DBMS?).
Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... );
Oh, okay.
The simplest alternative that comes to mind is to use RETURNS instead
I don't have a strong feeling either way, but now is the time to
decide.
I don't care either way, RETURNS is fine I guess.
Well, if the syntax is compatible with Oracle as-is, that's probably
a sufficient reason to stick with it. It's not like we haven't got
any other non-spec reserved words ...
regards, tom lane
Re: Updated INSERT/UPDATE RETURNING
On 8/5/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Jonah H. Harris" <jonah.harris@gmail.com> writes:
Here's the updated patch with DELETE RETURNING removed. This isn't
really an issue because no one wanted DELETE RETURNING to begin with.
I don't have the time to add DELETE RETURNING back in. My initial
patch for this included it, so anyone can feel free to pick it up and
add it back. If no one wants to do this, I'd just say to add INS/UPD
version to 8.2 and I'll work on the DELETE version for 8.3.
As far as the current patch, I'm actually fine with someone reviewing
it as-is; I just wanted another look over it to see if I missed
something... but one of you may see that much quicker than I (as I'm
too close too it).
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation | fax: 732.331.1301
33 Wood Ave S, 2nd Floor | jharris@enterprisedb.com
Iselin, New Jersey 08830 | http://www.enterprisedb.com/