Hi I wrote some initial patch
Do you think so has sense to continue in this topic? Regards Pavel
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 77e7440..15b867fa 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -3616,6 +3616,24 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, long tcount; int rc; PLpgSQL_expr *expr = stmt->sqlstmt; + bool too_many_rows_check; + int too_many_rows_level; + + if (plpgsql_extra_errors & PLPGSQL_XCHECK_TOOMANYROWS) + { + too_many_rows_check = true; + too_many_rows_level = ERROR; + } + else if (plpgsql_extra_warnings & PLPGSQL_XCHECK_TOOMANYROWS) + { + too_many_rows_check = true; + too_many_rows_level = WARNING; + } + else + { + too_many_rows_check = false; + too_many_rows_level = NOTICE; + } /* * On the first call for this statement generate the plan, and detect @@ -3666,7 +3684,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, */ if (stmt->into) { - if (stmt->strict || stmt->mod_stmt) + if (stmt->strict || stmt->mod_stmt || too_many_rows_check) tcount = 2; else tcount = 1; @@ -3786,7 +3804,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, } else { - if (n > 1 && (stmt->strict || stmt->mod_stmt)) + if (n > 1 && (stmt->strict || stmt->mod_stmt || too_many_rows_check)) { char *errdetail; @@ -3795,7 +3813,7 @@ exec_stmt_execsql(PLpgSQL_execstate *estate, else errdetail = NULL; - ereport(ERROR, + ereport(too_many_rows_level == WARNING && !stmt->strict ? WARNING : ERROR, (errcode(ERRCODE_TOO_MANY_ROWS), errmsg("query returned more than one row"), errdetail ? errdetail_internal("parameters: %s", errdetail) : 0)); @@ -6009,12 +6027,48 @@ exec_move_row(PLpgSQL_execstate *estate, int t_natts; int fnum; int anum; + bool strict_multiassignment_check; + int strict_multiassignment_level; + + if (plpgsql_extra_errors & PLPGSQL_XCHECK_STRICTMULTIASSIGNMENT) + { + strict_multiassignment_check = true; + strict_multiassignment_level = ERROR; + } + else if (plpgsql_extra_warnings & PLPGSQL_XCHECK_STRICTMULTIASSIGNMENT) + { + strict_multiassignment_check = true; + strict_multiassignment_level = WARNING; + } + else + { + strict_multiassignment_check = false; + strict_multiassignment_level = NOTICE; + } if (HeapTupleIsValid(tup)) t_natts = HeapTupleHeaderGetNatts(tup->t_data); else t_natts = 0; + if (strict_multiassignment_check) + { + int i; + + anum = 0; + for (i = 0; i < td_natts; i++) + if (!tupdesc->attrs[i]->attisdropped) + anum++; + + if (anum != row->nfields) + { + ereport(strict_multiassignment_level, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("Number of evaluated attributies (%d) does not match expected attributies (%d)", + anum, row->nfields))); + } + } + anum = 0; for (fnum = 0; fnum < row->nfields; fnum++) { diff --git a/src/pl/plpgsql/src/pl_handler.c b/src/pl/plpgsql/src/pl_handler.c index 36868fb..09bec86 100644 --- a/src/pl/plpgsql/src/pl_handler.c +++ b/src/pl/plpgsql/src/pl_handler.c @@ -89,6 +89,10 @@ plpgsql_extra_checks_check_hook(char **newvalue, void **extra, GucSource source) if (pg_strcasecmp(tok, "shadowed_variables") == 0) extrachecks |= PLPGSQL_XCHECK_SHADOWVAR; + else if (pg_strcasecmp(tok, "too_many_rows") == 0) + extrachecks |= PLPGSQL_XCHECK_TOOMANYROWS; + else if (pg_strcasecmp(tok, "strict_multi_assignment") == 0) + extrachecks |= PLPGSQL_XCHECK_STRICTMULTIASSIGNMENT; else if (pg_strcasecmp(tok, "all") == 0 || pg_strcasecmp(tok, "none") == 0) { GUC_check_errdetail("Key word \"%s\" cannot be combined with other key words.", tok); diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index c84a97b..820afe4 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -1025,7 +1025,9 @@ extern bool plpgsql_check_asserts; /* extra compile-time checks */ #define PLPGSQL_XCHECK_NONE 0 -#define PLPGSQL_XCHECK_SHADOWVAR 1 +#define PLPGSQL_XCHECK_SHADOWVAR (1 << 1) +#define PLPGSQL_XCHECK_TOOMANYROWS (1 << 2) +#define PLPGSQL_XCHECK_STRICTMULTIASSIGNMENT (1 << 3) #define PLPGSQL_XCHECK_ALL ((int) ~0) extern int plpgsql_extra_warnings; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 79513e4..b09e83a 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -3422,6 +3422,54 @@ select shadowtest(1); t (1 row) +-- runtime extra checks +set plpgsql.extra_warnings to 'too_many_rows'; +do $$ +declare x int; +begin + select v from generate_series(1,2) g(v) into x; +end; +$$; +WARNING: query returned more than one row +set plpgsql.extra_errors to 'too_many_rows'; +do $$ +declare x int; +begin + select v from generate_series(1,2) g(v) into x; +end; +$$; +ERROR: query returned more than one row +CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; +set plpgsql.extra_warnings to 'strict_multi_assignment'; +do $$ +declare + x int; + y int; +begin + select 1 into x, y; + select 1,2 into x, y; + select 1,2,3 into x, y; +end +$$; +WARNING: Number of evaluated attributies (1) does not match expected attributies (2) +WARNING: Number of evaluated attributies (3) does not match expected attributies (2) +set plpgsql.extra_errors to 'strict_multi_assignment'; +do $$ +declare + x int; + y int; +begin + select 1 into x, y; + select 1,2 into x, y; + select 1,2,3 into x, y; +end +$$; +ERROR: Number of evaluated attributies (1) does not match expected attributies (2) +CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; -- test scrollable cursor support create function sc_test() returns setof integer as $$ declare diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 877d3ad..aa47e93 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -2840,6 +2840,57 @@ declare f1 int; begin return 1; end $$ language plpgsql; select shadowtest(1); +-- runtime extra checks +set plpgsql.extra_warnings to 'too_many_rows'; + +do $$ +declare x int; +begin + select v from generate_series(1,2) g(v) into x; +end; +$$; + +set plpgsql.extra_errors to 'too_many_rows'; + +do $$ +declare x int; +begin + select v from generate_series(1,2) g(v) into x; +end; +$$; + +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; + +set plpgsql.extra_warnings to 'strict_multi_assignment'; + +do $$ +declare + x int; + y int; +begin + select 1 into x, y; + select 1,2 into x, y; + select 1,2,3 into x, y; +end +$$; + +set plpgsql.extra_errors to 'strict_multi_assignment'; + +do $$ +declare + x int; + y int; +begin + select 1 into x, y; + select 1,2 into x, y; + select 1,2,3 into x, y; +end +$$; + +reset plpgsql.extra_errors; +reset plpgsql.extra_warnings; + -- test scrollable cursor support create function sc_test() returns setof integer as $$
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers