Hi,
Courtesy of me, Christmas comes a bit early this year. I wrote a patch
which allows you to add STRICT into PERFORM and INSERT/UPDATE/DELETE
without specifying an INTO clause. Observe:
=# create table foo(a int);
CREATE TABLE
=# create function foof() returns void as $$ begin update strict foo set
a=a+1; end $$ language plpgsql;
CREATE FUNCTION
=# select foof();
ERROR: query returned no rows
I know everyone obviously wants this, so I will be sending another
version with regression tests and documentation later. The code is a
bit ugly at places, but I'm going to work on that too.
Regards,
Marko Tiikkaja
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***************
*** 1500,1508 **** static int
exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt)
{
PLpgSQL_expr *expr = stmt->expr;
(void) exec_run_select(estate, expr, 0, NULL);
! exec_set_found(estate, (estate->eval_processed != 0));
exec_eval_cleanup(estate);
return PLPGSQL_RC_OK;
--- 1500,1519 ----
exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt)
{
PLpgSQL_expr *expr = stmt->expr;
+ uint32 n;
(void) exec_run_select(estate, expr, 0, NULL);
! n = estate->eval_processed;
! if (stmt->strict && n == 0)
! ereport(ERROR,
! (errcode(ERRCODE_NO_DATA_FOUND),
! errmsg("query returned no rows")));
! else if (stmt->strict && n > 1)
! ereport(ERROR,
! (errcode(ERRCODE_TOO_MANY_ROWS),
! errmsg("query returned more than one row")));
!
! exec_set_found(estate, (n != 0));
exec_eval_cleanup(estate);
return PLPGSQL_RC_OK;
***************
*** 3211,3217 **** exec_stmt_execsql(PLpgSQL_execstate *estate,
* 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;
--- 3222,3228 ----
* 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;
***************
*** 3335,3340 **** exec_stmt_execsql(PLpgSQL_execstate *estate,
--- 3346,3366 ----
exec_eval_cleanup(estate);
SPI_freetuptable(SPI_tuptable);
}
+ else if (stmt->strict)
+ {
+ /*
+ * If a mod stmt 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")));
+ }
else
{
/* If the statement returned a tuple table, complain */
*** a/src/pl/plpgsql/src/pl_funcs.c
--- b/src/pl/plpgsql/src/pl_funcs.c
***************
*** 1187,1193 **** static void
dump_perform(PLpgSQL_stmt_perform *stmt)
{
dump_ind();
! printf("PERFORM expr = ");
dump_expr(stmt->expr);
printf("\n");
}
--- 1187,1193 ----
dump_perform(PLpgSQL_stmt_perform *stmt)
{
dump_ind();
! printf("PERFORM%s expr = ", stmt->strict ? " STRICT" : "");
dump_expr(stmt->expr);
printf("\n");
}
*** a/src/pl/plpgsql/src/pl_gram.y
--- b/src/pl/plpgsql/src/pl_gram.y
***************
*** 178,183 **** static List *read_raise_options(void);
--- 178,184 ----
%type <expr> expr_until_semi expr_until_rightbracket
%type <expr> expr_until_then expr_until_loop opt_expr_until_when
%type <expr> opt_exitcond
+ %type <boolean> opt_strict
%type <ival> assign_var foreach_slice
%type <var> cursor_variable
***************
*** 834,847 **** proc_stmt : pl_block ';'
{ $$ = $1; }
;
! stmt_perform : K_PERFORM expr_until_semi
{
PLpgSQL_stmt_perform *new;
new =
palloc0(sizeof(PLpgSQL_stmt_perform));
new->cmd_type =
PLPGSQL_STMT_PERFORM;
new->lineno =
plpgsql_location_to_lineno(@1);
! new->expr = $2;
$$ = (PLpgSQL_stmt *)new;
}
--- 835,849 ----
{ $$ = $1; }
;
! stmt_perform : K_PERFORM opt_strict expr_until_semi
{
PLpgSQL_stmt_perform *new;
new =
palloc0(sizeof(PLpgSQL_stmt_perform));
new->cmd_type =
PLPGSQL_STMT_PERFORM;
new->lineno =
plpgsql_location_to_lineno(@1);
! new->strict = $2;
! new->expr = $3;
$$ = (PLpgSQL_stmt *)new;
}
***************
*** 2207,2212 **** opt_exitcond : ';'
--- 2209,2223 ----
{ $$ = $2; }
;
+ opt_strict :
+ {
+ $$ = false;
+ }
+ | K_STRICT
+ {
+ $$ = true;
+ }
+
/*
* need to allow DATUM because scanner will have tried to resolve as variable
*/
***************
*** 2665,2679 **** make_execsql_stmt(int firsttoken, int location)
{
prev_tok = tok;
tok = yylex();
! if (have_into && into_end_loc < 0)
! into_end_loc = yylloc; /* token after the INTO
part */
if (tok == ';')
break;
if (tok == 0)
yyerror("unexpected end of function definition");
if (tok == K_INTO && prev_tok != K_INSERT)
{
if (have_into)
yyerror("INTO specified more than once");
have_into = true;
--- 2676,2698 ----
{
prev_tok = tok;
tok = yylex();
! if ((have_strict || have_into) && into_end_loc < 0)
! into_end_loc = yylloc; /* token after the INTO
(or STRICT) part */
if (tok == ';')
break;
if (tok == 0)
yyerror("unexpected end of function definition");
+ if (tok == K_STRICT)
+ {
+ into_start_loc = yylloc;
+ have_strict = true;
+ }
+
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;
***************
*** 2686,2692 **** make_execsql_stmt(int firsttoken, int location)
plpgsql_IdentifierLookup = save_IdentifierLookup;
! if (have_into)
{
/*
* Insert an appropriate number of spaces corresponding to the
--- 2705,2711 ----
plpgsql_IdentifierLookup = save_IdentifierLookup;
! if (have_strict || have_into)
{
/*
* Insert an appropriate number of spaces corresponding to the
*** a/src/pl/plpgsql/src/plpgsql.h
--- b/src/pl/plpgsql/src/plpgsql.h
***************
*** 375,380 **** typedef struct
--- 375,381 ----
{ /* PERFORM
statement */
int cmd_type;
int lineno;
+ bool strict;
PLpgSQL_expr *expr;
} PLpgSQL_stmt_perform;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
