pl_exec.c

PostgreSQL7.4.6 for Linux

			 * contained no commands.
			 */
			break;

		case SPI_OK_SELINTO:

			/*
			 * We want to disallow SELECT INTO for now, because its
			 * behavior is not consistent with SELECT INTO in a normal
			 * plpgsql context. (We need to reimplement EXECUTE to parse
			 * the string as a plpgsql command, not just feed it to
			 * SPI_exec.) However, CREATE AS should be allowed ... and
			 * since it produces the same parsetree as SELECT INTO,
			 * there's no way to tell the difference except to look at the
			 * source text.  Wotta kluge!
			 */
			{
				char	   *ptr;

				for (ptr = querystr; *ptr; ptr++)
					if (!isspace((unsigned char) *ptr))
						break;
				if (*ptr == 'S' || *ptr == 's')
					ereport(ERROR,
							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
							 errmsg("EXECUTE of SELECT ... INTO is not implemented yet")));
				break;
			}

		default:
			elog(ERROR, "unexpected error %d in EXECUTE of query \"%s\"",
				 exec_res, querystr);
			break;
	}

	/* Release any result from SPI_exec, as well as the querystring */
	SPI_freetuptable(SPI_tuptable);
	pfree(querystr);

	/* Save result info for GET DIAGNOSTICS */
	estate->eval_processed = SPI_processed;
	estate->eval_lastoid = SPI_lastoid;

	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 = PLPGSQL_RC_OK;
	int			i;
	int			n;
	void	   *plan;
	Portal		portal;
	bool		found = 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");

	/*
	 * 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)
		ereport(ERROR,
				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
				 errmsg("cannot EXECUTE a null querystring")));

	/* Get the C-String representation */
	querystr = convert_value_to_string(query, restype);

	exec_eval_cleanup(estate);

	/*
	 * Prepare a plan and open an implicit cursor for the query
	 */
	plan = SPI_prepare(querystr, 0, NULL);
	if (plan == NULL)
		elog(ERROR, "SPI_prepare() failed for dynamic query \"%s\"", querystr);
	portal = SPI_cursor_open(NULL, plan, NULL, NULL);
	if (portal == NULL)
		elog(ERROR, "failed to open implicit cursor for dynamic query \"%s\"",
			 querystr);
	pfree(querystr);
	SPI_freeplan(plan);

	/*
	 * Fetch the initial 10 tuples
	 */
	SPI_cursor_fetch(portal, true, 10);
	tuptab = SPI_tuptable;
	n = SPI_processed;

	/*
	 * If the query didn't return any rows, set the target to NULL and
	 * return with FOUND = false.
	 */
	if (n == 0)
		exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
	else
		found = true;			/* processed at least one tuple */

	/*
	 * Now do the loop
	 */
	while (n > 0)
	{
		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);

			if (rc != PLPGSQL_RC_OK)
			{
				/*
				 * We're aborting the loop, so cleanup and set FOUND.
				 * (This code should match the code after the loop.)
				 */
				SPI_freetuptable(tuptab);
				SPI_cursor_close(portal);
				exec_set_found(estate, found);

				if (rc == PLPGSQL_RC_EXIT)
				{
					if (estate->exitlabel == NULL)
						/* unlabelled exit, finish the current loop */
						rc = PLPGSQL_RC_OK;
					else if (stmt->label != NULL &&
							 strcmp(stmt->label, estate->exitlabel) == 0)
					{
						/* labelled exit, matches the current stmt's label */
						estate->exitlabel = NULL;
						rc = PLPGSQL_RC_OK;
					}

					/*
					 * otherwise, we processed a labelled exit that does
					 * not match the current statement's label, if any:
					 * return RC_EXIT so that the EXIT continues to
					 * recurse upward.
					 */
				}

				return rc;
			}
		}

		SPI_freetuptable(tuptab);

		/*
		 * Fetch the next 50 tuples
		 */
		SPI_cursor_fetch(portal, true, 50);
		n = SPI_processed;
		tuptab = SPI_tuptable;
	}

	/*
	 * Release last group of tuples
	 */
	SPI_freetuptable(tuptab);

	/*
	 * Close the implicit cursor
	 */
	SPI_cursor_close(portal);

	/*
	 * Set the FOUND variable to indicate the result of executing the loop
	 * (namely, whether we looped one or more times). This must be set
	 * here so that it does not interfere with the value of the FOUND
	 * variable inside the loop processing itself.
	 */
	exec_set_found(estate, found);

	return PLPGSQL_RC_OK;
}


/* ----------
 * exec_stmt_open			Execute an OPEN cursor statement
 * ----------
 */
static int
exec_stmt_open(PLpgSQL_execstate * estate, PLpgSQL_stmt_open * stmt)
{
	PLpgSQL_var *curvar = NULL;
	char	   *curname = NULL;
	PLpgSQL_expr *query = NULL;
	Portal		portal;
	int			i;
	Datum	   *values;
	char	   *nulls;
	bool		isnull;


	/* ----------
	 * Get the cursor variable and if it has an assigned name, check
	 * that it's not in use currently.
	 * ----------
	 */
	curvar = (PLpgSQL_var *) (estate->datums[stmt->curvar]);
	if (!curvar->isnull)
	{
		curname = DatumGetCString(DirectFunctionCall1(textout, curvar->value));
		if (SPI_cursor_find(curname) != NULL)
			ereport(ERROR,
					(errcode(ERRCODE_DUPLICATE_CURSOR),
					 errmsg("cursor \"%s\" already in use", curname)));
	}

	/* ----------
	 * Process the OPEN according to it's type.
	 * ----------
	 */
	if (stmt->query != NULL)
	{
		/* ----------
		 * This is an OPEN refcursor FOR SELECT ...
		 *
		 * We just make sure the query is planned. The real work is
		 * done downstairs.
		 * ----------
		 */
		query = stmt->query;
		if (query->plan == NULL)
			exec_prepare_plan(estate, query);
	}
	else if (stmt->dynquery != NULL)
	{
		/* ----------
		 * This is an OPEN refcursor FOR EXECUTE ...
		 * ----------
		 */
		Datum		queryD;
		Oid			restype;
		char	   *querystr;
		void	   *curplan;

		/* ----------
		 * We evaluate the string expression after the
		 * EXECUTE keyword. It's result is the querystring we have
		 * to execute.
		 * ----------
		 */
		queryD = exec_eval_expr(estate, stmt->dynquery, &isnull, &restype);
		if (isnull)
			ereport(ERROR,
					(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
					 errmsg("cannot EXECUTE a null querystring")));

		/* Get the C-String representation */
		querystr = convert_value_to_string(queryD, restype);

		exec_eval_cleanup(estate);

		/* ----------
		 * Now we prepare a query plan for it and open a cursor
		 * ----------
		 */
		curplan = SPI_prepare(querystr, 0, NULL);
		if (curplan == NULL)
			elog(ERROR, "SPI_prepare() failed for dynamic query \"%s\"",
				 querystr);
		portal = SPI_cursor_open(curname, curplan, NULL, NULL);
		if (portal == NULL)
			elog(ERROR, "failed to open cursor");
		pfree(querystr);
		SPI_freeplan(curplan);

		/* ----------
		 * Store the eventually assigned cursor name in the cursor variable
		 * ----------
		 */
		if (curvar->freeval)
			pfree((void *) (curvar->value));

		curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
		curvar->isnull = false;
		curvar->freeval = true;

		return PLPGSQL_RC_OK;
	}
	else
	{
		/* ----------
		 * This is an OPEN cursor
		 *
		 * Note: parser should already have checked that statement supplies
		 * args iff cursor needs them, but we check again to be safe.
		 * ----------
		 */
		if (stmt->argquery != NULL)
		{
			/* ----------
			 * Er - OPEN CURSOR (args). We fake a SELECT ... INTO ...
			 * statement to evaluate the args and put 'em into the
			 * internal row.
			 * ----------
			 */
			PLpgSQL_stmt_select set_args;

			if (curvar->cursor_explicit_argrow < 0)
				ereport(ERROR,
						(errcode(ERRCODE_SYNTAX_ERROR),
				errmsg("arguments given for cursor without arguments")));

			memset(&set_args, 0, sizeof(set_args));
			set_args.cmd_type = PLPGSQL_STMT_SELECT;
			set_args.lineno = stmt->lineno;
			set_args.row = (PLpgSQL_row *)
				(estate->datums[curvar->cursor_explicit_argrow]);
			set_args.query = stmt->argquery;

			if (exec_stmt_select(estate, &set_args) != PLPGSQL_RC_OK)
				elog(ERROR, "open cursor failed during argument processing");
		}
		else
		{
			if (curvar->cursor_explicit_argrow >= 0)
				ereport(ERROR,
						(errcode(ERRCODE_SYNTAX_ERROR),
						 errmsg("arguments required for cursor")));
		}

		query = curvar->cursor_explicit_expr;
		if (query->plan == NULL)
			exec_prepare_plan(estate, query);
	}

	/* ----------
	 * Here we go if we have a saved plan where we have to put
	 * values into, either from an explicit cursor or from a
	 * refcursor opened with OPEN ... FOR SELECT ...;
	 * ----------
	 */
	values = palloc(sizeof(Datum) * (query->nparams + 1));
	nulls = palloc(query->nparams + 1);

	for (i = 0; i < query->nparams; i++)
	{
		PLpgSQL_datum *datum = estate->datums[query->params[i]];
		Oid			paramtypeid;
		bool		paramisnull;

		exec_eval_datum(estate, datum, query->plan_argtypes[i],
						&paramtypeid, &values[i], &paramisnull);
		if (paramisnull)
			nulls[i] = 'n';
		else
			nulls[i] = ' ';
	}

	/* ----------
	 * Open the cursor
	 * ----------
	 */
	portal = SPI_cursor_open(curname, query->plan, values, nulls);
	if (portal == NULL)
		elog(ERROR, "failed to open cursor");

	pfree(values);
	pfree(nulls);
	if (curname)
		pfree(curname);

	/* ----------
	 * Store the eventually assigned portal name in the cursor variable
	 * ----------
	 */
	if (curvar->freeval)
		pfree((void *) (curvar->value));

	curvar->value = DirectFunctionCall1(textin, CStringGetDatum(portal->name));
	curvar->isnull = false;
	curvar->freeval = true;

	return PLPGSQL_RC_OK;
}


/* ----------
 * exec_stmt_fetch			Fetch from a cursor into a target
 * ----------
 */
static int
exec_stmt_fetch(PLpgSQL_execstate * estate, PLpgSQL_stmt_fetch * stmt)
{
	PLpgSQL_var *curvar = NULL;
	PLpgSQL_rec *rec = NULL;
	PLpgSQL_row *row = NULL;
	SPITupleTable *tuptab;
	Portal		portal;
	char	   *curname;
	int			n;

	/* ----------
	 * Get the portal of the cursor by name
	 * ----------
	 */
	curvar = (PLpgSQL_var *) (estate->datums[stmt->curvar]);
	if (curvar->isnull)
		ereport(ERROR,
				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			 errmsg("cursor variable \"%s\" is NULL", curvar->refname)));
	curname = DatumGetCString(DirectFunctionCall1(textout, curvar->value));

	portal = SPI_cursor_find(curname);
	if (portal == NULL)
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_CURSOR),
				 errmsg("cursor \"%s\" does not exist", curname)));
	pfree(curname);

	/* ----------
	 * Determine if we fetch into 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");

	/* ----------
	 * Fetch 1 tuple from the cursor
	 * ----------
	 */
	SPI_cursor_fetch(portal, true, 1);
	tuptab = SPI_tuptable;
	n = SPI_processed;

	/* ----------
	 * Set the target and the global FOUND variable appropriately.
	 * ----------
	 */
	if (n == 0)
	{
		exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
		exec_set_found(estate, false);
	}
	else
	{
		exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
		exec_set_found(estate, true);
	}

	SPI_freetuptable(tuptab);

	return PLPGSQL_RC_OK;
}


/* ----------
 * exec_stmt_close			Close a cursor
 * ----------
 */
static int
exec_stmt_close(PLpgSQL_execstate * estate, PLpgSQL_stmt_close * stmt)
{
	PLpgSQL_var *curvar = NULL;
	Portal		portal;
	char	   *curname;

	/* ----------
	 * Get the portal of the cursor by name
	 * ----------
	 */
	curvar = (PLpgSQL_var *) (estate->datums[stmt->curvar]);
	if (curvar->isnull)
		ereport(ERROR,
				(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
			 errmsg("cursor variable \"%s\" is NULL", curvar->refname)));
	curname = DatumGetCString(DirectFunctionCall1(textout, curvar->value));

	portal = SPI_cursor_find(curname);
	if (portal == NULL)
		ereport(ERROR,
				(errcode(ERRCODE_UNDEFINED_CURSOR),
				 errmsg("cursor \"%s\" does not exist", curname)));
	pfree(curname);

	/* ----------
	 * And close it.
	 * ----------
	 */
	SPI_cursor_close(portal);

	return PLPGSQL_RC_OK;
}


/* ----------
 * exec_assign_expr			Put an expression's result into
 *					a variable.
 * ----------
 */
static void