*** a/doc/src/sgml/plpgsql.sgml --- b/doc/src/sgml/plpgsql.sgml *************** *** 1072,1077 **** BEGIN --- 1072,1094 ---- RAISE EXCEPTION 'employee % not unique', myname; END; + + + + If you're not interested in the results of the query, but you want + to enforce that exactly one row was affected, you can use + STRICT at the beginning of the SQL statement, + for example: + + + STRICT UPDATE emp SET last_seen = current_date WHERE empname = myname; + + + If STRICT is specified at the beginning of the statement, + an INTO clause must not be present. + + + Successful execution of a command with STRICT always sets FOUND to true. *** a/src/pl/plpgsql/src/pl_exec.c --- b/src/pl/plpgsql/src/pl_exec.c *************** *** 3201,3217 **** exec_stmt_execsql(PLpgSQL_execstate *estate, paramLI = setup_param_list(estate, expr); /* ! * 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; --- 3201,3217 ---- paramLI = setup_param_list(estate, expr); /* ! * If we have INTO, then we only need one row back ... but if we have * STRICT, ask for two rows, so that we can verify the statement returns * only one. INSERT/UPDATE/DELETE are always treated strictly. Without ! * INTO or STRICT, 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 || stmt->strict) { if (stmt->strict || stmt->mod_stmt) tcount = 2; *************** *** 3337,3348 **** exec_stmt_execsql(PLpgSQL_execstate *estate, } 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)); } if (paramLI) --- 3337,3376 ---- } else { ! if (stmt->strict) ! { ! /* ! * If a statement specified STRICT, and the query didn't find ! * exactly one row, throw an error. ! */ ! if (SPI_processed == 0) ! ereport(ERROR, ! (errcode(ERRCODE_NO_DATA_FOUND), ! errmsg("query returned no rows"))); ! else if (SPI_processed > 1) ! ereport(ERROR, ! (errcode(ERRCODE_TOO_MANY_ROWS), ! errmsg("query returned more than one row"))); ! } ! ! /* ! * If the statement returned a tuple table, complain, unless it's a ! * STRICT SELECT statement. ! * */ if (SPI_tuptable != NULL) ! { ! if (stmt->strict && rc == SPI_OK_SELECT) ! { ! /* Clean up */ ! exec_eval_cleanup(estate); ! SPI_freetuptable(SPI_tuptable); ! } ! else ! 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)); ! } } if (paramLI) *** a/src/pl/plpgsql/src/pl_gram.y --- b/src/pl/plpgsql/src/pl_gram.y *************** *** 77,83 **** static PLpgSQL_expr *read_sql_expression2(int until, int until2, int *endtoken); static PLpgSQL_expr *read_sql_stmt(const char *sqlstart); static PLpgSQL_type *read_datatype(int tok); ! static PLpgSQL_stmt *make_execsql_stmt(int firsttoken, int location); static PLpgSQL_stmt_fetch *read_fetch_direction(void); static void complete_direction(PLpgSQL_stmt_fetch *fetch, bool *check_FROM); --- 77,84 ---- int *endtoken); static PLpgSQL_expr *read_sql_stmt(const char *sqlstart); static PLpgSQL_type *read_datatype(int tok); ! static PLpgSQL_stmt *make_execsql_stmt(int firsttoken, int location, ! bool have_strict); static PLpgSQL_stmt_fetch *read_fetch_direction(void); static void complete_direction(PLpgSQL_stmt_fetch *fetch, bool *check_FROM); *************** *** 1782,1788 **** loop_body : proc_sect K_END K_LOOP opt_label ';' */ stmt_execsql : K_INSERT { ! $$ = make_execsql_stmt(K_INSERT, @1); } | T_WORD { --- 1783,1796 ---- */ stmt_execsql : K_INSERT { ! $$ = make_execsql_stmt(K_INSERT, @1, false); ! } ! | K_STRICT ! { ! int tok; ! ! tok = yylex(); ! $$ = make_execsql_stmt(tok, yylloc, true); } | T_WORD { *************** *** 1792,1798 **** stmt_execsql : K_INSERT plpgsql_push_back_token(tok); if (tok == '=' || tok == COLON_EQUALS || tok == '[') word_is_not_variable(&($1), @1); ! $$ = make_execsql_stmt(T_WORD, @1); } | T_CWORD { --- 1800,1806 ---- plpgsql_push_back_token(tok); if (tok == '=' || tok == COLON_EQUALS || tok == '[') word_is_not_variable(&($1), @1); ! $$ = make_execsql_stmt(T_WORD, @1, false); } | T_CWORD { *************** *** 1802,1808 **** stmt_execsql : K_INSERT plpgsql_push_back_token(tok); if (tok == '=' || tok == COLON_EQUALS || tok == '[') cword_is_not_variable(&($1), @1); ! $$ = make_execsql_stmt(T_CWORD, @1); } ; --- 1810,1816 ---- plpgsql_push_back_token(tok); if (tok == '=' || tok == COLON_EQUALS || tok == '[') cword_is_not_variable(&($1), @1); ! $$ = make_execsql_stmt(T_CWORD, @1, false); } ; *************** *** 2631,2637 **** read_datatype(int tok) } static PLpgSQL_stmt * ! make_execsql_stmt(int firsttoken, int location) { StringInfoData ds; IdentifierLookup save_IdentifierLookup; --- 2639,2645 ---- } static PLpgSQL_stmt * ! make_execsql_stmt(int firsttoken, int location, bool have_strict) { StringInfoData ds; IdentifierLookup save_IdentifierLookup; *************** *** 2642,2648 **** make_execsql_stmt(int firsttoken, int location) int tok; int prev_tok; bool have_into = false; - bool have_strict = false; int into_start_loc = -1; int into_end_loc = -1; --- 2650,2655 ---- *************** *** 2674,2679 **** make_execsql_stmt(int firsttoken, int location) --- 2681,2688 ---- if (tok == K_INTO && prev_tok != K_INSERT) { + if (have_strict) + yyerror("STRICT must be part of INTO clause of INTO is specified"); if (have_into) yyerror("INTO specified more than once"); have_into = true; *** a/src/test/regress/expected/plpgsql.out --- b/src/test/regress/expected/plpgsql.out *************** *** 3103,3108 **** end$$ language plpgsql; --- 3103,3173 ---- select footest(); ERROR: query returned more than one row CONTEXT: PL/pgSQL function footest() line 5 at EXECUTE statement + -- + -- Test STRICT without INTO + -- + create or replace function footest() returns void as $$ + begin + -- should work + strict select 1; + end$$ language plpgsql; + select footest(); + footest + --------- + + (1 row) + + create or replace function footest() returns void as $$ + begin + -- should fail, no rows + strict select 1 where false; + 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 $$ + begin + -- should fail, too many rows + strict select 1 from generate_series(1,2); + 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 $$ + begin + -- should work + strict insert into foo values(5,6); + end$$ language plpgsql; + select footest(); + footest + --------- + + (1 row) + + create or replace function footest() returns void as $$ + begin + -- should fail, no rows + strict insert into foo values(5,6) limit 0; + 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 $$ + begin + -- should fail, too many rows + strict insert into foo values(5,6),(7,8); + 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 $$ + begin + -- should fail, no INTO + strict insert into foo values(5,6) returning *; + end$$ language plpgsql; + select footest(); + ERROR: query has no destination for result data + CONTEXT: PL/pgSQL function footest() line 4 at SQL statement drop function footest(); -- test scrollable cursor support create function sc_test() returns setof integer as $$ *** a/src/test/regress/sql/plpgsql.sql --- b/src/test/regress/sql/plpgsql.sql *************** *** 2585,2590 **** end$$ language plpgsql; --- 2585,2650 ---- select footest(); + -- + -- Test STRICT without INTO + -- + + create or replace function footest() returns void as $$ + begin + -- should work + strict select 1; + end$$ language plpgsql; + + select footest(); + + create or replace function footest() returns void as $$ + begin + -- should fail, no rows + strict select 1 where false; + end$$ language plpgsql; + + select footest(); + + create or replace function footest() returns void as $$ + begin + -- should fail, too many rows + strict select 1 from generate_series(1,2); + end$$ language plpgsql; + + select footest(); + + create or replace function footest() returns void as $$ + begin + -- should work + strict insert into foo values(5,6); + end$$ language plpgsql; + + select footest(); + + create or replace function footest() returns void as $$ + begin + -- should fail, no rows + strict insert into foo values(5,6) limit 0; + end$$ language plpgsql; + + select footest(); + + create or replace function footest() returns void as $$ + begin + -- should fail, too many rows + strict insert into foo values(5,6),(7,8); + end$$ language plpgsql; + + select footest(); + + create or replace function footest() returns void as $$ + begin + -- should fail, no INTO + strict insert into foo values(5,6) returning *; + end$$ language plpgsql; + + select footest(); + drop function footest(); -- test scrollable cursor support