Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit689746c

Browse files
committed
plpgsql: Add new option print_strict_params.
This option provides more detailed error messages when STRICT is usedand the number of rows returned is not one.Marko Tiikkaja, reviewed by Ian Lawrence Barwick
1 parentc01262a commit689746c

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
constchar*portalname,intcursorOptions);
223223

224+
staticchar*format_expr_params(PLpgSQL_execstate*estate,
225+
constPLpgSQL_expr*expr);
226+
staticchar*format_preparedparamsdata(PLpgSQL_execstate*estate,
227+
constPreparedParamsData*ppd);
228+
224229

225230
/* ----------
226231
* plpgsql_exec_functionCalled 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
Oidrestype;
34433470
char*querystr;
34443471
intexec_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
returnportal;
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+
staticchar*
6516+
format_expr_params(PLpgSQL_execstate*estate,
6517+
constPLpgSQL_expr*expr)
6518+
{
6519+
intparamno;
6520+
intdno;
6521+
StringInfoDataparamstr;
6522+
Bitmapset*tmpset;
6523+
6524+
if (!expr->paramnos)
6525+
returnNULL;
6526+
6527+
initStringInfo(&paramstr);
6528+
tmpset=bms_copy(expr->paramnos);
6529+
paramno=0;
6530+
while ((dno=bms_first_member(tmpset)) >=0)
6531+
{
6532+
Datumparamdatum;
6533+
Oidparamtypeid;
6534+
boolparamisnull;
6535+
int32paramtypmod;
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+
returnparamstr.data;
6568+
}
6569+
6570+
/*
6571+
* Return a formatted string with information about PreparedParamsData, or NULL
6572+
* if the there are no parameters.
6573+
*/
6574+
staticchar*
6575+
format_preparedparamsdata(PLpgSQL_execstate*estate,
6576+
constPreparedParamsData*ppd)
6577+
{
6578+
intparamno;
6579+
StringInfoDataparamstr;
6580+
6581+
if (!ppd)
6582+
returnNULL;
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+
returnparamstr.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 @@ staticList*read_raise_options(void);
185185
%type<forvariable>for_variable
186186
%type<stmt>for_control
187187

188-
%type<str>any_identifieropt_block_labelopt_label
188+
%type<str>any_identifieropt_block_labelopt_labeloption_value
189189

190190
%type<list>proc_sectproc_stmtsstmt_elsifsstmt_else
191191
%type<loop_body>loop_body
@@ -308,6 +308,7 @@ staticList*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_PARAMSoption_value
359+
{
360+
if (strcmp($3,"on") ==0)
361+
plpgsql_curr_compile->print_strict_params =true;
362+
elseif (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_CONFLICTK_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
intplpgsql_variable_conflict=PLPGSQL_RESOLVE_ERROR;
3939

40+
boolplpgsql_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_optionresolve_option;
739739

740+
boolprint_strict_params;
741+
740742
intndatums;
741743
PLpgSQL_datum**datums;
742744
PLpgSQL_stmt_block*action;
@@ -873,6 +875,8 @@ extern IdentifierLookup plpgsql_IdentifierLookup;
873875

874876
externintplpgsql_variable_conflict;
875877

878+
externboolplpgsql_print_strict_params;
879+
876880
externboolplpgsql_check_syntax;
877881
externboolplpgsql_DumpExecTree;
878882

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp