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.

4 files changed, 96 insertions, 128 deletions

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 2089008bbf5..3486a1e010b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.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/parser/analyze.c,v 1.264 2003/02/13 22:50:01 tgl Exp $
+ *	$Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.265 2003/03/10 03:53:50 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -96,6 +96,8 @@ static Query *transformSelectStmt(ParseState *pstate, SelectStmt *stmt);
 static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
 static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt);
 static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
+static Query *transformDeclareCursorStmt(ParseState *pstate,
+										 DeclareCursorStmt *stmt);
 static Query *transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt);
 static Query *transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt);
 static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt,
@@ -313,6 +315,11 @@ transformStmt(ParseState *pstate, Node *parseTree,
 											   (SelectStmt *) parseTree);
 			break;
 
+		case T_DeclareCursorStmt:
+			result = transformDeclareCursorStmt(pstate,
+												(DeclareCursorStmt *) parseTree);
+			break;
+
 		default:
 
 			/*
@@ -445,7 +452,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt,
 
 		Assert(IsA(selectQuery, Query));
 		Assert(selectQuery->commandType == CMD_SELECT);
-		if (selectQuery->into || selectQuery->isPortal)
+		if (selectQuery->into)
 			elog(ERROR, "INSERT ... SELECT may not specify INTO");
 
 		/*
@@ -1616,27 +1623,6 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 
 	qry->commandType = CMD_SELECT;
 
-	if (stmt->portalname)
-	{
-		/* DECLARE CURSOR */
-		if (stmt->into)
-			elog(ERROR, "DECLARE CURSOR must not specify INTO");
-		if (stmt->forUpdate)
-			elog(ERROR, "DECLARE/UPDATE is not supported"
-				 "\n\tCursors must be READ ONLY");
-		qry->into = makeNode(RangeVar);
-		qry->into->relname = stmt->portalname;
-		qry->isPortal = TRUE;
-		qry->isBinary = stmt->binary;	/* internal portal */
-	}
-	else
-	{
-		/* SELECT */
-		qry->into = stmt->into;
-		qry->isPortal = FALSE;
-		qry->isBinary = FALSE;
-	}
-
 	/* make FOR UPDATE clause available to addRangeTableEntry */
 	pstate->p_forUpdate = stmt->forUpdate;
 
@@ -1646,6 +1632,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
 	/* transform targetlist */
 	qry->targetList = transformTargetList(pstate, stmt->targetList);
 
+	/* handle any SELECT INTO/CREATE TABLE AS spec */
+	qry->into = stmt->into;
 	if (stmt->intoColNames)
 		applyColumnNames(qry->targetList, stmt->intoColNames);
 
@@ -1708,8 +1696,6 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	SetOperationStmt *sostmt;
 	RangeVar   *into;
 	List	   *intoColNames;
-	char	   *portalname;
-	bool		binary;
 	List	   *sortClause;
 	Node	   *limitOffset;
 	Node	   *limitCount;
@@ -1738,14 +1724,10 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 		   leftmostSelect->larg == NULL);
 	into = leftmostSelect->into;
 	intoColNames = leftmostSelect->intoColNames;
-	portalname = stmt->portalname;
-	binary = stmt->binary;
 
 	/* clear them to prevent complaints in transformSetOperationTree() */
 	leftmostSelect->into = NULL;
 	leftmostSelect->intoColNames = NIL;
-	stmt->portalname = NULL;
-	stmt->binary = false;
 
 	/*
 	 * These are not one-time, exactly, but we want to process them here
@@ -1825,36 +1807,13 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt)
 	}
 
 	/*
-	 * Insert one-time items into top-level query
+	 * Handle SELECT INTO/CREATE TABLE AS.
 	 *
-	 * This needs to agree with transformSelectStmt!
-	 */
-	if (portalname)
-	{
-		/* DECLARE CURSOR */
-		if (into)
-			elog(ERROR, "DECLARE CURSOR must not specify INTO");
-		if (forUpdate)
-			elog(ERROR, "DECLARE/UPDATE is not supported"
-				 "\n\tCursors must be READ ONLY");
-		qry->into = makeNode(RangeVar);
-		qry->into->relname = portalname;
-		qry->isPortal = TRUE;
-		qry->isBinary = binary; /* internal portal */
-	}
-	else
-	{
-		/* SELECT */
-		qry->into = into;
-		qry->isPortal = FALSE;
-		qry->isBinary = FALSE;
-	}
-
-	/*
 	 * Any column names from CREATE TABLE AS need to be attached to both
 	 * the top level and the leftmost subquery.  We do not do this earlier
 	 * because we do *not* want the targetnames list to be affected.
 	 */
+	qry->into = into;
 	if (intoColNames)
 	{
 		applyColumnNames(qry->targetList, intoColNames);
@@ -1938,8 +1897,6 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt)
 	 */
 	if (stmt->into)
 		elog(ERROR, "INTO is only allowed on first SELECT of UNION/INTERSECT/EXCEPT");
-	if (stmt->portalname)		/* should not happen */
-		elog(ERROR, "Portal may not appear in UNION/INTERSECT/EXCEPT");
 	/* We don't support forUpdate with set ops at the moment. */
 	if (stmt->forUpdate)
 		elog(ERROR, "SELECT FOR UPDATE is not allowed with UNION/INTERSECT/EXCEPT");
@@ -2328,6 +2285,27 @@ transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt,
 }
 
 static Query *
+transformDeclareCursorStmt(ParseState *pstate, DeclareCursorStmt *stmt)
+{
+	Query	   *result = makeNode(Query);
+	List	   *extras_before = NIL,
+			   *extras_after = NIL;
+
+	result->commandType = CMD_UTILITY;
+	result->utilityStmt = (Node *) stmt;
+
+	stmt->query = (Node *) transformStmt(pstate, stmt->query,
+										 &extras_before, &extras_after);
+
+	/* Shouldn't get any extras, since grammar only allows SelectStmt */
+	if (extras_before || extras_after)
+		elog(ERROR, "transformDeclareCursorStmt: internal error");
+
+	return result;
+}
+
+
+static Query *
 transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
 {
 	Query	   *result = makeNode(Query);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c593196dfc8..045d3cc2ca2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.404 2003/02/16 02:30:38 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.405 2003/03/10 03:53:50 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -135,12 +135,12 @@ static void doNegateFloat(Value *v);
 		CreateDomainStmt CreateGroupStmt CreateOpClassStmt CreatePLangStmt
 		CreateSchemaStmt CreateSeqStmt CreateStmt
 		CreateAssertStmt CreateTrigStmt CreateUserStmt
-		CreatedbStmt CursorStmt DefineStmt DeleteStmt
+		CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt
 		DropGroupStmt DropOpClassStmt DropPLangStmt DropStmt
 		DropAssertStmt DropTrigStmt DropRuleStmt DropCastStmt
 		DropUserStmt DropdbStmt ExplainStmt FetchStmt
 		GrantStmt IndexStmt InsertStmt ListenStmt LoadStmt
-		LockStmt NotifyStmt OptimizableStmt
+		LockStmt NotifyStmt ExplainableStmt PreparableStmt
 		CreateFunctionStmt ReindexStmt RemoveAggrStmt
 		RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt
 		RuleActionStmt RuleActionStmtOrEmpty RuleStmt
@@ -241,7 +241,7 @@ static void doNegateFloat(Value *v);
 %type <ival>	opt_interval
 %type <node>	overlay_placing substr_from substr_for
 
-%type <boolean> opt_instead opt_cursor opt_analyze
+%type <boolean> opt_instead opt_analyze
 %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
@@ -249,7 +249,7 @@ static void doNegateFloat(Value *v);
 %type <boolean> copy_from
 
 %type <ival>	direction reindex_type drop_type
-				opt_column event comment_type
+				opt_column event comment_type cursor_options
 
 %type <ival>	fetch_how_many
 
@@ -481,68 +481,72 @@ stmt :
 			| AlterDomainStmt
 			| AlterGroupStmt
 			| AlterTableStmt
-			| AlterUserStmt
 			| AlterUserSetStmt
+			| AlterUserStmt
+			| AnalyzeStmt
+			| CheckPointStmt
 			| ClosePortalStmt
+			| ClusterStmt
+			| CommentStmt
+			| ConstraintsSetStmt
 			| CopyStmt
-			| CreateStmt
 			| CreateAsStmt
+			| CreateAssertStmt
 			| CreateCastStmt
+			| CreateConversionStmt
 			| CreateDomainStmt
 			| CreateFunctionStmt
-			| CreateSchemaStmt
 			| CreateGroupStmt
-			| CreateSeqStmt
 			| CreateOpClassStmt
 			| CreatePLangStmt
-			| CreateAssertStmt
+			| CreateSchemaStmt
+			| CreateSeqStmt
+			| CreateStmt
 			| CreateTrigStmt
 			| CreateUserStmt
-			| ClusterStmt
+			| CreatedbStmt
 			| DeallocateStmt
+			| DeclareCursorStmt
 			| DefineStmt
-			| DropStmt
-			| TruncateStmt
-			| CommentStmt
+			| DeleteStmt
+			| DropAssertStmt
 			| DropCastStmt
 			| DropGroupStmt
 			| DropOpClassStmt
 			| DropPLangStmt
-			| DropAssertStmt
-			| DropTrigStmt
 			| DropRuleStmt
+			| DropStmt
+			| DropTrigStmt
 			| DropUserStmt
+			| DropdbStmt
 			| ExecuteStmt
 			| ExplainStmt
 			| FetchStmt
 			| GrantStmt
 			| IndexStmt
+			| InsertStmt
 			| ListenStmt
-			| UnlistenStmt
+			| LoadStmt
 			| LockStmt
 			| NotifyStmt
 			| PrepareStmt
 			| ReindexStmt
 			| RemoveAggrStmt
-			| RemoveOperStmt
 			| RemoveFuncStmt
+			| RemoveOperStmt
 			| RenameStmt
 			| RevokeStmt
-			| OptimizableStmt
 			| RuleStmt
+			| SelectStmt
 			| TransactionStmt
-			| ViewStmt
-			| LoadStmt
-			| CreatedbStmt
-			| DropdbStmt
+			| TruncateStmt
+			| UnlistenStmt
+			| UpdateStmt
 			| VacuumStmt
-			| AnalyzeStmt
+			| VariableResetStmt
 			| VariableSetStmt
 			| VariableShowStmt
-			| VariableResetStmt
-			| ConstraintsSetStmt
-			| CheckPointStmt
-			| CreateConversionStmt
+			| ViewStmt
 			| /*EMPTY*/
 				{ $$ = (Node *)NULL; }
 		;
@@ -3961,16 +3965,7 @@ opt_name_list:
  *
  *****************************************************************************/
 
-ExplainStmt:
-			EXPLAIN opt_analyze opt_verbose OptimizableStmt
-				{
-					ExplainStmt *n = makeNode(ExplainStmt);
-					n->analyze = $2;
-					n->verbose = $3;
-					n->query = (Query*)$4;
-					$$ = (Node *)n;
-				}
-			| EXPLAIN opt_analyze opt_verbose ExecuteStmt
+ExplainStmt: EXPLAIN opt_analyze opt_verbose ExplainableStmt
 				{
 					ExplainStmt *n = makeNode(ExplainStmt);
 					n->analyze = $2;
@@ -3980,6 +3975,15 @@ ExplainStmt:
 				}
 		;
 
+ExplainableStmt:
+			SelectStmt
+			| InsertStmt
+			| UpdateStmt
+			| DeleteStmt
+			| DeclareCursorStmt
+			| ExecuteStmt					/* by default all are $$=$1 */
+		;
+
 opt_analyze:
 			analyze_keyword			{ $$ = TRUE; }
 			| /* EMPTY */			{ $$ = FALSE; }
@@ -3992,7 +3996,7 @@ opt_analyze:
  *
  *****************************************************************************/
 
-PrepareStmt: PREPARE name prep_type_clause AS OptimizableStmt
+PrepareStmt: PREPARE name prep_type_clause AS PreparableStmt
 				{
 					PrepareStmt *n = makeNode(PrepareStmt);
 					n->name = $2;
@@ -4011,6 +4015,13 @@ prep_type_list: Typename			{ $$ = makeList1($1); }
 									{ $$ = lappend($1, $3); }
 		;
 
+PreparableStmt:
+			SelectStmt
+			| InsertStmt
+			| UpdateStmt
+			| DeleteStmt					/* by default all are $$=$1 */
+		;
+
 /*****************************************************************************
  *
  *		QUERY:
@@ -4054,26 +4065,6 @@ DeallocateStmt: DEALLOCATE name
 		;
 
 /*****************************************************************************
- *																			 *
- *		Optimizable Stmts:													 *
- *																			 *
- *		one of the five queries processed by the planner					 *
- *																			 *
- *		[ultimately] produces query-trees as specified						 *
- *		in the query-spec document in ~postgres/ref							 *
- *																			 *
- *****************************************************************************/
-
-OptimizableStmt:
-			SelectStmt
-			| CursorStmt
-			| UpdateStmt
-			| InsertStmt
-			| DeleteStmt					/* by default all are $$=$1 */
-		;
-
-
-/*****************************************************************************
  *
  *		QUERY:
  *				INSERT STATEMENTS
@@ -4213,20 +4204,20 @@ UpdateStmt: UPDATE relation_expr
  *				CURSOR STATEMENTS
  *
  *****************************************************************************/
-CursorStmt: DECLARE name opt_cursor CURSOR FOR SelectStmt
+DeclareCursorStmt: DECLARE name cursor_options CURSOR FOR SelectStmt
 				{
-					SelectStmt *n = (SelectStmt *)$6;
+					DeclareCursorStmt *n = makeNode(DeclareCursorStmt);
 					n->portalname = $2;
-					n->binary = $3;
-					$$ = $6;
+					n->options = $3;
+					n->query = $6;
+					$$ = (Node *)n;
 				}
 		;
 
-opt_cursor: BINARY									{ $$ = TRUE; }
-			| INSENSITIVE							{ $$ = FALSE; }
-			| SCROLL								{ $$ = FALSE; }
-			| INSENSITIVE SCROLL					{ $$ = FALSE; }
-			| /*EMPTY*/								{ $$ = FALSE; }
+cursor_options: /*EMPTY*/					{ $$ = 0; }
+			| cursor_options BINARY			{ $$ = $1 | CURSOR_OPT_BINARY; }
+			| cursor_options SCROLL			{ $$ = $1 | CURSOR_OPT_SCROLL; }
+			| cursor_options INSENSITIVE	{ $$ = $1 | CURSOR_OPT_INSENSITIVE; }
 		;
 
 /*****************************************************************************
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 33e7cce4203..d4c13165e5a 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.110 2003/02/16 02:30:38 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_clause.c,v 1.111 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -414,7 +414,7 @@ transformRangeSubselect(ParseState *pstate, RangeSubselect *r)
 
 	if (query->commandType != CMD_SELECT)
 		elog(ERROR, "Expected SELECT query from subselect in FROM");
-	if (query->resultRelation != 0 || query->into != NULL || query->isPortal)
+	if (query->resultRelation != 0 || query->into != NULL)
 		elog(ERROR, "Subselect in FROM may not have SELECT INTO");
 
 	/*
diff --git a/src/backend/parser/parse_type.c b/src/backend/parser/parse_type.c
index e5f1e88ffdb..1f0b7639d5f 100644
--- a/src/backend/parser/parse_type.c
+++ b/src/backend/parser/parse_type.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_type.c,v 1.53 2003/02/19 23:41:15 momjian Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/parser/parse_type.c,v 1.54 2003/03/10 03:53:51 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -455,7 +455,6 @@ parseTypeString(const char *str, Oid *type_id, int32 *typmod)
 		stmt->groupClause != NIL ||
 		stmt->havingClause != NULL ||
 		stmt->sortClause != NIL ||
-		stmt->portalname != NULL ||
 		stmt->limitOffset != NULL ||
 		stmt->limitCount != NULL ||
 		stmt->forUpdate != NIL ||