From f622c5404905cb998adabe3a3527f7e9cdace229 Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 2 Aug 2004 01:30:51 +0000
Subject: [PATCH] Allow DECLARE CURSOR to take parameters from the portal in
 which it is executed.  Previously, the DECLARE would succeed but subsequent
 FETCHes would fail since the parameter values supplied to DECLARE were not
 propagated to the portal created for the cursor. In support of this, add type
 Oids to ParamListInfo entries, which seems like a good idea anyway since code
 that extracts a value can double-check that it got the type of value it was
 expecting. Oliver Jowett, with minor editorialization by Tom Lane.

---
 src/backend/commands/portalcmds.c     |  19 ++++--
 src/backend/commands/prepare.c        |  11 +--
 src/backend/commands/schemacmds.c     |   4 +-
 src/backend/executor/execQual.c       |  62 ++++-------------
 src/backend/executor/functions.c      |   8 ++-
 src/backend/executor/spi.c            |   8 ++-
 src/backend/nodes/Makefile            |   4 +-
 src/backend/nodes/params.c            | 122 ++++++++++++++++++++++++++++++++++
 src/backend/optimizer/util/clauses.c  |  42 ++++--------
 src/backend/tcop/postgres.c           |   3 +-
 src/backend/tcop/pquery.c             |   6 +-
 src/backend/tcop/utility.c            |   6 +-
 src/include/commands/portalcmds.h     |   4 +-
 src/include/nodes/params.h            |  13 +++-
 src/include/tcop/utility.h            |   7 +-
 src/pl/plpgsql/src/pl_exec.c          |   7 +-
 src/test/regress/expected/portals.out |  35 ++++++++++
 src/test/regress/sql/portals.sql      |  17 +++++
 18 files changed, 263 insertions(+), 115 deletions(-)
 create mode 100644 src/backend/nodes/params.c

diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index 509d9e0dfa..64d0c77489 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -14,7 +14,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.30 2004/07/31 00:45:31 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/portalcmds.c,v 1.31 2004/08/02 01:30:40 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -36,7 +36,7 @@
  *		Execute SQL DECLARE CURSOR command.
  */
 void
-PerformCursorOpen(DeclareCursorStmt *stmt)
+PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params)
 {
 	List	   *rewritten;
 	Query	   *query;
@@ -104,6 +104,17 @@ PerformCursorOpen(DeclareCursorStmt *stmt)
 					  list_make1(plan),
 					  PortalGetHeapMemory(portal));
 
+	/*
+	 * Also copy the outer portal's parameter list into the inner portal's
+	 * memory context.  We want to pass down the parameter values in case
+	 * we had a command like
+	 *			DECLARE c CURSOR FOR SELECT ... WHERE foo = $1
+	 * This will have been parsed using the outer parameter set and the
+	 * parameter value needs to be preserved for use when the cursor is
+	 * executed.
+	 */
+	params = copyParamList(params);
+
 	MemoryContextSwitchTo(oldContext);
 
 	/*
@@ -123,9 +134,9 @@ PerformCursorOpen(DeclareCursorStmt *stmt)
 	}
 
 	/*
-	 * Start execution --- never any params for a cursor.
+	 * Start execution, inserting parameters if any.
 	 */
-	PortalStart(portal, NULL);
+	PortalStart(portal, params);
 
 	Assert(portal->strategy == PORTAL_ONE_SELECT);
 
diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c
index 9c183cb782..d4c8357ff0 100644
--- a/src/backend/commands/prepare.c
+++ b/src/backend/commands/prepare.c
@@ -10,7 +10,7 @@
  * Copyright (c) 2002-2003, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.28 2004/06/11 01:08:38 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.29 2004/08/02 01:30:40 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -200,7 +200,7 @@ ExecuteQuery(ExecuteStmt *stmt, DestReceiver *dest, char *completionTag)
 
 /*
  * Evaluates a list of parameters, using the given executor state. It
- * requires a list of the parameter values themselves, and a list of
+ * requires a list of the parameter expressions themselves, and a list of
  * their types. It returns a filled-in ParamListInfo -- this can later
  * be passed to CreateQueryDesc(), which allows the executor to make use
  * of the parameters during query execution.
@@ -211,7 +211,7 @@ EvaluateParams(EState *estate, List *params, List *argtypes)
 	int			nargs = list_length(argtypes);
 	ParamListInfo paramLI;
 	List	   *exprstates;
-	ListCell   *l;
+	ListCell   *le, *la;
 	int			i = 0;
 
 	/* Parser should have caught this error, but check for safety */
@@ -223,9 +223,9 @@ EvaluateParams(EState *estate, List *params, List *argtypes)
 	paramLI = (ParamListInfo)
 		palloc0((nargs + 1) * sizeof(ParamListInfoData));
 
-	foreach(l, exprstates)
+	forboth(le, exprstates, la, argtypes)
 	{
-		ExprState  *n = lfirst(l);
+		ExprState  *n = lfirst(le);
 		bool		isNull;
 
 		paramLI[i].value = ExecEvalExprSwitchContext(n,
@@ -234,6 +234,7 @@ EvaluateParams(EState *estate, List *params, List *argtypes)
 													 NULL);
 		paramLI[i].kind = PARAM_NUM;
 		paramLI[i].id = i + 1;
+		paramLI[i].ptype = lfirst_oid(la);
 		paramLI[i].isnull = isNull;
 
 		i++;
diff --git a/src/backend/commands/schemacmds.c b/src/backend/commands/schemacmds.c
index 2dead1e15a..d73c65aa6b 100644
--- a/src/backend/commands/schemacmds.c
+++ b/src/backend/commands/schemacmds.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/schemacmds.c,v 1.21 2004/08/01 20:30:48 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/schemacmds.c,v 1.22 2004/08/02 01:30:40 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -168,7 +168,7 @@ CreateSchemaCommand(CreateSchemaStmt *stmt)
 			/* schemas should contain only utility stmts */
 			Assert(querytree->commandType == CMD_UTILITY);
 			/* do this step */
-			ProcessUtility(querytree->utilityStmt, None_Receiver, NULL);
+			ProcessUtility(querytree->utilityStmt, NULL, None_Receiver, NULL);
 			/* make sure later steps can see the object created here */
 			CommandCounterIncrement();
 		}
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c
index e4362c38f5..6ac61d3c5b 100644
--- a/src/backend/executor/execQual.c
+++ b/src/backend/executor/execQual.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.164 2004/06/09 19:08:14 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/execQual.c,v 1.165 2004/08/02 01:30:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -589,56 +589,18 @@ ExecEvalParam(ExprState *exprstate, ExprContext *econtext,
 	else
 	{
 		/*
-		 * All other parameter types must be sought in
-		 * ecxt_param_list_info. NOTE: The last entry in the param array
-		 * is always an entry with kind == PARAM_INVALID.
+		 * All other parameter types must be sought in ecxt_param_list_info.
 		 */
-		ParamListInfo paramList = econtext->ecxt_param_list_info;
-		char	   *thisParamName = expression->paramname;
-		bool		matchFound = false;
-
-		if (paramList != NULL)
-		{
-			while (paramList->kind != PARAM_INVALID && !matchFound)
-			{
-				if (thisParamKind == paramList->kind)
-				{
-					switch (thisParamKind)
-					{
-						case PARAM_NAMED:
-							if (strcmp(paramList->name, thisParamName) == 0)
-								matchFound = true;
-							break;
-						case PARAM_NUM:
-							if (paramList->id == thisParamId)
-								matchFound = true;
-							break;
-						default:
-							elog(ERROR, "unrecognized paramkind: %d",
-								 thisParamKind);
-					}
-				}
-				if (!matchFound)
-					paramList++;
-			}					/* while */
-		}						/* if */
-
-		if (!matchFound)
-		{
-			if (thisParamKind == PARAM_NAMED)
-				ereport(ERROR,
-						(errcode(ERRCODE_UNDEFINED_OBJECT),
-						 errmsg("no value found for parameter \"%s\"",
-								thisParamName)));
-			else
-				ereport(ERROR,
-						(errcode(ERRCODE_UNDEFINED_OBJECT),
-						 errmsg("no value found for parameter %d",
-								thisParamId)));
-		}
-
-		*isNull = paramList->isnull;
-		return paramList->value;
+		ParamListInfo paramInfo;
+
+		paramInfo = lookupParam(econtext->ecxt_param_list_info,
+								thisParamKind,
+								expression->paramname,
+								thisParamId,
+								false);
+		Assert(paramInfo->ptype == expression->paramtype);
+		*isNull = paramInfo->isnull;
+		return paramInfo->value;
 	}
 }
 
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 0073f8f55c..cfcccb6169 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.83 2004/07/15 13:51:38 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/functions.c,v 1.84 2004/08/02 01:30:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -58,6 +58,7 @@ typedef struct local_es
  */
 typedef struct
 {
+	Oid        *argtypes;       /* resolved types of arguments */
 	Oid			rettype;		/* actual return type */
 	int			typlen;			/* length of the return type */
 	bool		typbyval;		/* true if return type is pass by value */
@@ -223,6 +224,7 @@ init_sql_fcache(FmgrInfo *finfo)
 	}
 	else
 		argOidVect = NULL;
+	fcache->argtypes = argOidVect;
 
 	tmp = SysCacheGetAttr(PROCOID,
 						  procedureTuple,
@@ -283,7 +285,8 @@ postquel_getnext(execution_state *es)
 
 	if (es->qd->operation == CMD_UTILITY)
 	{
-		ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->dest, NULL);
+		ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->params,
+					   es->qd->dest, NULL);
 		return NULL;
 	}
 
@@ -332,6 +335,7 @@ postquel_sub_params(SQLFunctionCachePtr fcache,
 		{
 			paramLI[i].kind = PARAM_NUM;
 			paramLI[i].id = i + 1;
+			paramLI[i].ptype = fcache->argtypes[i];
 			paramLI[i].value = fcinfo->arg[i];
 			paramLI[i].isnull = fcinfo->argnull[i];
 		}
diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c
index 7840f5f787..33379d5222 100644
--- a/src/backend/executor/spi.c
+++ b/src/backend/executor/spi.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.122 2004/07/31 20:55:41 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/executor/spi.c,v 1.123 2004/08/02 01:30:41 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -820,6 +820,7 @@ SPI_cursor_open(const char *name, void *plan, Datum *Values, const char *Nulls)
 		{
 			paramLI[k].kind = PARAM_NUM;
 			paramLI[k].id = k + 1;
+			paramLI[k].ptype = spiplan->argtypes[k];
 			paramLI[k].isnull = (Nulls && Nulls[k] == 'n');
 			if (paramLI[k].isnull)
 			{
@@ -1251,7 +1252,7 @@ _SPI_execute(const char *src, int tcount, _SPI_plan *plan)
 				res = SPI_OK_UTILITY;
 				if (plan == NULL)
 				{
-					ProcessUtility(queryTree->utilityStmt, dest, NULL);
+					ProcessUtility(queryTree->utilityStmt, NULL, dest, NULL);
 					CommandCounterIncrement();
 				}
 			}
@@ -1319,6 +1320,7 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
 		{
 			paramLI[k].kind = PARAM_NUM;
 			paramLI[k].id = k + 1;
+			paramLI[k].ptype = plan->argtypes[k];
 			paramLI[k].isnull = (Nulls && Nulls[k] == 'n');
 			paramLI[k].value = Values[k];
 		}
@@ -1366,7 +1368,7 @@ _SPI_execute_plan(_SPI_plan *plan, Datum *Values, const char *Nulls,
 			dest = CreateDestReceiver(queryTree->canSetTag ? SPI : None, NULL);
 			if (queryTree->commandType == CMD_UTILITY)
 			{
-				ProcessUtility(queryTree->utilityStmt, dest, NULL);
+				ProcessUtility(queryTree->utilityStmt, paramLI, dest, NULL);
 				res = SPI_OK_UTILITY;
 				CommandCounterIncrement();
 			}
diff --git a/src/backend/nodes/Makefile b/src/backend/nodes/Makefile
index a4c19201bb..86f533e7a0 100644
--- a/src/backend/nodes/Makefile
+++ b/src/backend/nodes/Makefile
@@ -4,7 +4,7 @@
 #    Makefile for backend/nodes
 #
 # IDENTIFICATION
-#    $PostgreSQL: pgsql/src/backend/nodes/Makefile,v 1.16 2004/01/07 18:43:36 neilc Exp $
+#    $PostgreSQL: pgsql/src/backend/nodes/Makefile,v 1.17 2004/08/02 01:30:42 tgl Exp $
 #
 #-------------------------------------------------------------------------
 
@@ -14,7 +14,7 @@ include $(top_builddir)/src/Makefile.global
 
 OBJS = nodeFuncs.o nodes.o list.o bitmapset.o \
        copyfuncs.o equalfuncs.o makefuncs.o \
-       outfuncs.o readfuncs.o print.o read.o value.o
+       outfuncs.o readfuncs.o print.o read.o params.o value.o
 
 all: SUBSYS.o
 
diff --git a/src/backend/nodes/params.c b/src/backend/nodes/params.c
new file mode 100644
index 0000000000..43bc40b9e1
--- /dev/null
+++ b/src/backend/nodes/params.c
@@ -0,0 +1,122 @@
+/*-------------------------------------------------------------------------
+ *
+ * params.c
+ *	  Support functions for plan parameter lists.
+ *
+ * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ *	  $PostgreSQL: pgsql/src/backend/nodes/params.c,v 1.1 2004/08/02 01:30:42 tgl Exp $
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres.h"
+
+#include "nodes/params.h"
+#include "utils/datum.h"
+#include "utils/lsyscache.h"
+
+
+/*
+ * Copy a ParamList.
+ *
+ * The result is allocated in CurrentMemoryContext.
+ */
+ParamListInfo
+copyParamList(ParamListInfo from)
+{
+	ParamListInfo retval;
+	int i, size;
+
+	if (from == NULL)
+		return NULL;
+
+	size = 0;
+	while (from[size].kind != PARAM_INVALID)
+		size++;
+
+	retval = (ParamListInfo) palloc0((size + 1) * sizeof(ParamListInfoData));
+
+	for (i = 0; i < size; i++) {
+		/* copy metadata */
+		retval[i].kind = from[i].kind;
+		if (from[i].kind == PARAM_NAMED)
+			retval[i].name = pstrdup(from[i].name);
+		retval[i].id = from[i].id;
+		retval[i].ptype = from[i].ptype;
+
+		/* copy value */
+		retval[i].isnull = from[i].isnull;
+		if (from[i].isnull)
+		{
+			retval[i].value = from[i].value; /* nulls just copy */
+		}
+		else
+		{
+			int16 typLen;
+			bool  typByVal;
+
+			get_typlenbyval(from[i].ptype, &typLen, &typByVal);
+			retval[i].value = datumCopy(from[i].value, typByVal, typLen);
+		}
+	}
+
+	retval[size].kind = PARAM_INVALID;
+
+	return retval;
+}
+
+/*
+ * Search a ParamList for a given parameter.
+ *
+ * On success, returns a pointer to the parameter's entry.
+ * On failure, returns NULL if noError is true, else ereports the error.
+ */
+ParamListInfo
+lookupParam(ParamListInfo paramList, int thisParamKind,
+			const char *thisParamName, AttrNumber thisParamId,
+			bool noError)
+{
+	if (paramList != NULL)
+	{
+		while (paramList->kind != PARAM_INVALID)
+		{
+			if (thisParamKind == paramList->kind)
+			{
+				switch (thisParamKind)
+				{
+					case PARAM_NAMED:
+						if (strcmp(paramList->name, thisParamName) == 0)
+							return paramList;
+						break;
+					case PARAM_NUM:
+						if (paramList->id == thisParamId)
+							return paramList;
+						break;
+					default:
+						elog(ERROR, "unrecognized paramkind: %d",
+							 thisParamKind);
+				}
+			}
+			paramList++;
+		}
+	}
+
+	if (!noError)
+	{
+		if (thisParamKind == PARAM_NAMED)
+			ereport(ERROR,
+					(errcode(ERRCODE_UNDEFINED_OBJECT),
+					 errmsg("no value found for parameter \"%s\"",
+							thisParamName)));
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_UNDEFINED_OBJECT),
+					 errmsg("no value found for parameter %d",
+							thisParamId)));
+	}
+
+	return NULL;
+}
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index e7088d2b76..68d2529889 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.176 2004/06/11 01:08:54 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/optimizer/util/clauses.c,v 1.177 2004/08/02 01:30:43 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -1120,39 +1120,20 @@ eval_const_expressions_mutator(Node *node,
 	if (IsA(node, Param))
 	{
 		Param	   *param = (Param *) node;
-		int			thisParamKind = param->paramkind;
 
 		/* OK to try to substitute value? */
-		if (context->estimate && thisParamKind != PARAM_EXEC &&
+		if (context->estimate && param->paramkind != PARAM_EXEC &&
 			PlannerBoundParamList != NULL)
 		{
-			ParamListInfo paramList = PlannerBoundParamList;
-			bool		matchFound = false;
+			ParamListInfo paramInfo;
 
 			/* Search to see if we've been given a value for this Param */
-			while (paramList->kind != PARAM_INVALID && !matchFound)
-			{
-				if (thisParamKind == paramList->kind)
-				{
-					switch (thisParamKind)
-					{
-						case PARAM_NAMED:
-							if (strcmp(paramList->name, param->paramname) == 0)
-								matchFound = true;
-							break;
-						case PARAM_NUM:
-							if (paramList->id == param->paramid)
-								matchFound = true;
-							break;
-						default:
-							elog(ERROR, "unrecognized paramkind: %d",
-								 thisParamKind);
-					}
-				}
-				if (!matchFound)
-					paramList++;
-			}
-			if (matchFound)
+			paramInfo = lookupParam(PlannerBoundParamList,
+									param->paramkind,
+									param->paramname,
+									param->paramid,
+									true);
+			if (paramInfo)
 			{
 				/*
 				 * Found it, so return a Const representing the param value.
@@ -1164,11 +1145,12 @@ eval_const_expressions_mutator(Node *node,
 				int16		typLen;
 				bool		typByVal;
 
+				Assert(paramInfo->ptype == param->paramtype);
 				get_typlenbyval(param->paramtype, &typLen, &typByVal);
 				return (Node *) makeConst(param->paramtype,
 										  (int) typLen,
-										  paramList->value,
-										  paramList->isnull,
+										  paramInfo->value,
+										  paramInfo->isnull,
 										  typByVal);
 			}
 		}
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 89ee40a532..bfbe0a53c5 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.427 2004/07/31 00:45:36 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.428 2004/08/02 01:30:44 tgl Exp $
  *
  * NOTES
  *	  this is the "main" module of the postgres backend and
@@ -1488,6 +1488,7 @@ exec_bind_message(StringInfo input_message)
 
 			params[i].kind = PARAM_NUM;
 			params[i].id = i + 1;
+			params[i].ptype = ptype;
 			params[i].isnull = isNull;
 
 			i++;
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index 8973cca6d2..588523f849 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/tcop/pquery.c,v 1.82 2004/07/31 00:45:36 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/tcop/pquery.c,v 1.83 2004/08/02 01:30:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -833,14 +833,14 @@ PortalRunUtility(Portal portal, Query *query,
 	if (query->canSetTag)
 	{
 		/* utility statement can override default tag string */
-		ProcessUtility(utilityStmt, dest, completionTag);
+		ProcessUtility(utilityStmt, portal->portalParams, dest, completionTag);
 		if (completionTag && completionTag[0] == '\0' && portal->commandTag)
 			strcpy(completionTag, portal->commandTag);	/* use the default */
 	}
 	else
 	{
 		/* utility added by rewrite cannot set tag */
-		ProcessUtility(utilityStmt, dest, NULL);
+		ProcessUtility(utilityStmt, portal->portalParams, dest, NULL);
 	}
 
 	/* Some utility statements may change context on us */
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index d8d718a182..b9c50d7d81 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.222 2004/08/01 17:32:18 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/tcop/utility.c,v 1.223 2004/08/02 01:30:45 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -294,6 +294,7 @@ check_xact_readonly(Node *parsetree)
  *		general utility function invoker
  *
  *	parsetree: the parse tree for the utility statement
+ *	params: parameters to use during execution (currently only used by DECLARE)
  *	dest: where to send results
  *	completionTag: points to a buffer of size COMPLETION_TAG_BUFSIZE
  *		in which to store a command completion status string.
@@ -305,6 +306,7 @@ check_xact_readonly(Node *parsetree)
  */
 void
 ProcessUtility(Node *parsetree,
+			   ParamListInfo params,
 			   DestReceiver *dest,
 			   char *completionTag)
 {
@@ -406,7 +408,7 @@ ProcessUtility(Node *parsetree,
 			 * Portal (cursor) manipulation
 			 */
 		case T_DeclareCursorStmt:
-			PerformCursorOpen((DeclareCursorStmt *) parsetree);
+			PerformCursorOpen((DeclareCursorStmt *) parsetree, params);
 			break;
 
 		case T_ClosePortalStmt:
diff --git a/src/include/commands/portalcmds.h b/src/include/commands/portalcmds.h
index 60e7d524d2..b16ef23384 100644
--- a/src/include/commands/portalcmds.h
+++ b/src/include/commands/portalcmds.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/commands/portalcmds.h,v 1.15 2004/07/17 03:30:56 tgl Exp $
+ * $PostgreSQL: pgsql/src/include/commands/portalcmds.h,v 1.16 2004/08/02 01:30:46 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -17,7 +17,7 @@
 #include "utils/portal.h"
 
 
-extern void PerformCursorOpen(DeclareCursorStmt *stmt);
+extern void PerformCursorOpen(DeclareCursorStmt *stmt, ParamListInfo params);
 
 extern void PerformPortalFetch(FetchStmt *stmt, DestReceiver *dest,
 				   char *completionTag);
diff --git a/src/include/nodes/params.h b/src/include/nodes/params.h
index e22ff27a3b..d4acbe2699 100644
--- a/src/include/nodes/params.h
+++ b/src/include/nodes/params.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/nodes/params.h,v 1.24 2003/11/29 22:41:06 pgsql Exp $
+ * $PostgreSQL: pgsql/src/include/nodes/params.h,v 1.25 2004/08/02 01:30:47 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -56,7 +56,8 @@
  *
  *		kind   : the kind of parameter (PARAM_NAMED or PARAM_NUM)
  *		name   : the parameter name (valid if kind == PARAM_NAMED)
- *		id	   : the parameter id (valid if kind == PARAM_NUM)
+ *		id     : the parameter id (valid if kind == PARAM_NUM)
+ *		ptype  : the type of the parameter value
  *		isnull : true if the value is null (if so 'value' is undefined)
  *		value  : the value that has to be substituted in the place
  *				 of the parameter.
@@ -72,6 +73,7 @@ typedef struct ParamListInfoData
 	int			kind;
 	char	   *name;
 	AttrNumber	id;
+	Oid			ptype;
 	bool		isnull;
 	Datum		value;
 } ParamListInfoData;
@@ -103,4 +105,11 @@ typedef struct ParamExecData
 	bool		isnull;
 } ParamExecData;
 
+
+/* Functions found in src/backend/nodes/params.c */
+extern ParamListInfo copyParamList(ParamListInfo from);
+extern ParamListInfo lookupParam(ParamListInfo paramList, int thisParamKind,
+								 const char *thisParamName, AttrNumber thisParamId,
+								 bool noError);
+
 #endif   /* PARAMS_H */
diff --git a/src/include/tcop/utility.h b/src/include/tcop/utility.h
index 59a9a8133d..a5398e0ded 100644
--- a/src/include/tcop/utility.h
+++ b/src/include/tcop/utility.h
@@ -7,7 +7,7 @@
  * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $PostgreSQL: pgsql/src/include/tcop/utility.h,v 1.21 2003/11/29 22:41:14 pgsql Exp $
+ * $PostgreSQL: pgsql/src/include/tcop/utility.h,v 1.22 2004/08/02 01:30:48 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -16,8 +16,9 @@
 
 #include "executor/execdesc.h"
 
-extern void ProcessUtility(Node *parsetree, DestReceiver *dest,
-			   char *completionTag);
+
+extern void ProcessUtility(Node *parsetree, ParamListInfo params,
+						   DestReceiver *dest, char *completionTag);
 
 extern bool UtilityReturnsTuples(Node *parsetree);
 
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index f075b96c0f..124d3c4bad 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -3,7 +3,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.112 2004/08/01 17:32:21 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.113 2004/08/02 01:30:49 tgl Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -3582,7 +3582,7 @@ exec_eval_simple_expr(PLpgSQL_execstate * estate,
 	 * need to have more than one active param list.
 	 */
 	paramLI = (ParamListInfo)
-		MemoryContextAlloc(econtext->ecxt_per_tuple_memory,
+		MemoryContextAllocZero(econtext->ecxt_per_tuple_memory,
 						(expr->nparams + 1) * sizeof(ParamListInfoData));
 
 	/*
@@ -3591,12 +3591,11 @@ exec_eval_simple_expr(PLpgSQL_execstate * estate,
 	for (i = 0; i < expr->nparams; i++)
 	{
 		PLpgSQL_datum *datum = estate->datums[expr->params[i]];
-		Oid			paramtypeid;
 
 		paramLI[i].kind = PARAM_NUM;
 		paramLI[i].id = i + 1;
 		exec_eval_datum(estate, datum, expr->plan_argtypes[i],
-						&paramtypeid,
+						&paramLI[i].ptype,
 						&paramLI[i].value, &paramLI[i].isnull);
 	}
 	paramLI[i].kind = PARAM_INVALID;
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index b8f8341898..a46a14ce80 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -738,3 +738,38 @@ ROLLBACK;
 -- should fail
 FETCH FROM foo26;
 ERROR:  cursor "foo26" does not exist
+--
+-- Parameterized DECLARE needs to insert param values into the cursor portal
+--
+BEGIN;
+CREATE FUNCTION declares_cursor(text)
+   RETURNS void
+   AS 'DECLARE c CURSOR FOR SELECT stringu1 FROM tenk1 WHERE stringu1 LIKE $1;'
+   LANGUAGE 'sql';
+SELECT declares_cursor('AB%');
+ declares_cursor 
+-----------------
+ 
+(1 row)
+
+FETCH ALL FROM c;
+ stringu1 
+----------
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+ ABAAAA
+(15 rows)
+
+ROLLBACK;
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index 807c847bc3..c7e29c3786 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -218,3 +218,20 @@ ROLLBACK;
 
 -- should fail
 FETCH FROM foo26;
+
+--
+-- Parameterized DECLARE needs to insert param values into the cursor portal
+--
+
+BEGIN;
+
+CREATE FUNCTION declares_cursor(text)
+   RETURNS void
+   AS 'DECLARE c CURSOR FOR SELECT stringu1 FROM tenk1 WHERE stringu1 LIKE $1;'
+   LANGUAGE 'sql';
+
+SELECT declares_cursor('AB%');
+
+FETCH ALL FROM c;
+
+ROLLBACK;
-- 
2.11.0