Added EXECUTE command to PL/pgSQL for execution of

dynamic SQL and utility statements.

Jan

6 files changed, 368 insertions, 9 deletions

diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index e76ddecb3c8..99f7a1b0f6e 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -4,7 +4,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *    $Header: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v 1.10 2000/06/05 07:29:14 tgl Exp $
+ *    $Header: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v 1.11 2000/08/31 13:26:15 wieck Exp $
  *
  *    This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -113,6 +113,7 @@ static	PLpgSQL_expr	*make_tupret_expr(PLpgSQL_row *row);
 %type <stmt>	stmt_assign, stmt_if, stmt_loop, stmt_while, stmt_exit
 %type <stmt>	stmt_return, stmt_raise, stmt_execsql, stmt_fori
 %type <stmt>	stmt_fors, stmt_select, stmt_perform
+%type <stmt>	stmt_dynexecute, stmt_dynfors
 
 %type <dtlist>	raise_params
 %type <ival>	raise_level, raise_param
@@ -134,6 +135,7 @@ static	PLpgSQL_expr	*make_tupret_expr(PLpgSQL_row *row);
 %token	K_ELSE
 %token	K_END
 %token	K_EXCEPTION
+%token	K_EXECUTE
 %token	K_EXIT
 %token	K_FOR
 %token	K_FROM
@@ -568,6 +570,10 @@ proc_stmt	: pl_block
 			{ $$ = $1; }
 		| stmt_execsql
 			{ $$ = $1; }
+		| stmt_dynexecute
+			{ $$ = $1; }
+		| stmt_dynfors
+			{ $$ = $1; }
 		| stmt_perform
 			{ $$ = $1; }
 		;
@@ -844,6 +850,35 @@ stmt_fors	: opt_label K_FOR lno fors_target K_IN K_SELECT expr_until_loop loop_b
 			$$ = (PLpgSQL_stmt *)new;
 		    }
 
+stmt_dynfors : opt_label K_FOR lno fors_target K_IN K_EXECUTE expr_until_loop loop_body
+		    {
+			PLpgSQL_stmt_dynfors	*new;
+
+			new = malloc(sizeof(PLpgSQL_stmt_dynfors));
+			memset(new, 0, sizeof(PLpgSQL_stmt_dynfors));
+
+			new->cmd_type = PLPGSQL_STMT_DYNFORS;
+			new->lineno   = $3;
+			new->label    = $1;
+			switch ($4->dtype) {
+			    case PLPGSQL_DTYPE_REC:
+			        new->rec = $4;
+				break;
+			    case PLPGSQL_DTYPE_ROW:
+			        new->row = (PLpgSQL_row *)$4;
+				break;
+			    default:
+				plpgsql_comperrinfo();
+			        elog(ERROR, "unknown dtype %d in stmt_dynfors", $4->dtype);
+			}
+			new->query = $7;
+			new->body  = $8;
+
+			plpgsql_ns_pop();
+
+			$$ = (PLpgSQL_stmt *)new;
+		    }
+
 fors_target	: T_RECORD
 		    {
 		        $$ = yylval.rec;
@@ -1028,6 +1063,19 @@ stmt_execsql	: execsql_start lno
 		    }
 		;
 
+stmt_dynexecute	: K_EXECUTE lno expr_until_semi
+			{
+				PLpgSQL_stmt_dynexecute	*new;
+
+			new = malloc(sizeof(PLpgSQL_stmt_dynexecute));
+			new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;
+			new->lineno   = $2;
+			new->query    = $3;
+
+			$$ = (PLpgSQL_stmt *)new;
+			}
+		;
+
 execsql_start	: T_WORD
 		    { $$ = strdup(yytext); }
 		| T_ERROR
diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c
index cc4196fb1e8..bed95890968 100644
--- a/src/pl/plpgsql/src/pl_comp.c
+++ b/src/pl/plpgsql/src/pl_comp.c
@@ -3,7 +3,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.22 2000/08/03 16:34:57 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_comp.c,v 1.23 2000/08/31 13:26:16 wieck Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -159,8 +159,8 @@ plpgsql_compile(Oid fn_oid, int functype)
 
 	function->fn_functype = functype;
 	function->fn_oid = fn_oid;
-	function->fn_name = DatumGetCString(DirectFunctionCall1(nameout,
-						NameGetDatum(&(procStruct->proname))));
+	function->fn_name = strdup(DatumGetCString(DirectFunctionCall1(nameout,
+						NameGetDatum(&(procStruct->proname)))));
 
 	switch (functype)
 	{
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 6c761c50f0c..7740c2790ee 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -3,7 +3,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.28 2000/08/24 03:29:15 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.29 2000/08/31 13:26:16 wieck Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -100,6 +100,10 @@ static int exec_stmt_raise(PLpgSQL_execstate * estate,
 				PLpgSQL_stmt_raise * stmt);
 static int exec_stmt_execsql(PLpgSQL_execstate * estate,
 				  PLpgSQL_stmt_execsql * stmt);
+static int exec_stmt_dynexecute(PLpgSQL_execstate * estate,
+				  PLpgSQL_stmt_dynexecute * stmt);
+static int exec_stmt_dynfors(PLpgSQL_execstate * estate,
+			   PLpgSQL_stmt_dynfors * stmt);
 
 static void exec_prepare_plan(PLpgSQL_execstate * estate,
 				  PLpgSQL_expr * expr);
@@ -219,6 +223,12 @@ plpgsql_exec_function(PLpgSQL_function * func, FunctionCallInfo fcinfo)
 					case PLPGSQL_STMT_EXECSQL:
 						stmttype = "SQL statement";
 						break;
+					case PLPGSQL_STMT_DYNEXECUTE:
+						stmttype = "execute statement";
+						break;
+					case PLPGSQL_STMT_DYNFORS:
+						stmttype = "for over execute statement";
+						break;
 					default:
 						stmttype = "unknown";
 						break;
@@ -522,6 +532,12 @@ plpgsql_exec_trigger(PLpgSQL_function * func,
 					case PLPGSQL_STMT_EXECSQL:
 						stmttype = "SQL statement";
 						break;
+					case PLPGSQL_STMT_DYNEXECUTE:
+						stmttype = "execute statement";
+						break;
+					case PLPGSQL_STMT_DYNFORS:
+						stmttype = "for over execute statement";
+						break;
 					default:
 						stmttype = "unknown";
 						break;
@@ -995,6 +1011,14 @@ exec_stmt(PLpgSQL_execstate * estate, PLpgSQL_stmt * stmt)
 			rc = exec_stmt_execsql(estate, (PLpgSQL_stmt_execsql *) stmt);
 			break;
 
+		case PLPGSQL_STMT_DYNEXECUTE:
+			rc = exec_stmt_dynexecute(estate, (PLpgSQL_stmt_dynexecute *) stmt);
+			break;
+
+		case PLPGSQL_STMT_DYNFORS:
+			rc = exec_stmt_dynfors(estate, (PLpgSQL_stmt_dynfors *) stmt);
+			break;
+
 		default:
 			error_info_stmt = save_estmt;
 			elog(ERROR, "unknown cmdtype %d in exec_stmt",
@@ -1853,6 +1877,234 @@ exec_stmt_execsql(PLpgSQL_execstate * estate,
 
 
 /* ----------
+ * exec_stmt_dynexecute			Execute a dynamic SQL query not
+ *					returning any data.
+ * ----------
+ */
+static int
+exec_stmt_dynexecute(PLpgSQL_execstate * estate,
+				  PLpgSQL_stmt_dynexecute * stmt)
+{
+	Datum		query;
+	bool		isnull = false;
+	Oid			restype;
+	char	   *querystr;
+	HeapTuple	typetup;
+	Form_pg_type typeStruct;
+	FmgrInfo	finfo_output;
+
+	/* ----------
+	 * First we evaluate the string expression after the
+	 * EXECUTE keyword. It's result is the querystring we have
+	 * to execute.
+	 * ----------
+	 */
+	query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
+	if (isnull)
+		elog(ERROR, "cannot EXECUTE NULL-query");
+
+	/* ----------
+	 * Get the C-String representation.
+	 * ----------
+	 */
+	typetup = SearchSysCacheTuple(TYPEOID,
+		ObjectIdGetDatum(restype), 0, 0, 0);
+	if (!HeapTupleIsValid(typetup))
+		elog(ERROR, "cache lookup for type %u failed (1)", restype);
+	typeStruct = (Form_pg_type) GETSTRUCT(typetup);
+
+	fmgr_info(typeStruct->typoutput, &finfo_output);
+	querystr = DatumGetCString(FunctionCall3(&finfo_output,
+				query,
+				ObjectIdGetDatum(typeStruct->typelem),
+				Int32GetDatum(-1)));
+
+	if(!typeStruct->typbyval)
+		pfree((void *)query);
+
+	/* ----------
+	 * Call SPI_exec() without preparing a saved plan. 
+	 * The returncode can be any OK except for OK_SELECT.
+	 * ----------
+	 */
+	switch(SPI_exec(querystr, 0))
+	{
+		case SPI_OK_UTILITY:
+		case SPI_OK_SELINTO:
+		case SPI_OK_INSERT:
+		case SPI_OK_UPDATE:
+		case SPI_OK_DELETE:
+			break;
+
+		case SPI_OK_SELECT:
+			elog(ERROR, "unexpected SELECT operation in EXECUTE of query '%s'",
+						querystr);
+			break;
+
+		default:
+			elog(ERROR, "unexpected error in EXECUTE for query '%s'",
+						querystr);
+			break;
+	}
+
+	pfree(querystr);
+	return PLPGSQL_RC_OK;
+}
+
+
+/* ----------
+ * exec_stmt_dynfors			Execute a dynamic query, assign each
+ *					tuple to a record or row and
+ *					execute a group of statements
+ *					for it.
+ * ----------
+ */
+static int
+exec_stmt_dynfors(PLpgSQL_execstate * estate, PLpgSQL_stmt_dynfors * stmt)
+{
+	Datum		query;
+	bool		isnull = false;
+	Oid			restype;
+	char	   *querystr;
+	PLpgSQL_rec *rec = NULL;
+	PLpgSQL_row *row = NULL;
+	SPITupleTable *tuptab;
+	int			rc;
+	int			i;
+	int			n;
+	HeapTuple	typetup;
+	Form_pg_type typeStruct;
+	FmgrInfo	finfo_output;
+
+	/* ----------
+	 * Initialize the global found variable to false
+	 * ----------
+	 */
+	exec_set_found(estate, false);
+
+	/* ----------
+	 * Determine if we assign to a record or a row
+	 * ----------
+	 */
+	if (stmt->rec != NULL)
+		rec = (PLpgSQL_rec *) (estate->datums[stmt->rec->recno]);
+	else
+	{
+		if (stmt->row != NULL)
+			row = (PLpgSQL_row *) (estate->datums[stmt->row->rowno]);
+		else
+			elog(ERROR, "unsupported target in exec_stmt_fors()");
+	}
+
+	/* ----------
+	 * Evaluate the string expression after the
+	 * EXECUTE keyword. It's result is the querystring we have
+	 * to execute.
+	 * ----------
+	 */
+	query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
+	if (isnull)
+		elog(ERROR, "cannot EXECUTE NULL-query");
+
+	/* ----------
+	 * Get the C-String representation.
+	 * ----------
+	 */
+	typetup = SearchSysCacheTuple(TYPEOID,
+		ObjectIdGetDatum(restype), 0, 0, 0);
+	if (!HeapTupleIsValid(typetup))
+		elog(ERROR, "cache lookup for type %u failed (1)", restype);
+	typeStruct = (Form_pg_type) GETSTRUCT(typetup);
+
+	fmgr_info(typeStruct->typoutput, &finfo_output);
+	querystr = DatumGetCString(FunctionCall3(&finfo_output,
+				query,
+				ObjectIdGetDatum(typeStruct->typelem),
+				Int32GetDatum(-1)));
+
+	if(!typeStruct->typbyval)
+		pfree((void *)query);
+
+	/* ----------
+	 * Run the query
+	 * ----------
+	 */
+	if (SPI_exec(querystr, 0) != SPI_OK_SELECT)
+		elog(ERROR, "FOR ... EXECUTE query '%s' was no SELECT", querystr);
+	pfree(querystr);
+
+	n = SPI_processed;
+
+	/* ----------
+	 * If the query didn't return any row, set the target
+	 * to NULL and return.
+	 * ----------
+	 */
+	if (n == 0)
+	{
+		exec_move_row(estate, rec, row, NULL, NULL);
+		return PLPGSQL_RC_OK;
+	}
+
+	/* ----------
+	 * There are tuples, so set found to true
+	 * ----------
+	 */
+	exec_set_found(estate, true);
+
+	/* ----------
+	 * Now do the loop
+	 * ----------
+	 */
+	tuptab = SPI_tuptable;
+	SPI_tuptable = NULL;
+
+	for (i = 0; i < n; i++)
+	{
+		/* ----------
+		 * Assign the tuple to the target
+		 * ----------
+		 */
+		exec_move_row(estate, rec, row, tuptab->vals[i], tuptab->tupdesc);
+
+		/* ----------
+		 * Execute the statements
+		 * ----------
+		 */
+		rc = exec_stmts(estate, stmt->body);
+
+		/* ----------
+		 * Check returncode
+		 * ----------
+		 */
+		switch (rc)
+		{
+			case PLPGSQL_RC_OK:
+				break;
+
+			case PLPGSQL_RC_EXIT:
+				if (estate->exitlabel == NULL)
+					return PLPGSQL_RC_OK;
+				if (stmt->label == NULL)
+					return PLPGSQL_RC_EXIT;
+				if (strcmp(stmt->label, estate->exitlabel))
+					return PLPGSQL_RC_EXIT;
+				estate->exitlabel = NULL;
+				return PLPGSQL_RC_OK;
+
+			case PLPGSQL_RC_RETURN:
+				return PLPGSQL_RC_RETURN;
+
+			default:
+				elog(ERROR, "unknown rc %d from exec_stmts()", rc);
+		}
+	}
+
+	return PLPGSQL_RC_OK;
+}
+
+
+/* ----------
  * exec_assign_expr			Put an expressions result into
  *					a variable.
  * ----------
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index 0b191ff603b..dc1a2f423d6 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -3,7 +3,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.6 2000/06/14 18:18:00 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.7 2000/08/31 13:26:16 wieck Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -388,6 +388,8 @@ static void dump_exit(PLpgSQL_stmt_exit * stmt);
 static void dump_return(PLpgSQL_stmt_return * stmt);
 static void dump_raise(PLpgSQL_stmt_raise * stmt);
 static void dump_execsql(PLpgSQL_stmt_execsql * stmt);
+static void dump_dynexecute(PLpgSQL_stmt_dynexecute * stmt);
+static void dump_dynfors(PLpgSQL_stmt_dynfors * stmt);
 static void dump_expr(PLpgSQL_expr * expr);
 
 
@@ -442,6 +444,12 @@ dump_stmt(PLpgSQL_stmt * stmt)
 		case PLPGSQL_STMT_EXECSQL:
 			dump_execsql((PLpgSQL_stmt_execsql *) stmt);
 			break;
+		case PLPGSQL_STMT_DYNEXECUTE:
+			dump_dynexecute((PLpgSQL_stmt_dynexecute *) stmt);
+			break;
+		case PLPGSQL_STMT_DYNFORS:
+			dump_dynfors((PLpgSQL_stmt_dynfors *) stmt);
+			break;
 		default:
 			elog(ERROR, "plpgsql_dump: unknown cmd_type %d\n", stmt->cmd_type);
 			break;
@@ -663,6 +671,34 @@ dump_execsql(PLpgSQL_stmt_execsql * stmt)
 }
 
 static void
+dump_dynexecute(PLpgSQL_stmt_dynexecute * stmt)
+{
+	dump_ind();
+	printf("EXECUTE ");
+	dump_expr(stmt->query);
+	printf("\n");
+}
+
+static void
+dump_dynfors(PLpgSQL_stmt_dynfors * stmt)
+{
+	int			i;
+
+	dump_ind();
+	printf("FORS %s EXECUTE ", (stmt->rec != NULL) ? stmt->rec->refname : stmt->row->refname);
+	dump_expr(stmt->query);
+	printf("\n");
+
+	dump_indent += 2;
+	for (i = 0; i < stmt->body->stmts_used; i++)
+		dump_stmt((PLpgSQL_stmt *) (stmt->body->stmts[i]));
+	dump_indent -= 2;
+
+	dump_ind();
+	printf("    ENDFORS\n");
+}
+
+static void
 dump_expr(PLpgSQL_expr * expr)
 {
 	int			i;
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index f4246980fca..e48a56ce893 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -3,7 +3,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.9 2000/05/28 17:56:28 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.10 2000/08/31 13:26:16 wieck Exp $
  *
  *	  This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -91,7 +91,9 @@ enum
 	PLPGSQL_STMT_EXIT,
 	PLPGSQL_STMT_RETURN,
 	PLPGSQL_STMT_RAISE,
-	PLPGSQL_STMT_EXECSQL
+	PLPGSQL_STMT_EXECSQL,
+	PLPGSQL_STMT_DYNEXECUTE,
+	PLPGSQL_STMT_DYNFORS
 };
 
 
@@ -319,6 +321,18 @@ typedef struct
 
 
 typedef struct
+{								/* FOR statement running over EXECUTE	*/
+	int			cmd_type;
+	int			lineno;
+	char	   *label;
+	PLpgSQL_rec *rec;
+	PLpgSQL_row *row;
+	PLpgSQL_expr *query;
+	PLpgSQL_stmts *body;
+}			PLpgSQL_stmt_dynfors;
+
+
+typedef struct
 {								/* SELECT ... INTO statement		*/
 	int			cmd_type;
 	int			lineno;
@@ -366,6 +380,14 @@ typedef struct
 }			PLpgSQL_stmt_execsql;
 
 
+typedef struct
+{								/* Dynamic SQL string to execute */
+	int			cmd_type;
+	int			lineno;
+	PLpgSQL_expr *query;
+}			PLpgSQL_stmt_dynexecute;
+
+
 typedef struct PLpgSQL_function
 {								/* Complete compiled function	  */
 	Oid			fn_oid;
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index a3eae579205..73b605032f1 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -4,7 +4,7 @@
  *			  procedural language
  *
  * IDENTIFICATION
- *    $Header: /cvsroot/pgsql/src/pl/plpgsql/src/Attic/scan.l,v 1.5 2000/08/22 14:59:28 tgl Exp $
+ *    $Header: /cvsroot/pgsql/src/pl/plpgsql/src/Attic/scan.l,v 1.6 2000/08/31 13:26:16 wieck Exp $
  *
  *    This software is copyrighted by Jan Wieck - Hamburg.
  *
@@ -100,6 +100,7 @@ default			{ return K_DEFAULT;			}
 else			{ return K_ELSE;			}
 end				{ return K_END;				}
 exception		{ return K_EXCEPTION;		}
+execute			{ return K_EXECUTE;			}
 exit			{ return K_EXIT;			}
 for				{ return K_FOR;				}
 from			{ return K_FROM;			}