aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/pl/plpgsql/src/gram.y249
-rw-r--r--src/pl/plpgsql/src/pl_exec.c362
-rw-r--r--src/pl/plpgsql/src/pl_funcs.c58
-rw-r--r--src/pl/plpgsql/src/plpgsql.h28
-rw-r--r--src/pl/plpgsql/src/scan.l4
-rw-r--r--src/test/regress/expected/plpgsql.out140
-rw-r--r--src/test/regress/sql/plpgsql.sql116
7 files changed, 615 insertions, 342 deletions
diff --git a/src/pl/plpgsql/src/gram.y b/src/pl/plpgsql/src/gram.y
index 86bf5fb0c6a..1ed995b6752 100644
--- a/src/pl/plpgsql/src/gram.y
+++ b/src/pl/plpgsql/src/gram.y
@@ -9,7 +9,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.94 2006/08/14 00:46:53 tgl Exp $
+ * $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.95 2006/08/14 21:14:41 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -28,11 +28,13 @@ static PLpgSQL_expr *read_sql_construct(int until,
int *endtoken);
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
static PLpgSQL_type *read_datatype(int tok);
-static PLpgSQL_stmt *make_select_stmt(int lineno);
+static PLpgSQL_stmt *make_execsql_stmt(const char *sqlstart, int lineno);
static PLpgSQL_stmt *make_fetch_stmt(int lineno, int curvar);
static PLpgSQL_stmt *make_return_stmt(int lineno);
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
static void check_assignable(PLpgSQL_datum *datum);
+static void read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
+ bool *strict);
static PLpgSQL_row *read_into_scalar_list(const char *initial_name,
PLpgSQL_datum *initial_datum);
static PLpgSQL_row *make_scalar_list1(const char *initial_name,
@@ -120,9 +122,8 @@ static void check_labels(const char *start_label,
%type <loop_body> loop_body
%type <stmt> proc_stmt pl_block
%type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
-%type <stmt> stmt_return stmt_raise stmt_execsql
-%type <stmt> stmt_for stmt_select stmt_perform
-%type <stmt> stmt_dynexecute stmt_getdiag
+%type <stmt> stmt_return stmt_raise stmt_execsql stmt_execsql_insert
+%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
%type <list> proc_exceptions
@@ -169,6 +170,7 @@ static void check_labels(const char *start_label,
%token K_IF
%token K_IN
%token K_INFO
+%token K_INSERT
%token K_INTO
%token K_IS
%token K_LOG
@@ -186,7 +188,6 @@ static void check_labels(const char *start_label,
%token K_RESULT_OID
%token K_RETURN
%token K_REVERSE
-%token K_SELECT
%token K_STRICT
%token K_THEN
%token K_TO
@@ -591,8 +592,6 @@ proc_stmt : pl_block ';'
{ $$ = $1; }
| stmt_for
{ $$ = $1; }
- | stmt_select
- { $$ = $1; }
| stmt_exit
{ $$ = $1; }
| stmt_return
@@ -601,6 +600,8 @@ proc_stmt : pl_block ';'
{ $$ = $1; }
| stmt_execsql
{ $$ = $1; }
+ | stmt_execsql_insert
+ { $$ = $1; }
| stmt_dynexecute
{ $$ = $1; }
| stmt_perform
@@ -1127,12 +1128,6 @@ for_variable : T_SCALAR
}
;
-stmt_select : K_SELECT lno
- {
- $$ = make_select_stmt($2);
- }
- ;
-
stmt_exit : exit_type lno opt_label opt_exitcond
{
PLpgSQL_stmt_exit *new;
@@ -1259,14 +1254,28 @@ loop_body : proc_sect K_END K_LOOP opt_label ';'
stmt_execsql : execsql_start lno
{
- PLpgSQL_stmt_execsql *new;
+ $$ = make_execsql_stmt($1, $2);
+ }
+ ;
- new = palloc(sizeof(PLpgSQL_stmt_execsql));
- new->cmd_type = PLPGSQL_STMT_EXECSQL;
- new->lineno = $2;
- new->sqlstmt = read_sql_stmt($1);
+/* this matches any otherwise-unrecognized starting keyword */
+execsql_start : T_WORD
+ { $$ = pstrdup(yytext); }
+ | T_ERROR
+ { $$ = pstrdup(yytext); }
+ ;
- $$ = (PLpgSQL_stmt *)new;
+stmt_execsql_insert : K_INSERT lno K_INTO
+ {
+ /*
+ * We have to special-case INSERT so that its INTO
+ * won't be treated as an INTO-variables clause.
+ *
+ * Fortunately, this is the only valid use of INTO
+ * in a pl/pgsql SQL command, and INTO is already
+ * a fully reserved word in the main grammar.
+ */
+ $$ = make_execsql_stmt("INSERT INTO", $2);
}
;
@@ -1276,46 +1285,24 @@ stmt_dynexecute : K_EXECUTE lno
PLpgSQL_expr *expr;
int endtoken;
- expr = read_sql_construct(K_INTO, ';', "INTO|;", "SELECT ",
+ expr = read_sql_construct(K_INTO, ';', "INTO|;",
+ "SELECT ",
true, true, &endtoken);
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;
- new->lineno = $2;
- new->query = expr;
+ new->lineno = $2;
+ new->query = expr;
+ new->into = false;
+ new->strict = false;
new->rec = NULL;
new->row = NULL;
- /*
- * If we saw "INTO", look for a following row
- * var, record var, or list of scalars.
- */
+ /* If we found "INTO", collect the argument */
if (endtoken == K_INTO)
{
- switch (yylex())
- {
- case T_ROW:
- new->row = yylval.row;
- check_assignable((PLpgSQL_datum *) new->row);
- break;
-
- case T_RECORD:
- new->rec = yylval.rec;
- check_assignable((PLpgSQL_datum *) new->rec);
- break;
-
- case T_SCALAR:
- new->row = read_into_scalar_list(yytext, yylval.scalar);
- break;
-
- default:
- plpgsql_error_lineno = $2;
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("syntax error at \"%s\"", yytext),
- errdetail("Expected record variable, row variable, "
- "or list of scalar variables.")));
- }
+ new->into = true;
+ read_into_target(&new->rec, &new->row, &new->strict);
if (yylex() != ';')
yyerror("syntax error");
}
@@ -1502,12 +1489,6 @@ cursor_variable : T_SCALAR
}
;
-execsql_start : T_WORD
- { $$ = pstrdup(yytext); }
- | T_ERROR
- { $$ = pstrdup(yytext); }
- ;
-
exception_sect :
{ $$ = NULL; }
| K_EXCEPTION lno
@@ -1892,12 +1873,13 @@ read_datatype(int tok)
}
static PLpgSQL_stmt *
-make_select_stmt(int lineno)
+make_execsql_stmt(const char *sqlstart, int lineno)
{
PLpgSQL_dstring ds;
int nparams = 0;
int params[MAX_EXPR_PARAMS];
char buf[32];
+ PLpgSQL_stmt_execsql *execsql;
PLpgSQL_expr *expr;
PLpgSQL_row *row = NULL;
PLpgSQL_rec *rec = NULL;
@@ -1906,12 +1888,11 @@ make_select_stmt(int lineno)
bool have_strict = false;
plpgsql_dstring_init(&ds);
- plpgsql_dstring_append(&ds, "SELECT ");
+ plpgsql_dstring_append(&ds, sqlstart);
- while (1)
+ for (;;)
{
tok = yylex();
-
if (tok == ';')
break;
if (tok == 0)
@@ -1930,37 +1911,8 @@ make_select_stmt(int lineno)
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("INTO specified more than once")));
}
- tok = yylex();
- if (tok == K_STRICT)
- {
- have_strict = true;
- tok = yylex();
- }
- switch (tok)
- {
- case T_ROW:
- row = yylval.row;
- check_assignable((PLpgSQL_datum *) row);
- have_into = true;
- break;
-
- case T_RECORD:
- rec = yylval.rec;
- check_assignable((PLpgSQL_datum *) rec);
- have_into = true;
- break;
-
- case T_SCALAR:
- row = read_into_scalar_list(yytext, yylval.scalar);
- have_into = true;
- break;
-
- default:
- /* Treat the INTO as non-special */
- plpgsql_dstring_append(&ds, " INTO ");
- plpgsql_push_back_token(tok);
- break;
- }
+ have_into = true;
+ read_into_target(&rec, &row, &have_strict);
continue;
}
@@ -2007,31 +1959,16 @@ make_select_stmt(int lineno)
check_sql_expr(expr->query);
- if (have_into)
- {
- PLpgSQL_stmt_select *select;
-
- select = palloc0(sizeof(PLpgSQL_stmt_select));
- select->cmd_type = PLPGSQL_STMT_SELECT;
- select->lineno = lineno;
- select->rec = rec;
- select->row = row;
- select->query = expr;
- select->strict = have_strict;
-
- return (PLpgSQL_stmt *)select;
- }
- else
- {
- PLpgSQL_stmt_execsql *execsql;
-
- execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
- execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
- execsql->lineno = lineno;
- execsql->sqlstmt = expr;
+ execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
+ execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
+ execsql->lineno = lineno;
+ execsql->sqlstmt = expr;
+ execsql->into = have_into;
+ execsql->strict = have_strict;
+ execsql->rec = rec;
+ execsql->row = row;
- return (PLpgSQL_stmt *)execsql;
- }
+ return (PLpgSQL_stmt *) execsql;
}
@@ -2039,38 +1976,12 @@ static PLpgSQL_stmt *
make_fetch_stmt(int lineno, int curvar)
{
int tok;
- PLpgSQL_row *row = NULL;
- PLpgSQL_rec *rec = NULL;
+ PLpgSQL_rec *rec;
+ PLpgSQL_row *row;
PLpgSQL_stmt_fetch *fetch;
/* We have already parsed everything through the INTO keyword */
-
- tok = yylex();
- switch (tok)
- {
- case T_ROW:
- row = yylval.row;
- check_assignable((PLpgSQL_datum *) row);
- break;
-
- case T_RECORD:
- rec = yylval.rec;
- check_assignable((PLpgSQL_datum *) rec);
- break;
-
- case T_SCALAR:
- row = read_into_scalar_list(yytext, yylval.scalar);
- break;
-
- default:
- plpgsql_error_lineno = plpgsql_scanner_lineno();
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("syntax error at \"%s\"", yytext),
- errdetail("Expected record variable, row variable, "
- "or list of scalar variables.")));
- }
-
+ read_into_target(&rec, &row, NULL);
tok = yylex();
if (tok != ';')
yyerror("syntax error");
@@ -2233,6 +2144,54 @@ check_assignable(PLpgSQL_datum *datum)
}
/*
+ * Read the argument of an INTO clause. On entry, we have just read the
+ * INTO keyword.
+ */
+static void
+read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row, bool *strict)
+{
+ int tok;
+
+ /* Set default results */
+ *rec = NULL;
+ *row = NULL;
+ if (strict)
+ *strict = false;
+
+ tok = yylex();
+ if (strict && tok == K_STRICT)
+ {
+ *strict = true;
+ tok = yylex();
+ }
+
+ switch (tok)
+ {
+ case T_ROW:
+ *row = yylval.row;
+ check_assignable((PLpgSQL_datum *) *row);
+ break;
+
+ case T_RECORD:
+ *rec = yylval.rec;
+ check_assignable((PLpgSQL_datum *) *rec);
+ break;
+
+ case T_SCALAR:
+ *row = read_into_scalar_list(yytext, yylval.scalar);
+ break;
+
+ default:
+ plpgsql_error_lineno = plpgsql_scanner_lineno();
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("syntax error at \"%s\"", yytext),
+ errdetail("Expected record variable, row variable, "
+ "or list of scalar variables following INTO.")));
+ }
+}
+
+/*
* Given the first datum and name in the INTO list, continue to read
* comma-separated scalar variables until we run out. Then construct
* and return a fake "row" variable that represents the list of
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 441e0801c9d..cf445a8a31d 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.174 2006/07/13 16:49:20 momjian Exp $
+ * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.175 2006/08/14 21:14:41 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -74,8 +74,6 @@ static int exec_stmt_fori(PLpgSQL_execstate *estate,
PLpgSQL_stmt_fori *stmt);
static int exec_stmt_fors(PLpgSQL_execstate *estate,
PLpgSQL_stmt_fors *stmt);
-static int exec_stmt_select(PLpgSQL_execstate *estate,
- PLpgSQL_stmt_select *stmt);
static int exec_stmt_open(PLpgSQL_execstate *estate,
PLpgSQL_stmt_open *stmt);
static int exec_stmt_fetch(PLpgSQL_execstate *estate,
@@ -1079,10 +1077,6 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt)
rc = exec_stmt_fors(estate, (PLpgSQL_stmt_fors *) stmt);
break;
- case PLPGSQL_STMT_SELECT:
- rc = exec_stmt_select(estate, (PLpgSQL_stmt_select *) stmt);
- break;
-
case PLPGSQL_STMT_EXIT:
rc = exec_stmt_exit(estate, (PLpgSQL_stmt_exit *) stmt);
break;
@@ -1674,81 +1668,6 @@ exec_stmt_fors(PLpgSQL_execstate *estate, PLpgSQL_stmt_fors *stmt)
/* ----------
- * exec_stmt_select Run a query and assign the first
- * row to a record or rowtype.
- * ----------
- */
-static int
-exec_stmt_select(PLpgSQL_execstate *estate, PLpgSQL_stmt_select *stmt)
-{
- PLpgSQL_rec *rec = NULL;
- PLpgSQL_row *row = NULL;
- SPITupleTable *tuptab;
- uint32 n;
-
- /*
- * 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");
-
- /*
- * Run the query
- *
- * Retrieving two rows can be slower than a single row, e.g.
- * a sequential scan where the scan has to be completed to
- * check for a second row. For this reason, we only retrieve
- * the second row if checking STRICT.
- */
- exec_run_select(estate, stmt->query, stmt->strict ? 2 : 1, NULL);
- tuptab = estate->eval_tuptable;
- n = estate->eval_processed;
-
- /*
- * If SELECT ... INTO specified STRICT, and the query didn't
- * find exactly one row, throw an error. If STRICT was not specified,
- * then allow the query to find any number of rows.
- */
- if (n == 0)
- {
- if (stmt->strict)
- ereport(ERROR,
- (errcode(ERRCODE_NO_DATA_FOUND),
- errmsg("query returned no rows")));
-
- /* set the target to NULL(s) */
- exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
- exec_eval_cleanup(estate);
- return PLPGSQL_RC_OK;
- }
-
- if (n > 1 && stmt->strict)
- ereport(ERROR,
- (errcode(ERRCODE_TOO_MANY_ROWS),
- errmsg("query returned more than one row")));
-
- /*
- * Put the first result into the target and set found to true
- */
- exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
- exec_set_found(estate, true);
-
- exec_eval_cleanup(estate);
-
- return PLPGSQL_RC_OK;
-}
-
-
-/* ----------
* exec_stmt_exit Implements EXIT and CONTINUE
*
* This begins the process of exiting / restarting a loop.
@@ -2296,8 +2215,7 @@ exec_prepare_plan(PLpgSQL_execstate *estate,
/* ----------
- * exec_stmt_execsql Execute an SQL statement not
- * returning any data.
+ * exec_stmt_execsql Execute an SQL statement (possibly with INTO).
* ----------
*/
static int
@@ -2307,14 +2225,41 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
int i;
Datum *values;
char *nulls;
+ long tcount;
int rc;
PLpgSQL_expr *expr = stmt->sqlstmt;
/*
- * On the first call for this expression generate the plan
+ * On the first call for this statement generate the plan, and
+ * detect whether the statement is INSERT/UPDATE/DELETE
*/
if (expr->plan == NULL)
+ {
+ _SPI_plan *spi_plan;
+ ListCell *l;
+
exec_prepare_plan(estate, expr);
+ stmt->mod_stmt = false;
+ spi_plan = (_SPI_plan *) expr->plan;
+ foreach(l, spi_plan->qtlist)
+ {
+ ListCell *l2;
+
+ foreach(l2, (List *) lfirst(l))
+ {
+ Query *q = (Query *) lfirst(l2);
+
+ Assert(IsA(q, Query));
+ if (q->canSetTag)
+ {
+ if (q->commandType == CMD_INSERT ||
+ q->commandType == CMD_UPDATE ||
+ q->commandType == CMD_DELETE)
+ stmt->mod_stmt = true;
+ }
+ }
+ }
+ }
/*
* Now build up the values and nulls arguments for SPI_execute_plan()
@@ -2337,49 +2282,134 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
}
/*
+ * If we have INTO, then we only need one row back ... but if we have
+ * INTO STRICT, ask for two rows, so that we can verify the statement
+ * returns only one. INSERT/UPDATE/DELETE are always treated strictly.
+ * Without INTO, just run the statement to completion (tcount = 0).
+ *
+ * We could just ask for two rows always when using INTO, but there
+ * are some cases where demanding the extra row costs significant time,
+ * eg by forcing completion of a sequential scan. So don't do it unless
+ * we need to enforce strictness.
+ */
+ if (stmt->into)
+ {
+ if (stmt->strict || stmt->mod_stmt)
+ tcount = 2;
+ else
+ tcount = 1;
+ }
+ else
+ tcount = 0;
+
+ /*
* Execute the plan
*/
rc = SPI_execute_plan(expr->plan, values, nulls,
- estate->readonly_func, 0);
+ estate->readonly_func, tcount);
+
+ /*
+ * Check for error, and set FOUND if appropriate (for historical reasons
+ * we set FOUND only for certain query types). Also Assert that we
+ * identified the statement type the same as SPI did.
+ */
switch (rc)
{
- case SPI_OK_UTILITY:
- case SPI_OK_SELINTO:
+ case SPI_OK_SELECT:
+ Assert(!stmt->mod_stmt);
+ exec_set_found(estate, (SPI_processed != 0));
break;
case SPI_OK_INSERT:
- case SPI_OK_DELETE:
case SPI_OK_UPDATE:
+ case SPI_OK_DELETE:
+ Assert(stmt->mod_stmt);
+ exec_set_found(estate, (SPI_processed != 0));
+ break;
+ case SPI_OK_SELINTO:
+ Assert(!stmt->mod_stmt);
+ break;
+
+ case SPI_OK_UTILITY:
+ Assert(!stmt->mod_stmt);
/*
- * If the INSERT, DELETE, or UPDATE query affected at least one
- * tuple, set the magic 'FOUND' variable to true. This conforms
- * with the behavior of PL/SQL.
+ * spi.c currently does not update SPI_processed for utility
+ * commands. Not clear if this should be considered a bug;
+ * for the moment, work around it here.
*/
- exec_set_found(estate, (SPI_processed != 0));
+ if (SPI_tuptable)
+ SPI_processed = (SPI_tuptable->alloced - SPI_tuptable->free);
break;
- case SPI_OK_SELECT:
- ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("SELECT query has no destination for result data"),
- errhint("If you want to discard the results, use PERFORM instead.")));
-
default:
elog(ERROR, "SPI_execute_plan failed executing query \"%s\": %s",
expr->query, SPI_result_code_string(rc));
}
- /*
- * Release any result tuples from SPI_execute_plan (probably shouldn't be
- * any)
- */
- SPI_freetuptable(SPI_tuptable);
-
- /* Save result info for GET DIAGNOSTICS */
+ /* All variants should save result info for GET DIAGNOSTICS */
estate->eval_processed = SPI_processed;
estate->eval_lastoid = SPI_lastoid;
+ /* Process INTO if present */
+ if (stmt->into)
+ {
+ SPITupleTable *tuptab = SPI_tuptable;
+ uint32 n = SPI_processed;
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
+
+ /* If the statement did not return a tuple table, complain */
+ if (tuptab == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("INTO used with a command that cannot return data")));
+
+ /* 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");
+
+ /*
+ * If SELECT ... INTO specified STRICT, and the query didn't
+ * find exactly one row, throw an error. If STRICT was not specified,
+ * then allow the query to find any number of rows.
+ */
+ if (n == 0)
+ {
+ if (stmt->strict)
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("query returned no rows")));
+ /* set the target to NULL(s) */
+ exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
+ }
+ else
+ {
+ if (n > 1 && (stmt->strict || stmt->mod_stmt))
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ROWS),
+ errmsg("query returned more than one row")));
+ /* Put the first result row into the target */
+ exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
+ }
+
+ /* Clean up */
+ SPI_freetuptable(SPI_tuptable);
+ }
+ else
+ {
+ /* If the statement returned a tuple table, complain */
+ if (SPI_tuptable != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("query has no destination for result data"),
+ (rc == SPI_OK_SELECT) ? errhint("If you want to discard the results of a SELECT, use PERFORM instead.") : 0));
+ }
+
pfree(values);
pfree(nulls);
@@ -2388,8 +2418,8 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
/* ----------
- * exec_stmt_dynexecute Execute a dynamic SQL query not
- * returning any data.
+ * exec_stmt_dynexecute Execute a dynamic SQL query
+ * (possibly with INTO).
* ----------
*/
static int
@@ -2401,17 +2431,10 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
Oid restype;
char *querystr;
int exec_res;
- PLpgSQL_rec *rec = NULL;
- PLpgSQL_row *row = NULL;
-
- 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]);
/*
- * First we evaluate the string expression after the EXECUTE keyword. It's
- * result is the querystring we have to execute.
+ * First we evaluate the string expression after the EXECUTE keyword.
+ * Its result is the querystring we have to execute.
*/
query = exec_eval_expr(estate, stmt->query, &isnull, &restype);
if (isnull)
@@ -2425,36 +2448,26 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
exec_eval_cleanup(estate);
/*
- * Call SPI_execute() without preparing a saved plan. The returncode can
- * be any standard OK. Note that while a SELECT is allowed, its results
- * will be discarded unless an INTO clause is specified.
+ * Call SPI_execute() without preparing a saved plan.
*/
exec_res = SPI_execute(querystr, estate->readonly_func, 0);
- /* Assign to INTO variable */
- if (rec || row)
- {
- if (exec_res != SPI_OK_SELECT)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("EXECUTE ... INTO is only for SELECT")));
- else
- {
- if (SPI_processed == 0)
- exec_move_row(estate, rec, row, NULL, SPI_tuptable->tupdesc);
- else
- exec_move_row(estate, rec, row,
- SPI_tuptable->vals[0], SPI_tuptable->tupdesc);
- }
- }
-
switch (exec_res)
{
case SPI_OK_SELECT:
case SPI_OK_INSERT:
case SPI_OK_UPDATE:
case SPI_OK_DELETE:
+ break;
+
case SPI_OK_UTILITY:
+ /*
+ * spi.c currently does not update SPI_processed for utility
+ * commands. Not clear if this should be considered a bug;
+ * for the moment, work around it here.
+ */
+ if (SPI_tuptable)
+ SPI_processed = (SPI_tuptable->alloced - SPI_tuptable->free);
break;
case 0:
@@ -2511,14 +2524,69 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
break;
}
- /* Release any result from SPI_execute, 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;
+ /* Process INTO if present */
+ if (stmt->into)
+ {
+ SPITupleTable *tuptab = SPI_tuptable;
+ uint32 n = SPI_processed;
+ PLpgSQL_rec *rec = NULL;
+ PLpgSQL_row *row = NULL;
+
+ /* If the statement did not return a tuple table, complain */
+ if (tuptab == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("INTO used with a command that cannot return data")));
+
+ /* 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");
+
+ /*
+ * If SELECT ... INTO specified STRICT, and the query didn't
+ * find exactly one row, throw an error. If STRICT was not specified,
+ * then allow the query to find any number of rows.
+ */
+ if (n == 0)
+ {
+ if (stmt->strict)
+ ereport(ERROR,
+ (errcode(ERRCODE_NO_DATA_FOUND),
+ errmsg("query returned no rows")));
+ /* set the target to NULL(s) */
+ exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
+ }
+ else
+ {
+ if (n > 1 && stmt->strict)
+ ereport(ERROR,
+ (errcode(ERRCODE_TOO_MANY_ROWS),
+ errmsg("query returned more than one row")));
+ /* Put the first result row into the target */
+ exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
+ }
+ }
+ else
+ {
+ /*
+ * It might be a good idea to raise an error if the query returned
+ * tuples that are being ignored, but historically we have not done
+ * that.
+ */
+ }
+
+ /* Release any result from SPI_execute, as well as the querystring */
+ SPI_freetuptable(SPI_tuptable);
+ pfree(querystr);
+
return PLPGSQL_RC_OK;
}
@@ -2823,12 +2891,12 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
if (stmt->argquery != NULL)
{
/* ----------
- * Er - OPEN CURSOR (args). We fake a SELECT ... INTO ...
+ * OPEN CURSOR with args. We fake a SELECT ... INTO ...
* statement to evaluate the args and put 'em into the
* internal row.
* ----------
*/
- PLpgSQL_stmt_select set_args;
+ PLpgSQL_stmt_execsql set_args;
if (curvar->cursor_explicit_argrow < 0)
ereport(ERROR,
@@ -2836,13 +2904,15 @@ exec_stmt_open(PLpgSQL_execstate *estate, PLpgSQL_stmt_open *stmt)
errmsg("arguments given for cursor without arguments")));
memset(&set_args, 0, sizeof(set_args));
- set_args.cmd_type = PLPGSQL_STMT_SELECT;
+ set_args.cmd_type = PLPGSQL_STMT_EXECSQL;
set_args.lineno = stmt->lineno;
+ set_args.sqlstmt = stmt->argquery;
+ set_args.into = true;
+ /* XXX historically this has not been STRICT */
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)
+ if (exec_stmt_execsql(estate, &set_args) != PLPGSQL_RC_OK)
elog(ERROR, "open cursor failed during argument processing");
}
else
diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c
index a4e661a44af..f763e25e8d6 100644
--- a/src/pl/plpgsql/src/pl_funcs.c
+++ b/src/pl/plpgsql/src/pl_funcs.c
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.53 2006/06/12 16:45:30 momjian Exp $
+ * $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.54 2006/08/14 21:14:41 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -439,8 +439,6 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt)
return "for with integer loopvar";
case PLPGSQL_STMT_FORS:
return "for over select rows";
- case PLPGSQL_STMT_SELECT:
- return "select into variables";
case PLPGSQL_STMT_EXIT:
return "exit";
case PLPGSQL_STMT_RETURN:
@@ -485,7 +483,6 @@ static void dump_loop(PLpgSQL_stmt_loop *stmt);
static void dump_while(PLpgSQL_stmt_while *stmt);
static void dump_fori(PLpgSQL_stmt_fori *stmt);
static void dump_fors(PLpgSQL_stmt_fors *stmt);
-static void dump_select(PLpgSQL_stmt_select *stmt);
static void dump_exit(PLpgSQL_stmt_exit *stmt);
static void dump_return(PLpgSQL_stmt_return *stmt);
static void dump_return_next(PLpgSQL_stmt_return_next *stmt);
@@ -537,9 +534,6 @@ dump_stmt(PLpgSQL_stmt *stmt)
case PLPGSQL_STMT_FORS:
dump_fors((PLpgSQL_stmt_fors *) stmt);
break;
- case PLPGSQL_STMT_SELECT:
- dump_select((PLpgSQL_stmt_select *) stmt);
- break;
case PLPGSQL_STMT_EXIT:
dump_exit((PLpgSQL_stmt_exit *) stmt);
break;
@@ -732,29 +726,6 @@ dump_fors(PLpgSQL_stmt_fors *stmt)
}
static void
-dump_select(PLpgSQL_stmt_select *stmt)
-{
- dump_ind();
- printf("SELECT ");
- dump_expr(stmt->query);
- printf("\n");
-
- dump_indent += 2;
- if (stmt->rec != NULL)
- {
- dump_ind();
- printf(" target = %d %s\n", stmt->rec->recno, stmt->rec->refname);
- }
- if (stmt->row != NULL)
- {
- dump_ind();
- printf(" target = %d %s\n", stmt->row->rowno, stmt->row->refname);
- }
- dump_indent -= 2;
-
-}
-
-static void
dump_open(PLpgSQL_stmt_open *stmt)
{
dump_ind();
@@ -891,6 +862,23 @@ dump_execsql(PLpgSQL_stmt_execsql *stmt)
printf("EXECSQL ");
dump_expr(stmt->sqlstmt);
printf("\n");
+
+ dump_indent += 2;
+ if (stmt->rec != NULL)
+ {
+ dump_ind();
+ printf(" INTO%s target = %d %s\n",
+ stmt->strict ? " STRICT" : "",
+ stmt->rec->recno, stmt->rec->refname);
+ }
+ if (stmt->row != NULL)
+ {
+ dump_ind();
+ printf(" INTO%s target = %d %s\n",
+ stmt->strict ? " STRICT" : "",
+ stmt->row->rowno, stmt->row->refname);
+ }
+ dump_indent -= 2;
}
static void
@@ -905,12 +893,16 @@ dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt)
if (stmt->rec != NULL)
{
dump_ind();
- printf(" target = %d %s\n", stmt->rec->recno, stmt->rec->refname);
+ printf(" INTO%s target = %d %s\n",
+ stmt->strict ? " STRICT" : "",
+ stmt->rec->recno, stmt->rec->refname);
}
- else if (stmt->row != NULL)
+ if (stmt->row != NULL)
{
dump_ind();
- printf(" target = %d %s\n", stmt->row->rowno, stmt->row->refname);
+ printf(" INTO%s target = %d %s\n",
+ stmt->strict ? " STRICT" : "",
+ stmt->row->rowno, stmt->row->refname);
}
dump_indent -= 2;
}
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 268fc13821e..1d53177f322 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -8,7 +8,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.78 2006/08/08 19:15:09 tgl Exp $
+ * $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.79 2006/08/14 21:14:41 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -80,7 +80,6 @@ enum
PLPGSQL_STMT_WHILE,
PLPGSQL_STMT_FORI,
PLPGSQL_STMT_FORS,
- PLPGSQL_STMT_SELECT,
PLPGSQL_STMT_EXIT,
PLPGSQL_STMT_RETURN,
PLPGSQL_STMT_RETURN_NEXT,
@@ -429,17 +428,6 @@ typedef struct
typedef struct
-{ /* SELECT ... INTO statement */
- int cmd_type;
- int lineno;
- bool strict;
- PLpgSQL_rec *rec;
- PLpgSQL_row *row;
- PLpgSQL_expr *query;
-} PLpgSQL_stmt_select;
-
-
-typedef struct
{ /* OPEN a curvar */
int cmd_type;
int lineno;
@@ -510,6 +498,12 @@ typedef struct
int cmd_type;
int lineno;
PLpgSQL_expr *sqlstmt;
+ bool mod_stmt; /* is the stmt INSERT/UPDATE/DELETE? */
+ /* note: mod_stmt is set when we plan the query */
+ bool into; /* INTO supplied? */
+ bool strict; /* INTO STRICT flag */
+ PLpgSQL_rec *rec; /* INTO target, if record */
+ PLpgSQL_row *row; /* INTO target, if row */
} PLpgSQL_stmt_execsql;
@@ -517,9 +511,11 @@ typedef struct
{ /* Dynamic SQL string to execute */
int cmd_type;
int lineno;
- PLpgSQL_rec *rec; /* INTO record or row variable */
- PLpgSQL_row *row;
- PLpgSQL_expr *query;
+ PLpgSQL_expr *query; /* string expression */
+ bool into; /* INTO supplied? */
+ bool strict; /* INTO STRICT flag */
+ PLpgSQL_rec *rec; /* INTO target, if record */
+ PLpgSQL_row *row; /* INTO target, if row */
} PLpgSQL_stmt_dynexecute;
diff --git a/src/pl/plpgsql/src/scan.l b/src/pl/plpgsql/src/scan.l
index d28185028d4..f07f9d48cbd 100644
--- a/src/pl/plpgsql/src/scan.l
+++ b/src/pl/plpgsql/src/scan.l
@@ -9,7 +9,7 @@
*
*
* IDENTIFICATION
- * $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.53 2006/08/14 00:46:53 tgl Exp $
+ * $PostgreSQL: pgsql/src/pl/plpgsql/src/scan.l,v 1.54 2006/08/14 21:14:42 tgl Exp $
*
*-------------------------------------------------------------------------
*/
@@ -137,6 +137,7 @@ get { return K_GET; }
if { return K_IF; }
in { return K_IN; }
info { return K_INFO; }
+insert { return K_INSERT; }
into { return K_INTO; }
is { return K_IS; }
log { return K_LOG; }
@@ -154,7 +155,6 @@ result_oid { return K_RESULT_OID; }
return { return K_RETURN; }
reverse { return K_REVERSE; }
row_count { return K_ROW_COUNT; }
-select { return K_SELECT; }
strict { return K_STRICT; }
then { return K_THEN; }
to { return K_TO; }
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
index e776c8f33cd..c0c76c0a987 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -2048,6 +2048,7 @@ select * from foo;
20
(2 rows)
+drop table foo;
-- Test for pass-by-ref values being stored in proper context
create function test_variable_storage() returns text as $$
declare x text;
@@ -2794,3 +2795,142 @@ select multi_datum_use(42);
t
(1 row)
+--
+-- Test STRICT limiter in both planned and EXECUTE invocations.
+-- Note that a data-modifying query is quasi strict (disallow multi rows)
+-- by default in the planned case, but not in EXECUTE.
+--
+create temp table foo (f1 int, f2 int);
+insert into foo values (1,2), (3,4);
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ insert into foo values(5,6) returning * into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+NOTICE: x.f1 = 5, x.f2 = 6
+ footest
+---------
+
+(1 row)
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail due to implicit strict
+ insert into foo values(7,8),(9,10) returning * into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR: query returned more than one row
+CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ execute 'insert into foo values(5,6) returning *' into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+NOTICE: x.f1 = 5, x.f2 = 6
+ footest
+---------
+
+(1 row)
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- this should work since EXECUTE isn't as picky
+ execute 'insert into foo values(7,8),(9,10) returning *' into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+NOTICE: x.f1 = 7, x.f2 = 8
+ footest
+---------
+
+(1 row)
+
+select * from foo;
+ f1 | f2
+----+----
+ 1 | 2
+ 3 | 4
+ 5 | 6
+ 5 | 6
+ 7 | 8
+ 9 | 10
+(6 rows)
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ select * from foo where f1 = 3 into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+NOTICE: x.f1 = 3, x.f2 = 4
+ footest
+---------
+
+(1 row)
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, no rows
+ select * from foo where f1 = 0 into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR: query returned no rows
+CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, too many rows
+ select * from foo where f1 > 3 into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR: query returned more than one row
+CONTEXT: PL/pgSQL function "footest" line 4 at SQL statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ execute 'select * from foo where f1 = 3' into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+NOTICE: x.f1 = 3, x.f2 = 4
+ footest
+---------
+
+(1 row)
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, no rows
+ execute 'select * from foo where f1 = 0' into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR: query returned no rows
+CONTEXT: PL/pgSQL function "footest" line 4 at execute statement
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, too many rows
+ execute 'select * from foo where f1 > 3' into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+select footest();
+ERROR: query returned more than one row
+CONTEXT: PL/pgSQL function "footest" line 4 at execute statement
+drop function footest();
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 86aa2ec0574..6e8f6de2e07 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -1777,6 +1777,8 @@ reset statement_timeout;
select * from foo;
+drop table foo;
+
-- Test for pass-by-ref values being stored in proper context
create function test_variable_storage() returns text as $$
declare x text;
@@ -2324,3 +2326,117 @@ begin
end$$ language plpgsql;
select multi_datum_use(42);
+
+--
+-- Test STRICT limiter in both planned and EXECUTE invocations.
+-- Note that a data-modifying query is quasi strict (disallow multi rows)
+-- by default in the planned case, but not in EXECUTE.
+--
+
+create temp table foo (f1 int, f2 int);
+
+insert into foo values (1,2), (3,4);
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ insert into foo values(5,6) returning * into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail due to implicit strict
+ insert into foo values(7,8),(9,10) returning * into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ execute 'insert into foo values(5,6) returning *' into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- this should work since EXECUTE isn't as picky
+ execute 'insert into foo values(7,8),(9,10) returning *' into x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+select * from foo;
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ select * from foo where f1 = 3 into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, no rows
+ select * from foo where f1 = 0 into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, too many rows
+ select * from foo where f1 > 3 into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should work
+ execute 'select * from foo where f1 = 3' into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, no rows
+ execute 'select * from foo where f1 = 0' into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+create or replace function footest() returns void as $$
+declare x record;
+begin
+ -- should fail, too many rows
+ execute 'select * from foo where f1 > 3' into strict x;
+ raise notice 'x.f1 = %, x.f2 = %', x.f1, x.f2;
+end$$ language plpgsql;
+
+select footest();
+
+drop function footest();
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-26 03:40:41 +0000