Skip to content

Commit 689746c

Browse files
robertmhaasrobertmhaas
committed
plpgsql: Add new option print_strict_params.
This option provides more detailed error messages when STRICT is used and the number of rows returned is not one. Marko Tiikkaja, reviewed by Ian Lawrence Barwick
1 parent c01262a commit 689746c

File tree

9 files changed

+426
-8
lines changed

9 files changed

+426
-8
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1076,6 +1076,30 @@ END;
10761076
always sets <literal>FOUND</literal> to true.
10771077
</para>
10781078

1079+
<para>
1080+
If <literal>print_strict_params</> is enabled for the function,
1081+
you will get information about the parameters passed to the
1082+
query in the <literal>DETAIL</> part of the error message produced
1083+
when the requirements of STRICT are not met. You can change this
1084+
setting on a system-wide basis by setting
1085+
<varname>plpgsql.print_strict_params</>, though only subsequent
1086+
function compilations will be affected. You can also enable it
1087+
on a per-function basis by using a compiler option:
1088+
<programlisting>
1089+
CREATE FUNCTION get_userid(username text) RETURNS int
1090+
AS $$
1091+
#print_strict_params on
1092+
DECLARE
1093+
userid int;
1094+
BEGIN
1095+
SELECT users.userid INTO STRICT userid
1096+
FROM users WHERE users.username = get_userid.username;
1097+
RETURN userid;
1098+
END
1099+
$$ LANGUAGE plpgsql;
1100+
</programlisting>
1101+
</para>
1102+
10791103
<para>
10801104
For <command>INSERT</>/<command>UPDATE</>/<command>DELETE</> with
10811105
<literal>RETURNING</>, <application>PL/pgSQL</application> reports

‎src/pl/plpgsql/src/pl_comp.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -351,6 +351,7 @@ do_compile(FunctionCallInfo fcinfo,
351351
function->fn_cxt = func_cxt;
352352
function->out_param_varno = -1; /* set up for no OUT param */
353353
function->resolve_option = plpgsql_variable_conflict;
354+
function->print_strict_params = plpgsql_print_strict_params;
354355

355356
if (is_dml_trigger)
356357
function->fn_is_trigger = PLPGSQL_DML_TRIGGER;
@@ -847,6 +848,7 @@ plpgsql_compile_inline(char *proc_source)
847848
function->fn_cxt = func_cxt;
848849
function->out_param_varno = -1; /* set up for no OUT param */
849850
function->resolve_option = plpgsql_variable_conflict;
851+
function->print_strict_params = plpgsql_print_strict_params;
850852

851853
plpgsql_ns_init();
852854
plpgsql_ns_push(func_name);

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 158 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -221,6 +221,11 @@ static Portal exec_dynquery_with_params(PLpgSQL_execstate *estate,
221221
PLpgSQL_expr *dynquery, List *params,
222222
const char *portalname, int cursorOptions);
223223

224+
static char *format_expr_params(PLpgSQL_execstate *estate,
225+
const PLpgSQL_expr *expr);
226+
static char *format_preparedparamsdata(PLpgSQL_execstate *estate,
227+
const PreparedParamsData *ppd);
228+
224229

225230
/* ----------
226231
* plpgsql_exec_function Called by the call handler for
@@ -3391,18 +3396,40 @@ exec_stmt_execsql(PLpgSQL_execstate *estate,
33913396
if (n == 0)
33923397
{
33933398
if (stmt->strict)
3399+
{
3400+
char *errdetail;
3401+
3402+
if (estate->func->print_strict_params)
3403+
errdetail = format_expr_params(estate, expr);
3404+
else
3405+
errdetail = NULL;
3406+
33943407
ereport(ERROR,
33953408
(errcode(ERRCODE_NO_DATA_FOUND),
3396-
errmsg("query returned no rows")));
3409+
errmsg("query returned no rows"),
3410+
errdetail ?
3411+
errdetail_internal("parameters: %s", errdetail) : 0));
3412+
}
33973413
/* set the target to NULL(s) */
33983414
exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
33993415
}
34003416
else
34013417
{
34023418
if (n > 1 && (stmt->strict || stmt->mod_stmt))
3419+
{
3420+
char *errdetail;
3421+
3422+
if (estate->func->print_strict_params)
3423+
errdetail = format_expr_params(estate, expr);
3424+
else
3425+
errdetail = NULL;
3426+
34033427
ereport(ERROR,
34043428
(errcode(ERRCODE_TOO_MANY_ROWS),
3405-
errmsg("query returned more than one row")));
3429+
errmsg("query returned more than one row"),
3430+
errdetail ?
3431+
errdetail_internal("parameters: %s", errdetail) : 0));
3432+
}
34063433
/* Put the first result row into the target */
34073434
exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
34083435
}
@@ -3442,6 +3469,7 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
34423469
Oid restype;
34433470
char *querystr;
34443471
int exec_res;
3472+
PreparedParamsData *ppd = NULL;
34453473

34463474
/*
34473475
* First we evaluate the string expression after the EXECUTE keyword. Its
@@ -3466,14 +3494,11 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
34663494
*/
34673495
if (stmt->params)
34683496
{
3469-
PreparedParamsData *ppd;
3470-
34713497
ppd = exec_eval_using_params(estate, stmt->params);
34723498
exec_res = SPI_execute_with_args(querystr,
34733499
ppd->nargs, ppd->types,
34743500
ppd->values, ppd->nulls,
34753501
estate->readonly_func, 0);
3476-
free_params_data(ppd);
34773502
}
34783503
else
34793504
exec_res = SPI_execute(querystr, estate->readonly_func, 0);
@@ -3565,18 +3590,41 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
35653590
if (n == 0)
35663591
{
35673592
if (stmt->strict)
3593+
{
3594+
char *errdetail;
3595+
3596+
if (estate->func->print_strict_params)
3597+
errdetail = format_preparedparamsdata(estate, ppd);
3598+
else
3599+
errdetail = NULL;
3600+
35683601
ereport(ERROR,
35693602
(errcode(ERRCODE_NO_DATA_FOUND),
3570-
errmsg("query returned no rows")));
3603+
errmsg("query returned no rows"),
3604+
errdetail ?
3605+
errdetail_internal("parameters: %s", errdetail) : 0));
3606+
}
35713607
/* set the target to NULL(s) */
35723608
exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);
35733609
}
35743610
else
35753611
{
35763612
if (n > 1 && stmt->strict)
3613+
{
3614+
char *errdetail;
3615+
3616+
if (estate->func->print_strict_params)
3617+
errdetail = format_preparedparamsdata(estate, ppd);
3618+
else
3619+
errdetail = NULL;
3620+
35773621
ereport(ERROR,
35783622
(errcode(ERRCODE_TOO_MANY_ROWS),
3579-
errmsg("query returned more than one row")));
3623+
errmsg("query returned more than one row"),
3624+
errdetail ?
3625+
errdetail_internal("parameters: %s", errdetail) : 0));
3626+
}
3627+
35803628
/* Put the first result row into the target */
35813629
exec_move_row(estate, rec, row, tuptab->vals[0], tuptab->tupdesc);
35823630
}
@@ -3592,6 +3640,9 @@ exec_stmt_dynexecute(PLpgSQL_execstate *estate,
35923640
*/
35933641
}
35943642

3643+
if (ppd)
3644+
free_params_data(ppd);
3645+
35953646
/* Release any result from SPI_execute, as well as the querystring */
35963647
SPI_freetuptable(SPI_tuptable);
35973648
pfree(querystr);
@@ -6456,3 +6507,103 @@ exec_dynquery_with_params(PLpgSQL_execstate *estate,
64566507

64576508
return portal;
64586509
}
6510+
6511+
/*
6512+
* Return a formatted string with information about an expression's parameters,
6513+
* or NULL if the expression does not take any parameters.
6514+
*/
6515+
static char *
6516+
format_expr_params(PLpgSQL_execstate *estate,
6517+
const PLpgSQL_expr *expr)
6518+
{
6519+
int paramno;
6520+
int dno;
6521+
StringInfoData paramstr;
6522+
Bitmapset *tmpset;
6523+
6524+
if (!expr->paramnos)
6525+
return NULL;
6526+
6527+
initStringInfo(&paramstr);
6528+
tmpset = bms_copy(expr->paramnos);
6529+
paramno = 0;
6530+
while ((dno = bms_first_member(tmpset)) >= 0)
6531+
{
6532+
Datum paramdatum;
6533+
Oid paramtypeid;
6534+
bool paramisnull;
6535+
int32 paramtypmod;
6536+
PLpgSQL_var *curvar;
6537+
6538+
curvar = (PLpgSQL_var *) estate->datums[dno];
6539+
6540+
exec_eval_datum(estate, (PLpgSQL_datum *) curvar, &paramtypeid,
6541+
&paramtypmod, &paramdatum, &paramisnull);
6542+
6543+
appendStringInfo(&paramstr, "%s%s = ",
6544+
paramno > 0 ? ", " : "",
6545+
curvar->refname);
6546+
6547+
if (paramisnull)
6548+
appendStringInfoString(&paramstr, "NULL");
6549+
else
6550+
{
6551+
char *value = convert_value_to_string(estate, paramdatum, paramtypeid);
6552+
char *p;
6553+
appendStringInfoCharMacro(&paramstr, '\'');
6554+
for (p = value; *p; p++)
6555+
{
6556+
if (*p == '\'') /* double single quotes */
6557+
appendStringInfoCharMacro(&paramstr, *p);
6558+
appendStringInfoCharMacro(&paramstr, *p);
6559+
}
6560+
appendStringInfoCharMacro(&paramstr, '\'');
6561+
}
6562+
6563+
paramno++;
6564+
}
6565+
bms_free(tmpset);
6566+
6567+
return paramstr.data;
6568+
}
6569+
6570+
/*
6571+
* Return a formatted string with information about PreparedParamsData, or NULL
6572+
* if the there are no parameters.
6573+
*/
6574+
static char *
6575+
format_preparedparamsdata(PLpgSQL_execstate *estate,
6576+
const PreparedParamsData *ppd)
6577+
{
6578+
int paramno;
6579+
StringInfoData paramstr;
6580+
6581+
if (!ppd)
6582+
return NULL;
6583+
6584+
initStringInfo(&paramstr);
6585+
for (paramno = 0; paramno < ppd->nargs; paramno++)
6586+
{
6587+
appendStringInfo(&paramstr, "%s$%d = ",
6588+
paramno > 0 ? ", " : "",
6589+
paramno + 1);
6590+
6591+
if (ppd->nulls[paramno] == 'n')
6592+
appendStringInfoString(&paramstr, "NULL");
6593+
else
6594+
{
6595+
char *value = convert_value_to_string(estate, ppd->values[paramno], ppd->types[paramno]);
6596+
char *p;
6597+
appendStringInfoCharMacro(&paramstr, '\'');
6598+
for (p = value; *p; p++)
6599+
{
6600+
if (*p == '\'') /* double single quotes */
6601+
appendStringInfoCharMacro(&paramstr, *p);
6602+
appendStringInfoCharMacro(&paramstr, *p);
6603+
}
6604+
appendStringInfoCharMacro(&paramstr, '\'');
6605+
}
6606+
}
6607+
6608+
return paramstr.data;
6609+
}

‎src/pl/plpgsql/src/pl_gram.y

Lines changed: 21 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -185,7 +185,7 @@ static List *read_raise_options(void);
185185
%type <forvariable> for_variable
186186
%type <stmt> for_control
187187

188-
%type <str> any_identifier opt_block_label opt_label
188+
%type <str> any_identifier opt_block_label opt_label option_value
189189

190190
%type <list> proc_sect proc_stmts stmt_elsifs stmt_else
191191
%type <loop_body> loop_body
@@ -308,6 +308,7 @@ static List *read_raise_options(void);
308308
%token <keyword> K_PG_EXCEPTION_CONTEXT
309309
%token <keyword> K_PG_EXCEPTION_DETAIL
310310
%token <keyword> K_PG_EXCEPTION_HINT
311+
%token <keyword> K_PRINT_STRICT_PARAMS
311312
%token <keyword> K_PRIOR
312313
%token <keyword> K_QUERY
313314
%token <keyword> K_RAISE
@@ -354,6 +355,15 @@ comp_option : '#' K_OPTION K_DUMP
354355
{
355356
plpgsql_DumpExecTree = true;
356357
}
358+
| '#' K_PRINT_STRICT_PARAMS option_value
359+
{
360+
if (strcmp($3, "on") == 0)
361+
plpgsql_curr_compile->print_strict_params = true;
362+
else if (strcmp($3, "off") == 0)
363+
plpgsql_curr_compile->print_strict_params = false;
364+
else
365+
elog(ERROR, "unrecognized print_strict_params option %s", $3);
366+
}
357367
| '#' K_VARIABLE_CONFLICT K_ERROR
358368
{
359369
plpgsql_curr_compile->resolve_option = PLPGSQL_RESOLVE_ERROR;
@@ -368,6 +378,15 @@ comp_option : '#' K_OPTION K_DUMP
368378
}
369379
;
370380

381+
option_value : T_WORD
382+
{
383+
$$ = $1.ident;
384+
}
385+
| unreserved_keyword
386+
{
387+
$$ = pstrdup($1);
388+
}
389+
371390
opt_semi :
372391
| ';'
373392
;
@@ -2300,6 +2319,7 @@ unreserved_keyword :
23002319
| K_PG_EXCEPTION_DETAIL
23012320
| K_PG_EXCEPTION_HINT
23022321
| K_PRIOR
2322+
| K_PRINT_STRICT_PARAMS
23032323
| K_QUERY
23042324
| K_RELATIVE
23052325
| K_RESULT_OID

‎src/pl/plpgsql/src/pl_handler.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -37,6 +37,8 @@ static const struct config_enum_entry variable_conflict_options[] = {
3737

3838
int plpgsql_variable_conflict = PLPGSQL_RESOLVE_ERROR;
3939

40+
bool plpgsql_print_strict_params = false;
41+
4042
/* Hook for plugins */
4143
PLpgSQL_plugin **plugin_ptr = NULL;
4244

@@ -66,6 +68,14 @@ _PG_init(void)
6668
PGC_SUSET, 0,
6769
NULL, NULL, NULL);
6870

71+
DefineCustomBoolVariable("plpgsql.print_strict_params",
72+
gettext_noop("Print information about parameters in the DETAIL part of the error messages generated on INTO .. STRICT failures."),
73+
NULL,
74+
&plpgsql_print_strict_params,
75+
false,
76+
PGC_USERSET, 0,
77+
NULL, NULL, NULL);
78+
6979
EmitWarningsOnPlaceholders("plpgsql");
7080

7181
plpgsql_HashTableInit();

‎src/pl/plpgsql/src/pl_scanner.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,7 @@ static const ScanKeyword unreserved_keywords[] = {
140140
PG_KEYWORD("pg_exception_context", K_PG_EXCEPTION_CONTEXT, UNRESERVED_KEYWORD)
141141
PG_KEYWORD("pg_exception_detail", K_PG_EXCEPTION_DETAIL, UNRESERVED_KEYWORD)
142142
PG_KEYWORD("pg_exception_hint", K_PG_EXCEPTION_HINT, UNRESERVED_KEYWORD)
143+
PG_KEYWORD("print_strict_params", K_PRINT_STRICT_PARAMS, UNRESERVED_KEYWORD)
143144
PG_KEYWORD("prior", K_PRIOR, UNRESERVED_KEYWORD)
144145
PG_KEYWORD("query", K_QUERY, UNRESERVED_KEYWORD)
145146
PG_KEYWORD("relative", K_RELATIVE, UNRESERVED_KEYWORD)

‎src/pl/plpgsql/src/plpgsql.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -737,6 +737,8 @@ typedef struct PLpgSQL_function
737737

738738
PLpgSQL_resolve_option resolve_option;
739739

740+
bool print_strict_params;
741+
740742
int ndatums;
741743
PLpgSQL_datum **datums;
742744
PLpgSQL_stmt_block *action;
@@ -873,6 +875,8 @@ extern IdentifierLookup plpgsql_IdentifierLookup;
873875

874876
extern int plpgsql_variable_conflict;
875877

878+
extern bool plpgsql_print_strict_params;
879+
876880
extern bool plpgsql_check_syntax;
877881
extern bool plpgsql_DumpExecTree;
878882

0 commit comments

Comments
 (0)