Restructure parsetree representation of DECLARE CURSOR: now it's a

utility statement (DeclareCursorStmt) with a SELECT query dangling from
it, rather than a SELECT query with a few unusual fields in it.  Add
code to determine whether a planned query can safely be run backwards.
If DECLARE CURSOR specifies SCROLL, ensure that the plan can be run
backwards by adding a Materialize plan node if it can't.  Without SCROLL,
you get an error if you try to fetch backwards from a cursor that can't
handle it.  (There is still some discussion about what the exact
behavior should be, but this is necessary infrastructure in any case.)
Along the way, make EXPLAIN DECLARE CURSOR work.

3 files changed, 72 insertions, 98 deletions

diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c
index b22ad763498..b189e3e94bc 100644
--- a/src/backend/executor/execAmi.c
+++ b/src/backend/executor/execAmi.c
@@ -6,7 +6,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- *	$Header: /cvsroot/pgsql/src/backend/executor/execAmi.c,v 1.69 2003/02/09 00:30:39 tgl Exp $
+ *	$Header: /cvsroot/pgsql/src/backend/executor/execAmi.c,v 1.70 2003/03/10 03:53:49 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -281,3 +281,61 @@ ExecSupportsMarkRestore(NodeTag plantype)
 
 	return false;
 }
+
+/*
+ * ExecSupportsBackwardScan - does a plan type support backwards scanning?
+ *
+ * Ideally, all plan types would support backwards scan, but that seems
+ * unlikely to happen soon.  In some cases, a plan node passes the backwards
+ * scan down to its children, and so supports backwards scan only if its
+ * children do.  Therefore, this routine must be passed a complete plan tree.
+ */
+bool
+ExecSupportsBackwardScan(Plan *node)
+{
+	if (node == NULL)
+		return false;
+
+	switch (nodeTag(node))
+	{
+		case T_Result:
+			if (outerPlan(node) != NULL)
+				return ExecSupportsBackwardScan(outerPlan(node));
+			else
+				return false;
+
+		case T_Append:
+		{
+			List   *l;
+
+			foreach(l, ((Append *) node)->appendplans)
+			{
+				if (!ExecSupportsBackwardScan((Plan *) lfirst(l)))
+					return false;
+			}
+			return true;
+		}
+
+		case T_SeqScan:
+		case T_IndexScan:
+		case T_TidScan:
+		case T_FunctionScan:
+			return true;
+
+		case T_SubqueryScan:
+			return ExecSupportsBackwardScan(((SubqueryScan *) node)->subplan);
+
+		case T_Material:
+		case T_Sort:
+			return true;
+
+		case T_Unique:
+			return ExecSupportsBackwardScan(outerPlan(node));
+
+		case T_Limit:
+			return ExecSupportsBackwardScan(outerPlan(node));
+
+		default:
+			return false;
+	}
+}
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 9c4b6e74819..f037e72fd91 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -26,7 +26,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/executor/execMain.c,v 1.200 2003/02/03 15:07:06 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/executor/execMain.c,v 1.201 2003/03/10 03:53:49 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -537,9 +537,7 @@ InitPlan(QueryDesc *queryDesc)
 	 */
 	do_select_into = false;
 
-	if (operation == CMD_SELECT &&
-		!parseTree->isPortal &&
-		parseTree->into != NULL)
+	if (operation == CMD_SELECT && parseTree->into != NULL)
 	{
 		do_select_into = true;
 		/*
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index d94e12cde56..e1ccdf08f97 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/executor/spi.c,v 1.86 2003/02/14 21:12:45 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/executor/spi.c,v 1.87 2003/03/10 03:53:49 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -725,9 +725,7 @@ SPI_cursor_open(const char *name, void *plan, Datum *Values, const char *Nulls)
 
 	if (queryTree->commandType != CMD_SELECT)
 		elog(ERROR, "plan in SPI_cursor_open() is not a SELECT");
-	if (queryTree->isPortal)
-		elog(ERROR, "plan in SPI_cursor_open() must NOT be a DECLARE already");
-	else if (queryTree->into != NULL)
+	if (queryTree->into != NULL)
 		elog(ERROR, "plan in SPI_cursor_open() must NOT be a SELECT INTO");
 
 	/* Increment CommandCounter to see changes made by now */
@@ -764,20 +762,12 @@ SPI_cursor_open(const char *name, void *plan, Datum *Values, const char *Nulls)
 
 	/* Create the portal */
 	portal = CreatePortal(name);
-	if (portal == NULL)
-		elog(ERROR, "failed to create portal \"%s\"", name);
 
 	/* Switch to portals memory and copy the parsetree and plan to there */
 	oldcontext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
 	queryTree = copyObject(queryTree);
 	planTree = copyObject(planTree);
 
-	/* Modify the parsetree to be a cursor */
-	queryTree->isPortal = true;
-	queryTree->into = makeNode(RangeVar);
-	queryTree->into->relname = pstrdup(name);
-	queryTree->isBinary = false;
-
 	/* If the plan has parameters, set them up */
 	if (spiplan->nargs > 0)
 	{
@@ -812,7 +802,7 @@ SPI_cursor_open(const char *name, void *plan, Datum *Values, const char *Nulls)
 		paramLI = NULL;
 
 	/* Create the QueryDesc object */
-	queryDesc = CreateQueryDesc(queryTree, planTree, SPI, NULL,
+	queryDesc = CreateQueryDesc(queryTree, planTree, SPI, pstrdup(name),
 								paramLI, false);
 
 	/* Start the executor */
@@ -1106,7 +1096,8 @@ _SPI_execute(const char *src, int tcount, _SPI_plan *plan)
 					if (stmt->filename == NULL)
 						return SPI_ERROR_COPY;
 				}
-				else if (IsA(queryTree->utilityStmt, ClosePortalStmt) ||
+				else if (IsA(queryTree->utilityStmt, DeclareCursorStmt) ||
+						 IsA(queryTree->utilityStmt, ClosePortalStmt) ||
 						 IsA(queryTree->utilityStmt, FetchStmt))
 					return SPI_ERROR_CURSOR;
 				else if (IsA(queryTree->utilityStmt, TransactionStmt))
@@ -1263,12 +1254,7 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
 static int
 _SPI_pquery(QueryDesc *queryDesc, bool runit, int tcount)
 {
-	Query	   *parseTree = queryDesc->parsetree;
 	int			operation = queryDesc->operation;
-	CommandDest dest = queryDesc->dest;
-	bool		isRetrieveIntoPortal = false;
-	bool		isRetrieveIntoRelation = false;
-	char	   *intoName = NULL;
 	int			res;
 	Oid			save_lastoid;
 
@@ -1276,20 +1262,10 @@ _SPI_pquery(QueryDesc *queryDesc, bool runit, int tcount)
 	{
 		case CMD_SELECT:
 			res = SPI_OK_SELECT;
-			if (parseTree->isPortal)
-			{
-				isRetrieveIntoPortal = true;
-				intoName = parseTree->into->relname;
-				parseTree->isBinary = false;	/* */
-
-				return SPI_ERROR_CURSOR;
-
-			}
-			else if (parseTree->into != NULL)	/* select into table */
+			if (queryDesc->parsetree->into != NULL)	/* select into table */
 			{
 				res = SPI_OK_SELINTO;
-				isRetrieveIntoRelation = true;
-				queryDesc->dest = None; /* */
+				queryDesc->dest = None; /* don't output results anywhere */
 			}
 			break;
 		case CMD_INSERT:
@@ -1315,14 +1291,6 @@ _SPI_pquery(QueryDesc *queryDesc, bool runit, int tcount)
 
 	ExecutorStart(queryDesc);
 
-	/*
-	 * Don't work currently --- need to rearrange callers so that we
-	 * prepare the portal before doing ExecutorStart() etc. See
-	 * pquery.c for the correct order of operations.
-	 */
-	if (isRetrieveIntoPortal)
-		elog(FATAL, "SPI_select: retrieve into portal not implemented");
-
 	ExecutorRun(queryDesc, ForwardScanDirection, (long) tcount);
 
 	_SPI_current->processed = queryDesc->estate->es_processed;
@@ -1334,7 +1302,7 @@ _SPI_pquery(QueryDesc *queryDesc, bool runit, int tcount)
 			elog(FATAL, "SPI_select: # of processed tuples check failed");
 	}
 
-	if (dest == SPI)
+	if (queryDesc->dest == SPI)
 	{
 		SPI_processed = _SPI_current->processed;
 		SPI_lastoid = save_lastoid;
@@ -1367,12 +1335,6 @@ static void
 _SPI_cursor_operation(Portal portal, bool forward, int count,
 					  CommandDest dest)
 {
-	QueryDesc  *querydesc;
-	EState	   *estate;
-	MemoryContext oldcontext;
-	ScanDirection direction;
-	CommandDest olddest;
-
 	/* Check that the portal is valid */
 	if (!PortalIsValid(portal))
 		elog(ERROR, "invalid portal in SPI cursor operation");
@@ -1386,53 +1348,9 @@ _SPI_cursor_operation(Portal portal, bool forward, int count,
 	_SPI_current->processed = 0;
 	_SPI_current->tuptable = NULL;
 
-	/* Switch to the portals memory context */
-	oldcontext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
-
-	querydesc = PortalGetQueryDesc(portal);
-	estate = querydesc->estate;
-
-	/* Save the queries command destination and set it to SPI (for fetch) */
-	/* or None (for move) */
-	olddest = querydesc->dest;
-	querydesc->dest = dest;
-
-	/* Run the executor like PerformPortalFetch and remember states */
-	if (forward)
-	{
-		if (portal->atEnd)
-			direction = NoMovementScanDirection;
-		else
-			direction = ForwardScanDirection;
-
-		ExecutorRun(querydesc, direction, (long) count);
-
-		if (estate->es_processed > 0)
-			portal->atStart = false;	/* OK to back up now */
-		if (count <= 0 || (int) estate->es_processed < count)
-			portal->atEnd = true;		/* we retrieved 'em all */
-	}
-	else
-	{
-		if (portal->atStart)
-			direction = NoMovementScanDirection;
-		else
-			direction = BackwardScanDirection;
-
-		ExecutorRun(querydesc, direction, (long) count);
-
-		if (estate->es_processed > 0)
-			portal->atEnd = false;		/* OK to go forward now */
-		if (count <= 0 || (int) estate->es_processed < count)
-			portal->atStart = true;		/* we retrieved 'em all */
-	}
-
-	_SPI_current->processed = estate->es_processed;
-
-	/* Restore the old command destination and switch back to callers */
-	/* memory context */
-	querydesc->dest = olddest;
-	MemoryContextSwitchTo(oldcontext);
+	/* Run the cursor */
+	_SPI_current->processed = DoPortalFetch(portal, forward, (long) count,
+											dest);
 
 	if (dest == SPI && _SPI_checktuples())
 		elog(FATAL, "SPI_fetch: # of processed tuples check failed");