Add INSERT/UPDATE/DELETE RETURNING, with basic docs and regression tests.

plpgsql support to come later.  Along the way, convert execMain's
SELECT INTO support into a DestReceiver, in order to eliminate some ugly
special cases.

Jonah Harris and Tom Lane

7 files changed, 48 insertions, 17 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index b97c3d07ec..d2141f1b38 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.348 2006/08/10 02:36:29 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.349 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200608091
+#define CATALOG_VERSION_NO	200608101
 
 #endif
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index b2baec841b..d5b6213a35 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/executor/executor.h,v 1.128 2006/08/04 21:33:36 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/executor/executor.h,v 1.129 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -132,6 +132,7 @@ extern void ExecConstraints(ResultRelInfo *resultRelInfo,
 				TupleTableSlot *slot, EState *estate);
 extern TupleTableSlot *EvalPlanQual(EState *estate, Index rti,
 			 ItemPointer tid, TransactionId priorXmax, CommandId curCid);
+extern DestReceiver *CreateIntoRelDestReceiver(void);
 
 /*
  * prototypes from functions in execProcnode.c
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 30b3fce416..47b7c01f23 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.158 2006/08/04 21:33:36 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/execnodes.h,v 1.159 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -262,6 +262,7 @@ typedef struct JunkFilter
  *		TrigInstrument			optional runtime measurements for triggers
  *		ConstraintExprs			array of constraint-checking expr states
  *		junkFilter				for removing junk attributes from tuples
+ *		projectReturning		for computing a RETURNING list
  * ----------------
  */
 typedef struct ResultRelInfo
@@ -277,6 +278,7 @@ typedef struct ResultRelInfo
 	struct Instrumentation *ri_TrigInstrument;
 	List	  **ri_ConstraintExprs;
 	JunkFilter *ri_junkFilter;
+	ProjectionInfo *ri_projectReturning;
 } ResultRelInfo;
 
 /* ----------------
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e2567ff8e4..f0f40e002e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.321 2006/08/10 02:36:29 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/parsenodes.h,v 1.322 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -104,6 +104,8 @@ typedef struct Query
 
 	List	   *targetList;		/* target list (of TargetEntry) */
 
+	List	   *returningList;	/* return-values list (of TargetEntry) */
+
 	List	   *groupClause;	/* a list of GroupClause's */
 
 	Node	   *havingQual;		/* qualifications applied to groups */
@@ -125,10 +127,23 @@ typedef struct Query
 	 * tree, the planner will add all the child tables to the rtable and store
 	 * a list of the rtindexes of all the result relations here. This is done
 	 * at plan time, not parse time, since we don't want to commit to the
-	 * exact set of child tables at parse time.  This field ought to go in
+	 * exact set of child tables at parse time.  XXX This field ought to go in
 	 * some sort of TopPlan plan node, not in the Query.
 	 */
 	List	   *resultRelations;	/* integer list of RT indexes, or NIL */
+
+	/*
+	 * If the query has a returningList then the planner will store a list
+	 * of processed targetlists (one per result relation) here.  We must
+	 * have a separate RETURNING targetlist for each result rel because
+	 * column numbers may vary within an inheritance tree.  In the targetlists,
+	 * Vars referencing the result relation will have their original varno
+	 * and varattno, while Vars referencing other rels will be converted
+	 * to have varno OUTER and varattno referencing a resjunk entry in the
+	 * top plan node's targetlist.  XXX This field ought to go in some sort of
+	 * TopPlan plan node, not in the Query.
+	 */
+	List	   *returningLists;		/* list of lists of TargetEntry, or NIL */
 } Query;
 
 
@@ -648,6 +663,7 @@ typedef struct InsertStmt
 	RangeVar   *relation;		/* relation to insert into */
 	List	   *cols;			/* optional: names of the target columns */
 	Node	   *selectStmt;		/* the source SELECT/VALUES, or NULL */
+	List	   *returningList;	/* list of expressions to return */
 } InsertStmt;
 
 /* ----------------------
@@ -658,8 +674,9 @@ typedef struct DeleteStmt
 {
 	NodeTag		type;
 	RangeVar   *relation;		/* relation to delete from */
-	Node	   *whereClause;	/* qualifications */
 	List	   *usingClause;	/* optional using clause for more tables */
+	Node	   *whereClause;	/* qualifications */
+	List	   *returningList;	/* list of expressions to return */
 } DeleteStmt;
 
 /* ----------------------
@@ -673,6 +690,7 @@ typedef struct UpdateStmt
 	List	   *targetList;		/* the target list (of ResTarget) */
 	Node	   *whereClause;	/* qualifications */
 	List	   *fromClause;		/* optional from clause for more tables */
+	List	   *returningList;	/* list of expressions to return */
 } UpdateStmt;
 
 /* ----------------------
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index b21ade9f9a..c4c9389af9 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.94 2006/07/26 00:34:48 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/optimizer/planmain.h,v 1.95 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -80,6 +80,9 @@ extern void process_implied_equality(PlannerInfo *root,
  * prototypes for plan/setrefs.c
  */
 extern Plan *set_plan_references(Plan *plan, List *rtable);
+extern List *set_returning_clause_references(List *rlist,
+								Plan *topplan,
+								Index resultRelation);
 extern void fix_opfuncids(Node *node);
 extern void set_opfuncid(OpExpr *opexpr);
 
diff --git a/src/include/tcop/dest.h b/src/include/tcop/dest.h
index 1461aff197..0e0c640d2a 100644
--- a/src/include/tcop/dest.h
+++ b/src/include/tcop/dest.h
@@ -3,8 +3,8 @@
  * dest.h
  *	  support for communication destinations
  *
- * Whenever the backend executes a query, the results
- * have to go someplace.
+ * Whenever the backend executes a query that returns tuples, the results
+ * have to go someplace.  For example:
  *
  *	  - stdout is the destination only when we are running a
  *		standalone backend (no postmaster) and are returning results
@@ -54,7 +54,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/tcop/dest.h,v 1.50 2006/03/05 15:59:00 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/tcop/dest.h,v 1.51 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -84,7 +84,8 @@ typedef enum
 	DestRemote,					/* results sent to frontend process */
 	DestRemoteExecute,			/* sent to frontend, in Execute command */
 	DestSPI,					/* results sent to SPI manager */
-	DestTuplestore				/* results sent to Tuplestore */
+	DestTuplestore,				/* results sent to Tuplestore */
+	DestIntoRel					/* results sent to relation (SELECT INTO) */
 } CommandDest;
 
 /* ----------------
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index 3704240ff5..3f308f5f58 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -39,7 +39,7 @@
  * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/utils/portal.h,v 1.64 2006/08/08 01:23:15 momjian Exp $
+ * $PostgreSQL: pgsql/src/include/utils/portal.h,v 1.65 2006/08/12 02:52:06 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -62,6 +62,12 @@
  * supports holdable cursors (the Executor results can be dumped into a
  * tuplestore for access after transaction completion).
  *
+ * PORTAL_ONE_RETURNING: the portal contains a single INSERT/UPDATE/DELETE
+ * query with a RETURNING clause.  On first execution, we run the statement
+ * and dump its results into the portal tuplestore; the results are then
+ * returned to the client as demanded.  (We can't support suspension of
+ * the query partway through, because the AFTER TRIGGER code can't cope.)
+ *
  * PORTAL_UTIL_SELECT: the portal contains a utility statement that returns
  * a SELECT-like result (for example, EXPLAIN or SHOW).  On first execution,
  * we run the statement and dump its results into the portal tuplestore;
@@ -73,6 +79,7 @@
 typedef enum PortalStrategy
 {
 	PORTAL_ONE_SELECT,
+	PORTAL_ONE_RETURNING,
 	PORTAL_UTIL_SELECT,
 	PORTAL_MULTI_QUERY
 } PortalStrategy;
@@ -133,7 +140,6 @@ typedef struct PortalData
 
 	/* Status data */
 	PortalStatus status;		/* see above */
-	bool		portalUtilReady;	/* PortalRunUtility complete? */
 
 	/* If not NULL, Executor is active; call ExecutorEnd eventually: */
 	QueryDesc  *queryDesc;		/* info needed for executor invocation */
@@ -144,9 +150,9 @@ typedef struct PortalData
 	int16	   *formats;		/* a format code for each column */
 
 	/*
-	 * Where we store tuples for a held cursor or a PORTAL_UTIL_SELECT query.
-	 * (A cursor held past the end of its transaction no longer has any active
-	 * executor state.)
+	 * Where we store tuples for a held cursor or a PORTAL_ONE_RETURNING or
+	 * PORTAL_UTIL_SELECT query.  (A cursor held past the end of its
+	 * transaction no longer has any active executor state.)
 	 */
 	Tuplestorestate *holdStore; /* store for holdable cursors */
 	MemoryContext holdContext;	/* memory containing holdStore */