Hello,
I propose to add a new value "no_data_found" for the
plpgsql.extra_errors and plpgsql.extra_warnings parameters [1].
With plpgsql.extra_errors=no_data_found SELECT INTO raises no_data_found
exception when the result set is empty. With
plpgsql.extra_errors=too_many_rows,no_data_found SELECT INTO behaves
like SELECT INTO STRICT [2]. This could simplify migration from PL/SQL
and may be just more convenient.
One potential downside is that plpgsql.extra_errors=no_data_found could
break existing functions expecting to get null or checking IF found
explicitly. This is also true for the too_many_rows exception, but
arguably it's a programmer error, while no_data_found switches to a
different convention for handling (or not handling) an empty result with
SELECT INTO.
Otherwise the patch is straightforward.
What do you think?
--
Sergey Shinderuk https://postgrespro.com/
[1]
https://www.postgresql.org/docs/devel/plpgsql-development-tips.html#PLPGSQL-EXTRA-CHECKS
[2]
https://www.postgresql.org/docs/devel/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROWdiff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index a6473429480..80672a3672f 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -4197,8 +4197,14 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
long tcount;
int rc;
PLpgSQL_expr *expr = stmt->sqlstmt;
+ int no_data_found_level = 0;
int too_many_rows_level = 0;
+ if (plpgsql_extra_errors & PLPGSQL_XCHECK_NODATAFOUND)
+ no_data_found_level = ERROR;
+ else if (plpgsql_extra_warnings & PLPGSQL_XCHECK_NODATAFOUND)
+ no_data_found_level = WARNING;
+
if (plpgsql_extra_errors & PLPGSQL_XCHECK_TOOMANYROWS)
too_many_rows_level = ERROR;
else if (plpgsql_extra_warnings & PLPGSQL_XCHECK_TOOMANYROWS)
@@ -4355,16 +4361,19 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
*/
if (n == 0)
{
- if (stmt->strict)
+ if (stmt->strict || no_data_found_level)
{
char *errdetail;
+ int errlevel;
if (estate->func->print_strict_params)
errdetail = format_expr_params(estate, expr);
else
errdetail = NULL;
- ereport(ERROR,
+ errlevel = stmt->strict ? ERROR : no_data_found_level;
+
+ ereport(errlevel,
(errcode(ERRCODE_NO_DATA_FOUND),
errmsg("query returned no rows"),
errdetail ? errdetail_internal("parameters: %s", errdetail) : 0));
diff --git a/src/pl/plpgsql/src/pl_handler.c b/src/pl/plpgsql/src/pl_handler.c
index 5dc334b61b3..80151540c38 100644
--- a/src/pl/plpgsql/src/pl_handler.c
+++ b/src/pl/plpgsql/src/pl_handler.c
@@ -90,6 +90,8 @@ 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, "no_data_found") == 0)
+ extrachecks |= PLPGSQL_XCHECK_NODATAFOUND;
else if (pg_strcasecmp(tok, "too_many_rows") == 0)
extrachecks |= PLPGSQL_XCHECK_TOOMANYROWS;
else if (pg_strcasecmp(tok, "strict_multi_assignment") == 0)
diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h
index 088768867d9..b69c058ba86 100644
--- a/src/pl/plpgsql/src/plpgsql.h
+++ b/src/pl/plpgsql/src/plpgsql.h
@@ -1202,8 +1202,9 @@ extern bool plpgsql_check_asserts;
/* extra compile-time and run-time checks */
#define PLPGSQL_XCHECK_NONE 0
#define PLPGSQL_XCHECK_SHADOWVAR (1 << 1)
-#define PLPGSQL_XCHECK_TOOMANYROWS (1 << 2)
-#define PLPGSQL_XCHECK_STRICTMULTIASSIGNMENT (1 << 3)
+#define PLPGSQL_XCHECK_NODATAFOUND (1 << 2)
+#define PLPGSQL_XCHECK_TOOMANYROWS (1 << 3)
+#define PLPGSQL_XCHECK_STRICTMULTIASSIGNMENT (1 << 4)
#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 08e42f17dc2..454e517fcc9 100644
--- a/src/test/regress/expected/plpgsql.out
+++ b/src/test/regress/expected/plpgsql.out
@@ -3086,6 +3086,25 @@ select shadowtest(1);
(1 row)
-- runtime extra checks
+set plpgsql.extra_warnings to 'no_data_found';
+do $$
+declare x int;
+begin
+ select 1 into x where 0 = 1;
+end;
+$$;
+WARNING: query returned no rows
+set plpgsql.extra_errors to 'no_data_found';
+do $$
+declare x int;
+begin
+ select 1 into x where 0 = 1;
+end;
+$$;
+ERROR: query returned no rows
+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 'too_many_rows';
do $$
declare x int;
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
index 588c3310337..60896b3d0da 100644
--- a/src/test/regress/sql/plpgsql.sql
+++ b/src/test/regress/sql/plpgsql.sql
@@ -2620,6 +2620,27 @@ declare f1 int; begin return 1; end $$ language plpgsql;
select shadowtest(1);
-- runtime extra checks
+set plpgsql.extra_warnings to 'no_data_found';
+
+do $$
+declare x int;
+begin
+ select 1 into x where 0 = 1;
+end;
+$$;
+
+set plpgsql.extra_errors to 'no_data_found';
+
+do $$
+declare x int;
+begin
+ select 1 into x where 0 = 1;
+end;
+$$;
+
+reset plpgsql.extra_errors;
+reset plpgsql.extra_warnings;
+
set plpgsql.extra_warnings to 'too_many_rows';
do $$
