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.

10 files changed, 59 insertions, 59 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index fc24db5d2e1..6344f0bae53 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -37,7 +37,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: catversion.h,v 1.179 2003/02/22 00:45:05 tgl Exp $
+ * $Id: catversion.h,v 1.180 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	200302211
+#define CATALOG_VERSION_NO	200303091
 
 #endif
diff --git a/src/include/commands/portalcmds.h b/src/include/commands/portalcmds.h
index d143423f6a3..3f2a4221add 100644
--- a/src/include/commands/portalcmds.h
+++ b/src/include/commands/portalcmds.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: portalcmds.h,v 1.4 2002/12/30 15:31:50 momjian Exp $
+ * $Id: portalcmds.h,v 1.5 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -16,23 +16,16 @@
 
 #include "utils/portal.h"
 
-/*
- * PerformPortalFetch
- *		Performs the POSTQUEL function FETCH.  Fetches count
- * tuples in portal with name in the forward direction iff goForward.
- *
- * Exceptions:
- *		BadArg if forward invalid.
- *		"ERROR" if portal not found.
- */
+
+extern void PerformCursorOpen(DeclareCursorStmt *stmt, CommandDest dest);
+
 extern void PerformPortalFetch(char *name, bool forward, long count,
 				   CommandDest dest, char *completionTag);
 
-/*
- * PerformPortalClose
- *		Performs the POSTQUEL function CLOSE.
- */
-extern void PerformPortalClose(char *name, CommandDest dest);
+extern long DoPortalFetch(Portal portal, bool forward, long count,
+						  CommandDest dest);
+
+extern void PerformPortalClose(char *name);
 
 extern void PortalCleanup(Portal portal);
 
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ee30f518969..785d21718b2 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: executor.h,v 1.89 2003/02/09 00:30:39 tgl Exp $
+ * $Id: executor.h,v 1.90 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -35,6 +35,7 @@ extern void ExecReScan(PlanState *node, ExprContext *exprCtxt);
 extern void ExecMarkPos(PlanState *node);
 extern void ExecRestrPos(PlanState *node);
 extern bool ExecSupportsMarkRestore(NodeTag plantype);
+extern bool ExecSupportsBackwardScan(Plan *node);
 
 /*
  * prototypes from functions in execGrouping.c
diff --git a/src/include/executor/spi.h b/src/include/executor/spi.h
index 85a987b14b2..ead328da247 100644
--- a/src/include/executor/spi.h
+++ b/src/include/executor/spi.h
@@ -2,7 +2,7 @@
  *
  * spi.h
  *
- * $Id: spi.h,v 1.35 2002/12/30 22:10:54 tgl Exp $
+ * $Id: spi.h,v 1.36 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -30,9 +30,10 @@
 #include "tcop/utility.h"
 #include "tcop/dest.h"
 #include "nodes/params.h"
+#include "utils/builtins.h"
 #include "utils/datum.h"
+#include "utils/portal.h"
 #include "utils/syscache.h"
-#include "utils/builtins.h"
 #include "catalog/pg_language.h"
 #include "access/heapam.h"
 #include "access/xact.h"
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h
index 356f4b60fa3..3304ea89e4e 100644
--- a/src/include/nodes/nodes.h
+++ b/src/include/nodes/nodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: nodes.h,v 1.137 2003/02/16 02:30:39 tgl Exp $
+ * $Id: nodes.h,v 1.138 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -248,6 +248,7 @@ typedef enum NodeTag
 	T_PrepareStmt,
 	T_ExecuteStmt,
 	T_DeallocateStmt,
+	T_DeclareCursorStmt,
 
 	T_A_Expr = 800,
 	T_ColumnRef,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 381c11c3893..c84348ded9e 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: parsenodes.h,v 1.231 2003/02/16 02:30:39 tgl Exp $
+ * $Id: parsenodes.h,v 1.232 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -38,9 +38,6 @@ typedef enum QuerySource
  *	  for further processing by the optimizer
  *	  utility statements (i.e. non-optimizable statements)
  *	  have the *utilityStmt field set.
- *
- * we need the isPortal flag because portal names can be null too; can
- * get rid of it if we support CURSOR as a commandType.
  */
 typedef struct Query
 {
@@ -54,10 +51,8 @@ typedef struct Query
 								 * statement */
 
 	int			resultRelation; /* target relation (index into rtable) */
-	RangeVar   *into;			/* target relation or portal (cursor) for
-								 * portal just name is meaningful */
-	bool		isPortal;		/* is this a retrieve into portal? */
-	bool		isBinary;		/* binary portal? */
+
+	RangeVar   *into;			/* target relation for SELECT INTO */
 
 	bool		hasAggs;		/* has aggregates in tlist or havingQual */
 	bool		hasSubLinks;	/* has subquery SubLink */
@@ -597,6 +592,8 @@ typedef struct SelectStmt
 
 	/*
 	 * These fields are used only in "leaf" SelectStmts.
+	 *
+	 * into and intoColNames are a kluge; they belong somewhere else...
 	 */
 	List	   *distinctClause; /* NULL, list of DISTINCT ON exprs, or
 								 * lcons(NIL,NIL) for all (SELECT
@@ -611,11 +608,9 @@ typedef struct SelectStmt
 
 	/*
 	 * These fields are used in both "leaf" SelectStmts and upper-level
-	 * SelectStmts.  portalname/binary may only be set at the top level.
+	 * SelectStmts.
 	 */
 	List	   *sortClause;		/* sort clause (a list of SortGroupBy's) */
-	char	   *portalname;		/* the portal (cursor) to create */
-	bool		binary;			/* a binary (internal) portal? */
 	Node	   *limitOffset;	/* # of result tuples to skip */
 	Node	   *limitCount;		/* # of result tuples to return */
 	List	   *forUpdate;		/* FOR UPDATE clause */
@@ -816,16 +811,6 @@ typedef struct PrivTarget
 } PrivTarget;
 
 /* ----------------------
- *		Close Portal Statement
- * ----------------------
- */
-typedef struct ClosePortalStmt
-{
-	NodeTag		type;
-	char	   *portalname;		/* name of the portal (cursor) */
-} ClosePortalStmt;
-
-/* ----------------------
  *		Copy Statement
  * ----------------------
  */
@@ -1212,7 +1197,33 @@ typedef struct CommentStmt
 } CommentStmt;
 
 /* ----------------------
- *		Fetch Statement
+ *		Declare Cursor Statement
+ * ----------------------
+ */
+#define CURSOR_OPT_BINARY		0x0001
+#define CURSOR_OPT_SCROLL		0x0002
+#define CURSOR_OPT_INSENSITIVE	0x0004
+
+typedef struct DeclareCursorStmt
+{
+	NodeTag		type;
+	char	   *portalname;		/* name of the portal (cursor) */
+	int			options;		/* bitmask of options (see above) */
+	Node	   *query;			/* the SELECT query */
+} DeclareCursorStmt;
+
+/* ----------------------
+ *		Close Portal Statement
+ * ----------------------
+ */
+typedef struct ClosePortalStmt
+{
+	NodeTag		type;
+	char	   *portalname;		/* name of the portal (cursor) */
+} ClosePortalStmt;
+
+/* ----------------------
+ *		Fetch Statement (also Move)
  * ----------------------
  */
 typedef enum FetchDirection
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 399b3bb1310..bd1d757e6a7 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: planmain.h,v 1.68 2003/01/24 03:58:44 tgl Exp $
+ * $Id: planmain.h,v 1.69 2003/03/10 03:53:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -44,6 +44,7 @@ extern Group *make_group(Query *root, List *tlist,
 						 double numGroups,
 						 Plan *lefttree);
 extern Material *make_material(List *tlist, Plan *lefttree);
+extern Plan *materialize_finished_plan(Plan *subplan);
 extern Unique *make_unique(List *tlist, Plan *lefttree, List *distinctList);
 extern Limit *make_limit(List *tlist, Plan *lefttree,
 		   Node *limitOffset, Node *limitCount);
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index 16885b2f138..f9500202ce8 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: planner.h,v 1.25 2003/01/20 18:55:05 tgl Exp $
+ * $Id: planner.h,v 1.26 2003/03/10 03:53:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -18,7 +18,7 @@
 #include "nodes/plannodes.h"
 
 
-extern Plan *planner(Query *parse);
+extern Plan *planner(Query *parse, bool isCursor, int cursorOptions);
 extern Plan *subquery_planner(Query *parse, double tuple_fraction);
 
 #endif   /* PLANNER_H */
diff --git a/src/include/tcop/pquery.h b/src/include/tcop/pquery.h
index b0014249977..c992306a9af 100644
--- a/src/include/tcop/pquery.h
+++ b/src/include/tcop/pquery.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: pquery.h,v 1.23 2002/12/05 15:50:39 tgl Exp $
+ * $Id: pquery.h,v 1.24 2003/03/10 03:53:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -15,12 +15,9 @@
 #define PQUERY_H
 
 #include "executor/execdesc.h"
-#include "utils/portal.h"
 
 
 extern void ProcessQuery(Query *parsetree, Plan *plan, CommandDest dest,
 			 char *completionTag);
 
-extern Portal PreparePortal(char *portalName);
-
 #endif   /* PQUERY_H */
diff --git a/src/include/utils/portal.h b/src/include/utils/portal.h
index 41fa2f735c2..21469dd52df 100644
--- a/src/include/utils/portal.h
+++ b/src/include/utils/portal.h
@@ -9,7 +9,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: portal.h,v 1.37 2002/12/30 22:10:54 tgl Exp $
+ * $Id: portal.h,v 1.38 2003/03/10 03:53:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -27,8 +27,9 @@ typedef struct PortalData
 	char	   *name;			/* Portal's name */
 	MemoryContext heap;			/* subsidiary memory */
 	QueryDesc  *queryDesc;		/* Info about query associated with portal */
-	bool		atStart;		/* T => fetch backwards is not allowed */
-	bool		atEnd;			/* T => fetch forwards is not allowed */
+	bool		backwardOK;		/* is fetch backwards allowed at all? */
+	bool		atStart;		/* T => fetch backwards is not allowed now */
+	bool		atEnd;			/* T => fetch forwards is not allowed now */
 	void		(*cleanup) (Portal);	/* Cleanup routine (optional) */
 } PortalData;
 
@@ -44,12 +45,6 @@ typedef struct PortalData
 #define PortalGetQueryDesc(portal)	((portal)->queryDesc)
 #define PortalGetHeapMemory(portal) ((portal)->heap)
 
-/*
- * estimate of the maximum number of open portals a user would have,
- * used in initially sizing the PortalHashTable in EnablePortalManager()
- */
-#define PORTALS_PER_USER	   64
-
 
 extern void EnablePortalManager(void);
 extern void AtEOXact_portals(void);