prep_stmt.patch

Index: src/backend/commands/Makefile
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/commands/Makefile,v
retrieving revision 1.28
diff -c -r1.28 Makefile
*** src/backend/commands/Makefile	15 Apr 2002 05:22:03 -0000	1.28
--- src/backend/commands/Makefile	28 Jun 2002 17:20:06 -0000
***************
*** 13,20 ****
  include $(top_builddir)/src/Makefile.global
  
  OBJS = aggregatecmds.o analyze.o async.o cluster.o comment.o copy.o \
! 	dbcommands.o define.o explain.o functioncmds.o \
! 	indexcmds.o lockcmds.o operatorcmds.o portalcmds.o proclang.o \
  	schemacmds.o sequence.o tablecmds.o trigger.o typecmds.o user.o \
  	vacuum.o vacuumlazy.o variable.o view.o
  
--- 13,20 ----
  include $(top_builddir)/src/Makefile.global
  
  OBJS = aggregatecmds.o analyze.o async.o cluster.o comment.o copy.o \
! 	dbcommands.o define.o explain.o functioncmds.o indexcmds.o \
! 	lockcmds.o operatorcmds.o portalcmds.o prepare.o proclang.o \
  	schemacmds.o sequence.o tablecmds.o trigger.o typecmds.o user.o \
  	vacuum.o vacuumlazy.o variable.o view.o
  
Index: src/backend/commands/prepare.c
===================================================================
RCS file: src/backend/commands/prepare.c
diff -N src/backend/commands/prepare.c
*** /dev/null	1 Jan 1970 00:00:00 -0000
--- src/backend/commands/prepare.c	28 Jun 2002 17:20:06 -0000
***************
*** 0 ****
--- 1,387 ----
+ /*-------------------------------------------------------------------------
+  *
+  * prepare.c
+  *	  Prepareable SQL statements via PREPARE, EXECUTE and DEALLOCATE
+  *
+  * Copyright (c) 2002, PostgreSQL Global Development Group
+  *
+  * IDENTIFICATION
+  *	  $Header$
+  *
+  *-------------------------------------------------------------------------
+  */
+ 
+ #include "postgres.h"
+ 
+ #include "commands/prepare.h"
+ #include "executor/executor.h"
+ #include "utils/guc.h"
+ #include "optimizer/planner.h"
+ #include "optimizer/planmain.h"
+ #include "rewrite/rewriteHandler.h"
+ #include "tcop/pquery.h"
+ #include "tcop/tcopprot.h"
+ #include "tcop/utility.h"
+ #include "utils/hsearch.h"
+ #include "utils/memutils.h"
+ 
+ #define HASH_KEY_LEN NAMEDATALEN
+ 
+ typedef struct
+ {
+ 	char		 key[HASH_KEY_LEN];
+ 	QueryData	*data;
+ } HashEntry;
+ 
+ /*
+  * The hash table in which prepared queries are stored. This is
+  * per-backend: query plans are not shared between backends.
+  * The keys for this hash table are the arguments to PREPARE
+  * and EXECUTE ("plan names"); the values are QueryData structs.
+  */
+ static HTAB *prepared_queries = NULL;
+ 
+ static void StoreQuery(const char *plan_name, QueryData *query_data);
+ static void InitHashTable(void);
+ static void RunQuery(QueryDesc *qdesc, EState *state);
+ 
+ void
+ PrepareQuery(PrepareStmt *stmt)
+ {
+ 	QueryData query_data;
+ 	List *plan_list = NIL;
+ 	List *query_list,
+ 		 *query_list_item;
+ 
+ 	if (!stmt->name)
+ 		elog(ERROR, "No statement name given.");
+ 
+ 	if (stmt->query->commandType == CMD_UTILITY)
+ 		elog(ERROR, "Utility statements cannot be prepared.");
+ 
+ 	/* Rewrite the query. The result could be 0, 1, or many queries. */
+ 	query_list = QueryRewrite(stmt->query);
+ 
+ 	foreach(query_list_item, query_list)
+ 	{
+ 		Plan  *plan;
+ 		Query *query = (Query *) lfirst(query_list_item);
+ 
+ 		/* We can't generate plans for utility statements. */
+ 		if (query->commandType == CMD_UTILITY)
+ 			continue;
+ 
+ 		/* Call the query planner to generate a plan. */
+ 		plan = planner(query);
+ 
+ 		plan_list = lappend(plan_list, plan);
+ 	}
+ 
+ 	query_data.plan_list = plan_list;
+ 	query_data.query_list = query_list;
+ 	query_data.nargs = stmt->nargs;
+ 	query_data.argtypes = stmt->argtoids;
+ 
+ 	StoreQuery(stmt->name, &query_data);
+ }
+ 
+ /*
+  * Store all the data pertaining to the query in the hash table using
+  * the specified key. A copy of the data is made before storage, so the
+  * caller can dispose of their copy.
+  */
+ static void
+ StoreQuery(const char *stmt_name, QueryData *query_data)
+ {
+ 	bool found;
+ 	HashEntry *entry;
+ 	QueryData *data;
+ 	MemoryContext oldcxt,
+ 				  entrycxt;
+ 	char key[HASH_KEY_LEN];
+ 
+ 	MemSet(key, 0, sizeof(key));
+ 	strncpy(key, stmt_name, sizeof(key));
+ 
+ 	/* Initialize the hash table, if necessary */
+ 	if (!prepared_queries)
+ 		InitHashTable();
+ 
+ 	hash_search(prepared_queries, key, HASH_FIND, &found);
+ 
+ 	if (found)
+ 		elog(ERROR, "Prepared statement with name \"%s\" already exists.", stmt_name);
+ 
+ 	entrycxt = AllocSetContextCreate(TopMemoryContext,
+ 									 stmt_name,
+ 									 1024,
+ 									 1024,
+ 									 ALLOCSET_DEFAULT_MAXSIZE);
+ 
+ 	oldcxt = MemoryContextSwitchTo(entrycxt);
+ 
+ 	/*
+ 	 * Create the hash table entry. We need to copy the data so that
+ 	 * it is stored in the correct memory context.
+ 	 */
+ 	data = (QueryData *) palloc(sizeof(QueryData));
+ 
+ 	data->context = entrycxt;
+ 	data->nargs = query_data->nargs;
+ 	data->plan_list = (List *) copyObject(query_data->plan_list);
+ 	data->query_list = (List *) copyObject(query_data->query_list);
+ 
+ 	if (data->nargs)
+ 	{
+ 		int mem_size = sizeof(Oid) * data->nargs;
+ 		data->argtypes = (Oid *) palloc(mem_size);
+ 		memcpy(data->argtypes, query_data->argtypes, mem_size);
+ 	}
+ 	else
+ 		data->argtypes = NULL;
+ 
+ 	/* Add entry to hash table */
+ 	MemoryContextSwitchTo(TopMemoryContext);
+ 
+ 	entry = (HashEntry *) hash_search(prepared_queries,
+ 									  key,
+ 									  HASH_ENTER,
+ 									  NULL);
+ 
+ 	if (!entry)
+ 		elog(ERROR, "Unable to store prepared statement \"%s\"!", stmt_name);
+ 
+ 	data->key = entry->key;
+ 	entry->data = data;
+ 
+ 	MemoryContextSwitchTo(oldcxt);
+ }
+ 
+ static void
+ InitHashTable(void)
+ {
+ 	HASHCTL hash_ctl;
+ 
+ 	MemSet(&hash_ctl, 0, sizeof(hash_ctl));
+ 
+ 	hash_ctl.keysize = HASH_KEY_LEN;
+ 	hash_ctl.entrysize = sizeof(HashEntry);
+ 
+ 	prepared_queries = hash_create("Prepared Queries",
+ 								   8,
+ 								   &hash_ctl,
+ 								   HASH_ELEM);
+ 
+ 	if (!prepared_queries)
+ 		elog(ERROR, "InitHashTable(): unable to create hash table.");
+ }
+ 
+ /*
+  * Implements the 'EXECUTE' utility statement.
+  */
+ void
+ ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest)
+ {
+ 	ParamListInfo paramLI = NULL;
+ 	QueryData	*qdata;
+ 	List		*l,
+ 				*query_list,
+ 				*plan_list;
+ 
+ 	qdata = FetchQuery(stmt->name);
+ 
+ 	if (qdata->nargs)
+ 	{
+ 		List *l;
+ 		bool isNull;
+ 		int i = 0;
+ 
+ 		ExprContext *econtext = MakeExprContext(NULL, CurrentMemoryContext);
+ 
+ 		paramLI = (ParamListInfo) palloc(sizeof(ParamListInfoData) * qdata->nargs);
+ 
+ 		foreach (l, stmt->params)
+ 		{
+ 			Node *n = lfirst(l);
+ 			fix_opids(n);
+ 
+ 			paramLI[i].value = ExecEvalExprSwitchContext(n, econtext, &isNull, NULL);
+ 			paramLI[i].kind = PARAM_NUM;
+ 			paramLI[i].id = i + 1;
+ 			paramLI[i].isnull = isNull;
+ 
+ 			i++;
+ 		}
+ 
+ 		pfree(econtext);
+ 	}
+ 
+ 	query_list = qdata->query_list;
+ 	plan_list = qdata->plan_list;
+ 
+ 	Assert(length(query_list) == length(plan_list));
+ 
+ 	foreach(l, query_list)
+ 	{
+ 		CommandDest dest;
+ 		bool is_last_query;
+ 		Query *query = lfirst(l);
+ 		Plan *plan = lfirst(plan_list);
+ 		plan_list = lnext(plan_list);
+ 
+ 		is_last_query = (plan_list == NIL);
+ 
+ 		/*
+ 		 * We only send the output to the final destination for the final
+ 		 * query we execute.
+ 		 */
+ 		dest = (is_last_query ? outputDest : None);
+ 
+ 		if (query->commandType == CMD_UTILITY)
+ 			ProcessUtility(query->utilityStmt, dest, NULL);
+ 		else
+ 		{
+ 			if (Show_executor_stats)
+ 				ResetUsage();
+ 
+ 			QueryDesc *qdesc = CreateQueryDesc(query, plan, dest, NULL);
+ 			EState *state = CreateExecutorState();
+ 
+ 			state->es_param_list_info = qdata->nargs ? paramLI : NULL;
+ 
+ 			if (stmt->into)
+ 			{
+ 				if (qdesc->operation != CMD_SELECT)
+ 					elog(ERROR, "INTO clause specified for non-SELECT query.");
+ 
+ 				query->into = stmt->into;
+ 				qdesc->dest = None;
+ 			}
+ 
+ 			RunQuery(qdesc, state);
+ 
+ 			if (Show_executor_stats)
+ 				ShowUsage("EXECUTOR STATISTICS");
+ 		}
+ 
+ 		/*
+ 		 * If we're processing multiple queries, we need to increment
+ 		 * the command counter between them. For the last query,
+ 		 * there's no need to do this, it's done automatically.
+ 		 */
+ 		if (! is_last_query)
+ 			CommandCounterIncrement();
+ 	}
+ 
+ 	if (paramLI)
+ 		pfree(paramLI);
+ 	pfree(qdata);
+ }
+ 
+ /*
+  * Fetch all data on the given prepared statement from the hash table in
+  * which it is stored.
+  */
+ QueryData *
+ FetchQuery(const char *plan_name)
+ {
+ 	QueryData *result;
+ 	HashEntry *entry;
+ 
+ 	/* See notes in DeallocateQuery() */
+ 	char key[HASH_KEY_LEN];
+ 
+ 	MemSet(key, 0, HASH_KEY_LEN);
+ 	strncpy(key, plan_name, HASH_KEY_LEN);
+ 
+ 	/*
+ 	 * If the hash table hasn't been initialized, it can't be storing
+ 	 * anything, therefore it couldn't possibly store our plan.
+ 	 */
+ 	if (!prepared_queries)
+ 		elog(ERROR, "Prepared statement with name \"%s\" does not exist", plan_name);
+ 
+ 	entry = (HashEntry *) hash_search(prepared_queries,
+ 									  key,
+ 									  HASH_FIND,
+ 									  NULL);
+ 
+ 	if (!entry)
+ 		elog(ERROR, "Prepared statement with name \"%s\" does not exist", plan_name);
+ 
+ 	result = palloc(sizeof(QueryData));
+ 
+ 	result->key = entry->data->key;
+ 	result->nargs = entry->data->nargs;
+ 	result->plan_list = (List *) copyObject(entry->data->plan_list);
+ 	result->query_list = (List *) copyObject(entry->data->query_list);
+ 	result->context = entry->data->context;
+ 
+ 	if (result->nargs)
+ 	{
+ 		int mem_size = sizeof(Oid) * result->nargs;
+ 		result->argtypes = (Oid *) palloc(mem_size);
+ 		memcpy(result->argtypes, entry->data->argtypes, mem_size);
+ 	}
+ 	else
+ 		result->argtypes = NULL;
+ 
+ 	return result;
+ }
+ 
+ /*
+  * Actually execute a prepared query. We can't use any of the existing
+  * routines in tcop/postgres.c because we need to manipulate part of the
+  * Executor (e.g. to inform it of parameters to execute) -- in any case,
+  * not much code is duplicated.
+  */
+ static void
+ RunQuery(QueryDesc *qdesc, EState *state)
+ {
+ 	TupleDesc tupdesc;
+ 
+ 	tupdesc = ExecutorStart(qdesc, state);
+ 
+ 	BeginCommand(NULL, qdesc->dest);
+ 
+ 	ExecutorRun(qdesc, state, state->es_direction, 0L);
+ 
+ 	ExecutorEnd(qdesc, state);
+ }
+ 
+ /*
+  * Implements the 'DEALLOCATE' utility statement: deletes the
+  * specified plan from storage.
+  */
+ void
+ DeallocateQuery(DeallocateStmt *stmt)
+ {
+ 	/*
+ 	 * We can't just use the statement name as supplied by the user: the
+ 	 * hash package is picky enough that it needs to be NULL-padded out
+ 	 * to the appropriate length to work correctly.
+ 	 */
+ 	char key[HASH_KEY_LEN];
+ 	HashEntry *entry;
+ 
+ 	MemSet(key, 0, HASH_KEY_LEN);
+ 	strncpy(key, stmt->name, HASH_KEY_LEN);
+ 
+ 	/*
+ 	 * First lookup the entry, so we can release some of the memory
+ 	 * it has allocated (when it's removed, hash_search() will return
+ 	 * a dangling pointer, so it needs to be done prior to HASH_REMOVE).
+ 	 * This requires an extra hash-table lookup, but DEALLOCATE
+ 	 * isn't exactly a performance bottleneck.
+ 	 */
+ 	entry = hash_search(prepared_queries, key, HASH_FIND, NULL);
+ 
+ 	if (!entry)
+ 		elog(ERROR, "No plan found with name \"%s\"", stmt->name);
+ 
+ 	if (entry->data && MemoryContextIsValid(entry->data->context))
+ 		MemoryContextDelete(entry->data->context);
+ 
+ 	/* Okay, now we can remove the hash table entry */
+ 	hash_search(prepared_queries, key, HASH_REMOVE, NULL);
+ }
Index: src/backend/parser/analyze.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/analyze.c,v
retrieving revision 1.237
diff -c -r1.237 analyze.c
*** src/backend/parser/analyze.c	20 Jun 2002 20:29:31 -0000	1.237
--- src/backend/parser/analyze.c	28 Jun 2002 17:20:06 -0000
***************
*** 20,25 ****
--- 20,26 ----
  #include "catalog/namespace.h"
  #include "catalog/pg_index.h"
  #include "catalog/pg_type.h"
+ #include "commands/prepare.h"
  #include "nodes/makefuncs.h"
  #include "parser/analyze.h"
  #include "parser/gramparse.h"
***************
*** 43,49 ****
  #include "mb/pg_wchar.h"
  #endif
  
- 
  /* State shared by transformCreateSchemaStmt and its subroutines */
  typedef struct
  {
--- 44,49 ----
***************
*** 94,99 ****
--- 94,101 ----
  static Query *transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt);
  static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt);
  static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt);
+ static Query *transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt);
+ static Query *transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt);
  static Query *transformCreateStmt(ParseState *pstate, CreateStmt *stmt,
  						List **extras_before, List **extras_after);
  static Query *transformAlterTableStmt(ParseState *pstate, AlterTableStmt *stmt,
***************
*** 280,285 ****
--- 282,295 ----
  										extras_before, extras_after);
  			break;
  
+ 		case T_PrepareStmt:
+ 			result = transformPrepareStmt(pstate, (PrepareStmt *) parseTree);
+ 			break;
+ 
+ 		case T_ExecuteStmt:
+ 			result = transformExecuteStmt(pstate, (ExecuteStmt *) parseTree);
+ 			break;
+ 
  			/*
  			 * Optimizable statements
  			 */
***************
*** 2651,2656 ****
--- 2661,2784 ----
  	qry->utilityStmt = (Node *) stmt;
  
  	return qry;
+ }
+ 
+ static Query *
+ transformPrepareStmt(ParseState *pstate, PrepareStmt *stmt)
+ {
+ 	Query	*result = makeNode(Query);
+ 	List	*extras_before	= NIL,
+ 			*extras_after	= NIL;
+ 	Oid		*argtoids = NULL;
+ 
+ 	stmt->nargs = length(stmt->argtypes);
+ 
+ 	if (stmt->nargs)
+ 	{
+ 		List *l;
+ 		int i = 0;
+ 
+ 		argtoids = palloc(sizeof(*argtoids) * stmt->nargs);
+ 
+ 		foreach (l, stmt->argtypes)
+ 		{
+ 			TypeName *tn = lfirst(l);
+ 			Oid toid = typenameTypeId(tn);
+ 
+ 			if (!OidIsValid(toid))
+ 			{
+ 				elog(ERROR, "Argument $%d has invalid type \"%s\"",
+ 					 i + 1, TypeNameToString(tn));
+ 			}
+ 
+ 			argtoids[i++] = toid;
+ 		}
+ 	}
+ 
+ 	/*
+ 	 * We need to adjust the number of parameters expected by the
+ 	 * rest of the system, so that $1, ... $n are parsed properly.
+ 	 * This is somewhat of a hack; however, the existing interfaces
+ 	 * only allow parameters to be specified when working with a
+ 	 * raw query string (parser(), pg_parse_query(), etc.), which
+ 	 * can't be used here.
+ 	 */
+ 	parser_param_init(argtoids, stmt->nargs);
+ 
+ 	stmt->argtoids = argtoids;
+ 
+ 	stmt->query = transformStmt(pstate, (Node *) stmt->query,
+ 								&extras_before, &extras_after);
+ 
+ 	result->commandType = CMD_UTILITY;
+ 	result->utilityStmt = (Node *) stmt;
+ 
+ 	return result;
+ }
+ 
+ static Query *
+ transformExecuteStmt(ParseState *pstate, ExecuteStmt *stmt)
+ {
+ 	Query *result = makeNode(Query);
+ 	QueryData *qdata;
+ 
+ 	result->commandType = CMD_UTILITY;
+ 	result->utilityStmt = (Node *) stmt;
+ 
+ 	qdata = FetchQuery(stmt->name);
+ 
+ 	if (!qdata)
+ 		elog(ERROR, "No such prepared statement: %s", stmt->name);
+ 
+ 	if (stmt->params || qdata->nargs)
+ 	{
+ 		List *l;
+ 		Oid expected_type_id,
+ 			given_type_id;
+ 		int i = 0;
+ 		int nparams = length(stmt->params);
+ 
+ 		if (nparams != qdata->nargs)
+ 		{
+ 			elog(ERROR, "Wrong number of parameters, expected %d but got %d",
+ 				 qdata->nargs, nparams);
+ 		}
+ 
+ 		foreach (l, stmt->params)
+ 		{
+ 			Node *expr = lfirst(l);
+ 
+ 			expr = transformExpr(pstate, expr);
+ 
+ 			given_type_id = exprType(expr);
+ 			expected_type_id = qdata->argtypes[i];
+ 
+ 			if (given_type_id != expected_type_id)
+ 			{
+ 				expr = CoerceTargetExpr(pstate,
+ 										expr,
+ 										given_type_id,
+ 										expected_type_id,
+ 										-1,
+ 										false);
+ 
+ 				if (!expr)
+ 				{
+ 					elog(ERROR, "Parameter $%d of type \"%s\" cannot be "
+ 								"coerced into the expected type (\"%s\").\n\t"
+ 								"You will need to rewrite or cast the expression.",
+ 						 i + 1,
+ 						 typeidToString(given_type_id),
+ 						 typeidToString(expected_type_id));
+ 				}
+ 			}
+ 
+ 			lfirst(l) = expr;
+ 			i++;
+ 		}
+ 	}
+ 
+ 	return result;
  }
  
  /* exported so planner can check again after rewriting, query pullup, etc */
Index: src/backend/parser/gram.y
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.334
diff -c -r2.334 gram.y
*** src/backend/parser/gram.y	22 Jun 2002 02:04:45 -0000	2.334
--- src/backend/parser/gram.y	28 Jun 2002 17:20:06 -0000
***************
*** 152,158 ****
  		SelectStmt, TransactionStmt, TruncateStmt,
  		UnlistenStmt, UpdateStmt, VacuumStmt,
  		VariableResetStmt, VariableSetStmt, VariableShowStmt,
! 		ViewStmt, CheckPointStmt
  
  %type <node>	select_no_parens, select_with_parens, select_clause,
  				simple_select
--- 152,159 ----
  		SelectStmt, TransactionStmt, TruncateStmt,
  		UnlistenStmt, UpdateStmt, VacuumStmt,
  		VariableResetStmt, VariableSetStmt, VariableShowStmt,
! 		ViewStmt, CheckPointStmt, DeallocateStmt, ExecuteStmt,
! 		PrepareStmt
  
  %type <node>	select_no_parens, select_with_parens, select_clause,
  				simple_select
***************
*** 217,223 ****
  				target_list, update_target_list, insert_column_list,
  				insert_target_list, def_list, opt_indirection,
  				group_clause, TriggerFuncArgs, select_limit,
! 				opt_select_limit
  
  %type <range>	into_clause, OptTempTableName
  
--- 218,225 ----
  				target_list, update_target_list, insert_column_list,
  				insert_target_list, def_list, opt_indirection,
  				group_clause, TriggerFuncArgs, select_limit,
! 				opt_select_limit, execute_using, prepare_type_clause,
! 				prepare_type_list
  
  %type <range>	into_clause, OptTempTableName
  
***************
*** 333,339 ****
  	CREATEUSER, CROSS, CURRENT_DATE, CURRENT_TIME,
  	CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE,
  
! 	DATABASE, DAY_P, DEC, DECIMAL, DECLARE, DEFAULT,
  	DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS,
      DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP,
  
--- 335,341 ----
  	CREATEUSER, CROSS, CURRENT_DATE, CURRENT_TIME,
  	CURRENT_TIMESTAMP, CURRENT_USER, CURSOR, CYCLE,
  
! 	DATABASE, DAY_P, DEALLOCATE, DEC, DECIMAL, DECLARE, DEFAULT,
  	DEFERRABLE, DEFERRED, DEFINER, DELETE_P, DELIMITER, DELIMITERS,
      DESC, DISTINCT, DO, DOMAIN_P, DOUBLE, DROP,
  
***************
*** 369,375 ****
  	ORDER, OUT_P, OUTER_P, OVERLAPS, OVERLAY, OWNER,
  
  	PARTIAL, PASSWORD, PATH_P, PENDANT, PLACING, POSITION,
! 	PRECISION, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE,
  	PROCEDURAL,
  
  	READ, REAL, REFERENCES, REINDEX, RELATIVE, RENAME, REPLACE,
--- 371,377 ----
  	ORDER, OUT_P, OUTER_P, OVERLAPS, OVERLAY, OWNER,
  
  	PARTIAL, PASSWORD, PATH_P, PENDANT, PLACING, POSITION,
! 	PRECISION, PREPARE, PRIMARY, PRIOR, PRIVILEGES, PROCEDURE,
  	PROCEDURAL,
  
  	READ, REAL, REFERENCES, REINDEX, RELATIVE, RENAME, REPLACE,
***************
*** 492,503 ****
--- 494,507 ----
  			| DropSchemaStmt
  			| TruncateStmt
  			| CommentStmt
+ 			| DeallocateStmt
  			| DropGroupStmt
  			| DropPLangStmt
  			| DropAssertStmt
  			| DropTrigStmt
  			| DropRuleStmt
  			| DropUserStmt
+ 			| ExecuteStmt
  			| ExplainStmt
  			| FetchStmt
  			| GrantStmt
***************
*** 526,531 ****
--- 530,536 ----
  			| VariableResetStmt
  			| ConstraintsSetStmt
  			| CheckPointStmt
+ 			| PrepareStmt
  			| /*EMPTY*/
  				{ $$ = (Node *)NULL; }
  		;
***************
*** 3803,3808 ****
--- 3808,3882 ----
  				}
  		;
  
+ /*****************************************************************************
+  *
+  *		QUERY:
+  *				PREPARE <plan_name> FROM <query>
+  *
+  *****************************************************************************/
+ 
+ PrepareStmt: PREPARE name prepare_type_clause FROM OptimizableStmt
+ 				{
+ 					PrepareStmt *n = makeNode(PrepareStmt);
+ 					n->name = $2;
+ 					n->argtypes = $3;
+ 					n->query = (Query *) $5;
+ 					$$ = (Node *) n;
+ 				}
+ 		;
+ 
+ prepare_type_clause:
+ 				 '(' prepare_type_list ')'	{ $$ = $2; }
+ 				| /* EMPTY */				{ $$ = NIL; }
+ 		;
+ 
+ prepare_type_list:
+ 				  Typename				{ $$ = makeList1($1); }
+ 				| prepare_type_list ',' Typename
+ 										{ $$ = lappend($1, $3); }
+ 				| /* EMPTY */			{ $$ = NIL; }
+ 		;
+ 
+ /*****************************************************************************
+  *
+  *		QUERY:
+  *				EXECUTE <plan_name> [INTO ...] [USING expr, ...]
+  *
+  *****************************************************************************/
+ 
+ ExecuteStmt: EXECUTE name into_clause execute_using
+ 				{
+ 					ExecuteStmt *n = makeNode(ExecuteStmt);
+ 					n->name = $2;
+ 					n->into = $3;
+ 					n->params = $4;
+ 					$$ = (Node *) n;
+ 				}
+ 		;
+ 
+ execute_using: USING expr_list		{ $$ = $2; }
+ 		| /* EMPTY */				{ $$ = NULL; }
+ 		;
+ 
+ /*****************************************************************************
+  *
+  *		QUERY:
+  *				DEALLOCATE <plan_name>
+  *
+  *****************************************************************************/
+ 
+ DeallocateStmt: DEALLOCATE opt_prepare name
+ 					{
+ 						DeallocateStmt *n = makeNode(DeallocateStmt);
+ 						n->name = $3;
+ 						$$ = (Node *) n;
+ 					}
+ 		;
+ 
+ /* Ignored, for SQL92 spec compliance */
+ opt_prepare: PREPARE		{}
+ 		| /* EMPTY */		{}
+ 		;
  
  /*****************************************************************************
   *																			 *
***************
*** 7176,7181 ****
--- 7250,7266 ----
  	 * Keep enough information around to fill out the type of param nodes
  	 * used in postquel functions
  	 */
+ 	parser_param_init(typev, nargs);
+ }
+ 
+ /*
+  * Do the initialization required for processing parameters. This
+  * is also called by transformPrepareStmt() to update the parser's
+  * view of the parameters in a prepared statement.
+  */
+ void
+ parser_param_init(Oid *typev, int nargs)
+ {
  	param_type_info = typev;
  	pfunc_num_args = nargs;
  }
Index: src/backend/parser/keywords.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.117
diff -c -r1.117 keywords.c
*** src/backend/parser/keywords.c	22 Jun 2002 02:04:45 -0000	1.117
--- src/backend/parser/keywords.c	28 Jun 2002 17:20:06 -0000
***************
*** 91,96 ****
--- 91,97 ----
  	{"cycle", CYCLE},
  	{"database", DATABASE},
  	{"day", DAY_P},
+ 	{"deallocate", DEALLOCATE},
  	{"dec", DEC},
  	{"decimal", DECIMAL},
  	{"declare", DECLARE},
***************
*** 225,230 ****
--- 226,232 ----
  	{"placing", PLACING},
  	{"position", POSITION},
  	{"precision", PRECISION},
+ 	{"prepare", PREPARE},
  	{"primary", PRIMARY},
  	{"prior", PRIOR},
  	{"privileges", PRIVILEGES},
Index: src/backend/parser/parse_type.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/parser/parse_type.c,v
retrieving revision 1.43
diff -c -r1.43 parse_type.c
*** src/backend/parser/parse_type.c	20 Jun 2002 20:29:33 -0000	1.43
--- src/backend/parser/parse_type.c	28 Jun 2002 17:20:06 -0000
***************
*** 277,282 ****
--- 277,295 ----
  	return (Type) tup;
  }
  
+ /* given a Type structure, return the name of the type in string form */
+ char *
+ typeidToString(Oid id)
+ {
+ 	Type type = typeidType(id);
+ 
+ 	char *name = typeTypeName(type);
+ 
+ 	ReleaseSysCache((HeapTuple) type);
+ 
+ 	return name;
+ }
+ 
  /* given type (as type struct), return the type OID */
  Oid
  typeTypeId(Type tp)
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.268
diff -c -r1.268 postgres.c
*** src/backend/tcop/postgres.c	20 Jun 2002 20:29:36 -0000	1.268
--- src/backend/tcop/postgres.c	28 Jun 2002 17:20:06 -0000
***************
*** 2426,2431 ****
--- 2426,2443 ----
  			tag = "REINDEX";
  			break;
  
+ 		case T_ExecuteStmt:
+ 			tag = "EXECUTE";
+ 			break;
+ 
+ 		case T_PrepareStmt:
+ 			tag = "PREPARE";
+ 			break;
+ 
+ 		case T_DeallocateStmt:
+ 			tag = "DEALLOCATE";
+ 			break;
+ 
  		default:
  			elog(LOG, "CreateCommandTag: unknown parse node type %d",
  				 nodeTag(parsetree));
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /var/lib/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.159
diff -c -r1.159 utility.c
*** src/backend/tcop/utility.c	20 Jun 2002 20:29:36 -0000	1.159
--- src/backend/tcop/utility.c	28 Jun 2002 17:20:06 -0000
***************
*** 29,34 ****
--- 29,35 ----
  #include "commands/explain.h"
  #include "commands/lockcmds.h"
  #include "commands/portalcmds.h"
+ #include "commands/prepare.h"
  #include "commands/proclang.h"
  #include "commands/schemacmds.h"
  #include "commands/sequence.h"
***************
*** 351,356 ****
--- 352,375 ----
  					SetQuerySnapshot();
  
  				DoCopy(stmt);
+ 			}
+ 			break;
+ 
+ 		case T_PrepareStmt:
+ 			{
+ 				PrepareQuery((PrepareStmt *) parsetree);
+ 			}
+ 			break;
+ 
+ 		case T_ExecuteStmt:
+ 			{
+ 				ExecuteQuery((ExecuteStmt *) parsetree, dest);
+ 			}
+ 			break;
+ 
+ 		case T_DeallocateStmt:
+ 			{
+ 				DeallocateQuery((DeallocateStmt *) parsetree);
  			}
  			break;
  
Index: src/include/commands/prepare.h
===================================================================
RCS file: src/include/commands/prepare.h
diff -N src/include/commands/prepare.h
*** /dev/null	1 Jan 1970 00:00:00 -0000
--- src/include/commands/prepare.h	28 Jun 2002 17:20:06 -0000
***************
*** 0 ****
--- 1,26 ----
+ #ifndef _PREPARE_H
+ #define _PREPARE_H
+ 
+ #include "nodes/parsenodes.h"
+ #include "tcop/dest.h"
+ 
+ /* All the data we need to execute a stored query */
+ typedef struct
+ {
+ 	char			*key;
+ 	List			*plan_list;
+ 	List			*query_list;
+ 	int				 nargs;
+ 	Oid				*argtypes;
+ 	MemoryContext	 context;
+ } QueryData;
+ 
+ extern void PrepareQuery(PrepareStmt *stmt);
+ 
+ extern void ExecuteQuery(ExecuteStmt *stmt, CommandDest outputDest);
+ 
+ extern void DeallocateQuery(DeallocateStmt *stmt);
+ 
+ extern QueryData *FetchQuery(const char *plan_name);
+ 
+ #endif
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.109
diff -c -r1.109 nodes.h
*** src/include/nodes/nodes.h	20 Jun 2002 20:29:51 -0000	1.109
--- src/include/nodes/nodes.h	28 Jun 2002 17:20:06 -0000
***************
*** 198,203 ****
--- 198,206 ----
  	T_CreateSchemaStmt,
  	T_AlterDatabaseSetStmt,
  	T_AlterUserSetStmt,
+ 	T_DeallocateStmt,
+ 	T_ExecuteStmt,
+ 	T_PrepareStmt,
  
  	T_A_Expr = 700,
  	T_ColumnRef,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.182
diff -c -r1.182 parsenodes.h
*** src/include/nodes/parsenodes.h	20 Jun 2002 20:29:51 -0000	1.182
--- src/include/nodes/parsenodes.h	28 Jun 2002 17:20:06 -0000
***************
*** 1494,1497 ****
--- 1494,1536 ----
  	bool		all;
  } ReindexStmt;
  
+ /* ----------------------
+  *		PREPARE Statement
+  * ----------------------
+  */
+ typedef struct PrepareStmt
+ {
+ 	NodeTag		 type;
+ 	char		*name;			/* Name of plan, arbitrary */
+ 	int			 nargs;			/* # of parameters */
+ 	List		*argtypes;		/* Types of parameters (TypeNames) */
+ 	Oid			*argtoids;		/* The OIDs of the param types */
+ 	Query		*query;			/* The query itself */
+ } PrepareStmt;
+ 
+ 
+ /* ----------------------
+  *		EXECUTE Statement
+  * ----------------------
+  */
+ 
+ typedef struct ExecuteStmt
+ {
+ 	NodeTag		 type;
+ 	char		*name;			/* The name of the plan to execute */
+ 	RangeVar	*into;			/* The relation to store the results in */
+ 	List		*params;		/* Values of parameters */
+ } ExecuteStmt;
+ 
+ 
+ /* ----------------------
+  *		DEALLOCATE Statement
+  * ----------------------
+  */
+ typedef struct DeallocateStmt
+ {
+ 	NodeTag		type;
+ 	char	   *name;			/* The name of the plan to remove */
+ } DeallocateStmt;
+ 
  #endif   /* PARSENODES_H */
Index: src/include/parser/gramparse.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/parser/gramparse.h,v
retrieving revision 1.23
diff -c -r1.23 gramparse.h
*** src/include/parser/gramparse.h	20 Jun 2002 20:29:51 -0000	1.23
--- src/include/parser/gramparse.h	28 Jun 2002 17:20:06 -0000
***************
*** 29,34 ****
--- 29,35 ----
  
  /* from gram.y */
  extern void parser_init(Oid *typev, int nargs);
+ extern void parser_param_init(Oid *typev, int nargs);
  extern Oid	param_type(int t);
  extern int	yyparse(void);
  extern List *SystemFuncName(char *name);
Index: src/include/parser/parse_type.h
===================================================================
RCS file: /var/lib/cvs/pgsql/src/include/parser/parse_type.h,v
retrieving revision 1.23
diff -c -r1.23 parse_type.h
*** src/include/parser/parse_type.h	20 Jun 2002 20:29:52 -0000	1.23
--- src/include/parser/parse_type.h	28 Jun 2002 17:20:06 -0000
***************
*** 27,32 ****
--- 27,33 ----
  
  extern bool typeidIsValid(Oid id);
  extern Type typeidType(Oid id);
+ extern char *typeidToString(Oid id);
  
  extern Oid	typeTypeId(Type tp);
  extern int16 typeLen(Type t);
Index: src/test/regress/parallel_schedule
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/parallel_schedule,v
retrieving revision 1.10
diff -c -r1.10 parallel_schedule
*** src/test/regress/parallel_schedule	20 Jun 2002 17:09:42 -0000	1.10
--- src/test/regress/parallel_schedule	28 Jun 2002 17:20:07 -0000
***************
*** 74,77 ****
  # The sixth group of parallel test
  # ----------
  # "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs
--- 74,77 ----
  # The sixth group of parallel test
  # ----------
  # "plpgsql" cannot run concurrently with "rules"
! test: limit plpgsql temp domain rangefuncs prepare
Index: src/test/regress/serial_schedule
===================================================================
RCS file: /var/lib/cvs/pgsql/src/test/regress/serial_schedule,v
retrieving revision 1.10
diff -c -r1.10 serial_schedule
*** src/test/regress/serial_schedule	20 Jun 2002 17:09:42 -0000	1.10
--- src/test/regress/serial_schedule	28 Jun 2002 17:20:07 -0000
***************
*** 83,85 ****
--- 83,86 ----
  test: temp
  test: domain
  test: rangefuncs
+ test: prepare
Index: src/test/regress/expected/prepare.out
===================================================================
RCS file: src/test/regress/expected/prepare.out
diff -N src/test/regress/expected/prepare.out
*** /dev/null	1 Jan 1970 00:00:00 -0000
--- src/test/regress/expected/prepare.out	28 Jun 2002 17:20:07 -0000
***************
*** 0 ****
--- 1,107 ----
+ -- Regression tests for prepareable statements
+ PREPARE q1 FROM SELECT 1;
+ EXECUTE q1;
+  ?column? 
+ ----------
+         1
+ (1 row)
+ 
+ -- should fail
+ PREPARE q1 FROM SELECT 2;
+ ERROR:  Prepared statement with name "q1" already exists.
+ -- should succeed
+ DEALLOCATE q1;
+ PREPARE q1 FROM SELECT 2;
+ EXECUTE q1;
+  ?column? 
+ ----------
+         2
+ (1 row)
+ 
+ -- sql92 syntax
+ DEALLOCATE PREPARE q1;
+ -- parametized queries
+ PREPARE q2(text) FROM
+ 	SELECT datname, datdba, datistemplate, datallowconn
+ 	FROM pg_database WHERE datname = $1;
+ EXECUTE q2 USING 'regression';
+   datname   | datdba | datistemplate | datallowconn 
+ ------------+--------+---------------+--------------
+  regression |      1 | f             | t
+ (1 row)
+ 
+ PREPARE q3(text, int, float, boolean, oid, smallint) FROM
+ 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+ 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+ EXECUTE q3 USING 'AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint;
+  unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+     4502 |     412 |   0 |    2 |   2 |      2 |       2 |      502 |         502 |      4502 |     4502 |   4 |    5 | ERAAAA   | WPAAAA   | AAAAxx
+      102 |     612 |   0 |    2 |   2 |      2 |       2 |      102 |         102 |       102 |      102 |   4 |    5 | YDAAAA   | OXAAAA   | AAAAxx
+     7602 |    1040 |   0 |    2 |   2 |      2 |       2 |      602 |        1602 |      2602 |     7602 |   4 |    5 | KGAAAA   | AOBAAA   | AAAAxx
+      902 |    1104 |   0 |    2 |   2 |      2 |       2 |      902 |         902 |       902 |      902 |   4 |    5 | SIAAAA   | MQBAAA   | AAAAxx
+     4902 |    1600 |   0 |    2 |   2 |      2 |       2 |      902 |         902 |      4902 |     4902 |   4 |    5 | OGAAAA   | OJCAAA   | AAAAxx
+     9502 |    1812 |   0 |    2 |   2 |      2 |       2 |      502 |        1502 |      4502 |     9502 |   4 |    5 | MBAAAA   | SRCAAA   | AAAAxx
+     4702 |    2520 |   0 |    2 |   2 |      2 |       2 |      702 |         702 |      4702 |     4702 |   4 |    5 | WYAAAA   | YSDAAA   | AAAAxx
+     1002 |    2580 |   0 |    2 |   2 |      2 |       2 |        2 |        1002 |      1002 |     1002 |   4 |    5 | OMAAAA   | GVDAAA   | AAAAxx
+        2 |    2716 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |         2 |        2 |   4 |    5 | CAAAAA   | MAEAAA   | AAAAxx
+      802 |    2908 |   0 |    2 |   2 |      2 |       2 |      802 |         802 |       802 |      802 |   4 |    5 | WEAAAA   | WHEAAA   | AAAAxx
+     6402 |    3808 |   0 |    2 |   2 |      2 |       2 |      402 |         402 |      1402 |     6402 |   4 |    5 | GMAAAA   | MQFAAA   | AAAAxx
+     8602 |    5440 |   0 |    2 |   2 |      2 |       2 |      602 |         602 |      3602 |     8602 |   4 |    5 | WSAAAA   | GBIAAA   | AAAAxx
+     8402 |    5708 |   0 |    2 |   2 |      2 |       2 |      402 |         402 |      3402 |     8402 |   4 |    5 | ELAAAA   | OLIAAA   | AAAAxx
+     2102 |    6184 |   0 |    2 |   2 |      2 |       2 |      102 |         102 |      2102 |     2102 |   4 |    5 | WCAAAA   | WDJAAA   | AAAAxx
+     4202 |    6628 |   0 |    2 |   2 |      2 |       2 |      202 |         202 |      4202 |     4202 |   4 |    5 | QFAAAA   | YUJAAA   | AAAAxx
+     2902 |    6816 |   0 |    2 |   2 |      2 |       2 |      902 |         902 |      2902 |     2902 |   4 |    5 | QHAAAA   | ECKAAA   | AAAAxx
+     2302 |    7112 |   0 |    2 |   2 |      2 |       2 |      302 |         302 |      2302 |     2302 |   4 |    5 | OKAAAA   | ONKAAA   | AAAAxx
+     3202 |    7128 |   0 |    2 |   2 |      2 |       2 |      202 |        1202 |      3202 |     3202 |   4 |    5 | ETAAAA   | EOKAAA   | AAAAxx
+     7802 |    7508 |   0 |    2 |   2 |      2 |       2 |      802 |        1802 |      2802 |     7802 |   4 |    5 | COAAAA   | UCLAAA   | AAAAxx
+     4102 |    7676 |   0 |    2 |   2 |      2 |       2 |      102 |         102 |      4102 |     4102 |   4 |    5 | UBAAAA   | GJLAAA   | AAAAxx
+     8302 |    7800 |   0 |    2 |   2 |      2 |       2 |      302 |         302 |      3302 |     8302 |   4 |    5 | IHAAAA   | AOLAAA   | AAAAxx
+     1702 |    7940 |   0 |    2 |   2 |      2 |       2 |      702 |        1702 |      1702 |     1702 |   4 |    5 | MNAAAA   | KTLAAA   | AAAAxx
+     2202 |    8028 |   0 |    2 |   2 |      2 |       2 |      202 |         202 |      2202 |     2202 |   4 |    5 | SGAAAA   | UWLAAA   | AAAAxx
+     1602 |    8148 |   0 |    2 |   2 |      2 |       2 |      602 |        1602 |      1602 |     1602 |   4 |    5 | QJAAAA   | KBMAAA   | AAAAxx
+     5602 |    8796 |   0 |    2 |   2 |      2 |       2 |      602 |        1602 |       602 |     5602 |   4 |    5 | MHAAAA   | IANAAA   | AAAAxx
+     6002 |    8932 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |      1002 |     6002 |   4 |    5 | WWAAAA   | OFNAAA   | AAAAxx
+     3902 |    9224 |   0 |    2 |   2 |      2 |       2 |      902 |        1902 |      3902 |     3902 |   4 |    5 | CUAAAA   | UQNAAA   | AAAAxx
+     9602 |    9972 |   0 |    2 |   2 |      2 |       2 |      602 |        1602 |      4602 |     9602 |   4 |    5 | IFAAAA   | OTOAAA   | AAAAxx
+     8002 |    9980 |   0 |    2 |   2 |      2 |       2 |        2 |           2 |      3002 |     8002 |   4 |    5 | UVAAAA   | WTOAAA   | AAAAxx
+ (29 rows)
+ 
+ -- too few params
+ EXECUTE q3 USING 'bool';
+ ERROR:  Wrong number of parameters, expected 6 but got 1
+ -- too many params
+ EXECUTE q3 USING 'bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true;
+ ERROR:  Wrong number of parameters, expected 6 but got 7
+ -- wrong param types
+ EXECUTE q3 USING 5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea';
+ ERROR:  Parameter $2 of type "float8" cannot be coerced into the expected type ("int4").
+ 	You will need to rewrite or cast the expression.
+ -- invalid type
+ PREPARE q4(nonexistenttype) FROM SELECT $1;
+ ERROR:  Type "nonexistenttype" does not exist
+ -- execute into
+ PREPARE q5(int, text) FROM
+ 	SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
+ EXECUTE q5 INTO TEMPORARY q5_prep_results USING 200, 'DTAAAA';
+ SELECT * FROM q5_prep_results;
+  unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+ ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+     2525 |      64 |   1 |    1 |   5 |      5 |      25 |      525 |         525 |      2525 |     2525 |  50 |   51 | DTAAAA   | MCAAAA   | AAAAxx
+     7257 |    1895 |   1 |    1 |   7 |     17 |      57 |      257 |        1257 |      2257 |     7257 | 114 |  115 | DTAAAA   | XUCAAA   | VVVVxx
+     9961 |    2058 |   1 |    1 |   1 |      1 |      61 |      961 |        1961 |      4961 |     9961 | 122 |  123 | DTAAAA   | EBDAAA   | OOOOxx
+     3877 |    4060 |   1 |    1 |   7 |     17 |      77 |      877 |        1877 |      3877 |     3877 | 154 |  155 | DTAAAA   | EAGAAA   | AAAAxx
+     4553 |    4113 |   1 |    1 |   3 |     13 |      53 |      553 |         553 |      4553 |     4553 | 106 |  107 | DTAAAA   | FCGAAA   | HHHHxx
+     7933 |    4514 |   1 |    1 |   3 |     13 |      33 |      933 |        1933 |      2933 |     7933 |  66 |   67 | DTAAAA   | QRGAAA   | OOOOxx
+     6581 |    4686 |   1 |    1 |   1 |      1 |      81 |      581 |         581 |      1581 |     6581 | 162 |  163 | DTAAAA   | GYGAAA   | OOOOxx
+     8609 |    5918 |   1 |    1 |   9 |      9 |       9 |      609 |         609 |      3609 |     8609 |  18 |   19 | DTAAAA   | QTIAAA   | OOOOxx
+     5229 |    6407 |   1 |    1 |   9 |      9 |      29 |      229 |        1229 |       229 |     5229 |  58 |   59 | DTAAAA   | LMJAAA   | VVVVxx
+     1173 |    6699 |   1 |    1 |   3 |     13 |      73 |      173 |        1173 |      1173 |     1173 | 146 |  147 | DTAAAA   | RXJAAA   | VVVVxx
+     3201 |    7309 |   1 |    1 |   1 |      1 |       1 |      201 |        1201 |      3201 |     3201 |   2 |    3 | DTAAAA   | DVKAAA   | HHHHxx
+     1849 |    8143 |   1 |    1 |   9 |      9 |      49 |      849 |        1849 |      1849 |     1849 |  98 |   99 | DTAAAA   | FBMAAA   | VVVVxx
+     9285 |    8469 |   1 |    1 |   5 |      5 |      85 |      285 |        1285 |      4285 |     9285 | 170 |  171 | DTAAAA   | TNMAAA   | HHHHxx
+      497 |    9092 |   1 |    1 |   7 |     17 |      97 |      497 |         497 |       497 |      497 | 194 |  195 | DTAAAA   | SLNAAA   | AAAAxx
+      200 |    9441 |   0 |    0 |   0 |      0 |       0 |      200 |         200 |       200 |      200 |   0 |    1 | SHAAAA   | DZNAAA   | HHHHxx
+     5905 |    9537 |   1 |    1 |   5 |      5 |       5 |      905 |        1905 |       905 |     5905 |  10 |   11 | DTAAAA   | VCOAAA   | HHHHxx
+ (16 rows)
+ 
Index: src/test/regress/sql/prepare.sql
===================================================================
RCS file: src/test/regress/sql/prepare.sql
diff -N src/test/regress/sql/prepare.sql
*** /dev/null	1 Jan 1970 00:00:00 -0000
--- src/test/regress/sql/prepare.sql	28 Jun 2002 17:20:07 -0000
***************
*** 0 ****
--- 1,45 ----
+ -- Regression tests for prepareable statements
+ 
+ PREPARE q1 FROM SELECT 1;
+ EXECUTE q1;
+ 
+ -- should fail
+ PREPARE q1 FROM SELECT 2;
+ 
+ -- should succeed
+ DEALLOCATE q1;
+ PREPARE q1 FROM SELECT 2;
+ EXECUTE q1;
+ 
+ -- sql92 syntax
+ DEALLOCATE PREPARE q1;
+ 
+ -- parametized queries
+ PREPARE q2(text) FROM
+ 	SELECT datname, datdba, datistemplate, datallowconn
+ 	FROM pg_database WHERE datname = $1;
+ EXECUTE q2 USING 'regression';
+ 
+ PREPARE q3(text, int, float, boolean, oid, smallint) FROM
+ 	SELECT * FROM tenk1 WHERE string4 = $1 AND (four = $2 OR
+ 	ten = $3::bigint OR true = $4 OR oid = $5 OR odd = $6::int);
+ 
+ EXECUTE q3 USING 'AAAAxx', 5::smallint, 10.5::float, false, 500::oid, 4::bigint;
+ 
+ -- too few params
+ EXECUTE q3 USING 'bool';
+ 
+ -- too many params
+ EXECUTE q3 USING 'bytea', 5::smallint, 10.5::float, false, 500::oid, 4::bigint, true;
+ 
+ -- wrong param types
+ EXECUTE q3 USING 5::smallint, 10.5::float, false, 500::oid, 4::bigint, 'bytea';
+ 
+ -- invalid type
+ PREPARE q4(nonexistenttype) FROM SELECT $1;
+ 
+ -- execute into
+ PREPARE q5(int, text) FROM
+ 	SELECT * FROM tenk1 WHERE unique1 = $1 OR stringu1 = $2;
+ EXECUTE q5 INTO TEMPORARY q5_prep_results USING 200, 'DTAAAA';
+ SELECT * FROM q5_prep_results;