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

Commitd663654

Browse files
author
Neil Conway
committed
Allow the parameters to PL/PgSQL's RAISE statement to be expressions,
instead of just scalar variables. Add regression tests and update thedocumentation. Along the way, remove some redundant error checkingcode from exec_stmt_perform().Original patch from Pavel Stehule, reworked by Neil Conway.
1 parentbd6bf50 commitd663654

File tree

7 files changed

+124
-89
lines changed

7 files changed

+124
-89
lines changed

‎doc/src/sgml/plpgsql.sgml‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.71 2005/06/10 16:23:09 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/plpgsql.sgml,v 1.72 2005/06/14 06:43:14 neilc Exp $
33
-->
44

55
<chapter id="plpgsql">
@@ -2533,9 +2533,9 @@ RAISE <replaceable class="parameter">level</replaceable> '<replaceable class="pa
25332533
<para>
25342534
Inside the format string, <literal>%</literal> is replaced by the
25352535
next optional argument's string representation. Write
2536-
<literal>%%</literal> to emit a literal <literal>%</literal>.Note
2537-
that the optional arguments must presentlybe simple variables,
2538-
not expressions,and the format must be a simple string literal.
2536+
<literal>%%</literal> to emit a literal <literal>%</literal>.
2537+
Arguments canbe simple variables or expressions,
2538+
and the format must be a simple string literal.
25392539
</para>
25402540

25412541
<!--

‎src/pl/plpgsql/src/gram.y‎

Lines changed: 30 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44
* procedural language
55
*
66
* IDENTIFICATION
7-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.75 2005/06/10 16:23:11 neilc Exp $
7+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.76 2005/06/14 06:43:14 neilc Exp $
88
*
99
* This software is copyrighted by Jan Wieck - Hamburg.
1010
*
@@ -135,8 +135,8 @@ staticvoid plpgsql_sql_error_callback(void *arg);
135135
%type<exception>proc_exception
136136
%type<condition>proc_conditions
137137

138-
%type<list>raise_params
139-
%type<ival>raise_levelraise_param
138+
139+
%type<ival>raise_level
140140
%type<str>raise_msg
141141

142142
%type<list>getdiag_list
@@ -1157,31 +1157,44 @@ stmt_return_next: K_RETURN_NEXT lno
11571157
}
11581158
;
11591159

1160-
stmt_raise:K_RAISElnoraise_levelraise_msgraise_params';'
1160+
stmt_raise:K_RAISElnoraise_levelraise_msg
11611161
{
11621162
PLpgSQL_stmt_raise*new;
1163+
inttok;
11631164

11641165
new = palloc(sizeof(PLpgSQL_stmt_raise));
11651166

11661167
new->cmd_type= PLPGSQL_STMT_RAISE;
11671168
new->lineno=$2;
11681169
new->elog_level =$3;
11691170
new->message=$4;
1170-
new->params=$5;
1171+
new->params=NIL;
11711172

1172-
$$ = (PLpgSQL_stmt *)new;
1173-
}
1174-
|K_RAISElnoraise_levelraise_msg';'
1175-
{
1176-
PLpgSQL_stmt_raise*new;
1173+
tok =yylex();
11771174

1178-
new = palloc(sizeof(PLpgSQL_stmt_raise));
1175+
/*
1176+
* We expect either a semi-colon, which
1177+
* indicates no parameters, or a comma that
1178+
* begins the list of parameter expressions
1179+
*/
1180+
if (tok !=',' && tok !=';')
1181+
yyerror("syntax error");
11791182

1180-
new->cmd_type= PLPGSQL_STMT_RAISE;
1181-
new->lineno=$2;
1182-
new->elog_level =$3;
1183-
new->message=$4;
1184-
new->params= NIL;
1183+
if (tok ==',')
1184+
{
1185+
PLpgSQL_expr *expr;
1186+
int term;
1187+
1188+
for (;;)
1189+
{
1190+
expr = read_sql_construct(',',';',", or ;",
1191+
"SELECT",
1192+
true,true, &term);
1193+
new->params = lappend(new->params, expr);
1194+
if (term ==';')
1195+
break;
1196+
}
1197+
}
11851198

11861199
$$ = (PLpgSQL_stmt *)new;
11871200
}
@@ -1219,22 +1232,6 @@ raise_level: K_EXCEPTION
12191232
}
12201233
;
12211234

1222-
raise_params:raise_paramsraise_param
1223-
{
1224-
$$ = lappend_int($1,$2);
1225-
}
1226-
|raise_param
1227-
{
1228-
$$ = list_make1_int($1);
1229-
}
1230-
;
1231-
1232-
raise_param:','T_SCALAR
1233-
{
1234-
$$ = yylval.scalar->dno;
1235-
}
1236-
;
1237-
12381235
loop_body:proc_sectK_ENDK_LOOP';'
12391236
{$$ =$1; }
12401237
;
@@ -1658,7 +1655,7 @@ read_sql_stmt(const char *sqlstart)
16581655
* expected:text to use in complaining that terminator was not found
16591656
* sqlstart:text to prefix to the accumulated SQL text
16601657
* isexpression: whether to say we're reading an "expression" or a "statement"
1661-
* valid_sql: whether to check the syntax of theexpression (plus sqlstart)
1658+
* valid_sql: whether to check the syntax of theexpr (prefixed with sqlstart)
16621659
* endtoken:if not NULL, ending token is stored at *endtoken
16631660
*(this is only interesting if until2 isn't zero)
16641661
*/

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

Lines changed: 9 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.143 2005/06/10 16:23:11 neilc Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_exec.c,v 1.144 2005/06/14 06:43:14 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -594,7 +594,7 @@ plpgsql_exec_trigger(PLpgSQL_function *func,
594594
error_context_stack=plerrcontext.previous;
595595

596596
/*
597-
* Return thetriggers result
597+
* Return thetrigger's result
598598
*/
599599
returnrettup;
600600
}
@@ -1095,22 +1095,9 @@ static int
10951095
exec_stmt_perform(PLpgSQL_execstate*estate,PLpgSQL_stmt_perform*stmt)
10961096
{
10971097
PLpgSQL_expr*expr=stmt->expr;
1098-
intrc;
1099-
1100-
/*
1101-
* If not already done create a plan for this expression
1102-
*/
1103-
if (expr->plan==NULL)
1104-
exec_prepare_plan(estate,expr);
1105-
1106-
rc=exec_run_select(estate,expr,0,NULL);
1107-
if (rc!=SPI_OK_SELECT)
1108-
ereport(ERROR,
1109-
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
1110-
errmsg("query \"%s\" did not return data",expr->query)));
11111098

1099+
(void)exec_run_select(estate,expr,0,NULL);
11121100
exec_set_found(estate, (estate->eval_processed!=0));
1113-
11141101
exec_eval_cleanup(estate);
11151102

11161103
returnPLPGSQL_RC_OK;
@@ -1941,15 +1928,18 @@ exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt)
19411928
(errcode(ERRCODE_SYNTAX_ERROR),
19421929
errmsg("too few parameters specified for RAISE")));
19431930

1944-
exec_eval_datum(estate,estate->datums[lfirst_int(current_param)],
1945-
InvalidOid,
1946-
&paramtypeid,&paramvalue,&paramisnull);
1931+
paramvalue=exec_eval_expr(estate,
1932+
(PLpgSQL_expr*)lfirst(current_param),
1933+
&paramisnull,
1934+
&paramtypeid);
1935+
19471936
if (paramisnull)
19481937
extval="<NULL>";
19491938
else
19501939
extval=convert_value_to_string(paramvalue,paramtypeid);
19511940
plpgsql_dstring_append(&ds,extval);
19521941
current_param=lnext(current_param);
1942+
exec_eval_cleanup(estate);
19531943
continue;
19541944
}
19551945

‎src/pl/plpgsql/src/pl_funcs.c‎

Lines changed: 15 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.42 2005/06/1400:10:02 neilc Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/pl_funcs.c,v 1.43 2005/06/1406:43:14 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -885,13 +885,20 @@ dump_return_next(PLpgSQL_stmt_return_next *stmt)
885885
staticvoid
886886
dump_raise(PLpgSQL_stmt_raise*stmt)
887887
{
888-
ListCell*l;
888+
ListCell*lc;
889+
inti=0;
889890

890891
dump_ind();
891-
printf("RAISE '%s'",stmt->message);
892-
foreach (l,stmt->params)
893-
printf(" %d",lfirst_int(l));
894-
printf("\n");
892+
printf("RAISE '%s'\n",stmt->message);
893+
dump_indent+=2;
894+
foreach (lc,stmt->params)
895+
{
896+
dump_ind();
897+
printf(" parameter %d: ",i++);
898+
dump_expr((PLpgSQL_expr*)lfirst(lc));
899+
printf("\n");
900+
}
901+
dump_indent-=2;
895902
}
896903

897904
staticvoid
@@ -916,7 +923,8 @@ dump_dynexecute(PLpgSQL_stmt_dynexecute *stmt)
916923
{
917924
dump_ind();
918925
printf(" target = %d %s\n",stmt->rec->recno,stmt->rec->refname);
919-
}elseif (stmt->row!=NULL)
926+
}
927+
elseif (stmt->row!=NULL)
920928
{
921929
dump_ind();
922930
printf(" target = %d %s\n",stmt->row->rowno,stmt->row->refname);

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

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
* procedural language
44
*
55
* IDENTIFICATION
6-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.62 2005/06/10 16:23:11 neilc Exp $
6+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/plpgsql.h,v 1.63 2005/06/14 06:43:14 neilc Exp $
77
*
88
* This software is copyrighted by Jan Wieck - Hamburg.
99
*
@@ -515,7 +515,7 @@ typedef struct
515515
intlineno;
516516
intelog_level;
517517
char*message;
518-
List*params;
518+
List*params;/* list of expressions */
519519
}PLpgSQL_stmt_raise;
520520

521521

‎src/test/regress/expected/plpgsql.out‎

Lines changed: 38 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2418,28 +2418,30 @@ drop type eitype cascade;
24182418
--
24192419
-- SQLSTATE and SQLERRM test
24202420
--
2421-
-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2422-
-- blocks
2423-
create function excpt_test() returns void as $$
2421+
create function excpt_test1() returns void as $$
24242422
begin
24252423
raise notice '% %', sqlstate, sqlerrm;
24262424
end; $$ language plpgsql;
2427-
ERROR: syntax error at or near "sqlstate" at character 79
2428-
LINE 3: raise notice '% %', sqlstate, sqlerrm;
2429-
^
2430-
-- should fail
2431-
create function excpt_test() returns void as $$
2425+
-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2426+
-- blocks
2427+
select excpt_test1();
2428+
ERROR: column "sqlstate" does not exist
2429+
CONTEXT: SQL statement "SELECT sqlstate"
2430+
PL/pgSQL function "excpt_test1" line 2 at raise
2431+
create function excpt_test2() returns void as $$
24322432
begin
24332433
begin
24342434
begin
24352435
raise notice '% %', sqlstate, sqlerrm;
24362436
end;
24372437
end;
24382438
end; $$ language plpgsql;
2439-
ERROR: syntax error at or near "sqlstate" at character 108
2440-
LINE 5: raise notice '% %', sqlstate, sqlerrm;
2441-
^
2442-
create function excpt_test() returns void as $$
2439+
-- should fail
2440+
select excpt_test2();
2441+
ERROR: column "sqlstate" does not exist
2442+
CONTEXT: SQL statement "SELECT sqlstate"
2443+
PL/pgSQL function "excpt_test2" line 4 at raise
2444+
create function excpt_test3() returns void as $$
24432445
begin
24442446
begin
24452447
raise exception 'user exception';
@@ -2458,14 +2460,34 @@ begin
24582460
raise notice '% %', sqlstate, sqlerrm;
24592461
end;
24602462
end; $$ language plpgsql;
2461-
selectexcpt_test();
2463+
selectexcpt_test3();
24622464
NOTICE: caught exception P0001 user exception
24632465
NOTICE: P0001 user exception
24642466
NOTICE: caught exception 22012 division by zero
24652467
NOTICE: P0001 user exception
2466-
excpt_test
2467-
------------
2468+
excpt_test3
2469+
-------------
2470+
2471+
(1 row)
2472+
2473+
drop function excpt_test1();
2474+
drop function excpt_test2();
2475+
drop function excpt_test3();
2476+
-- parameters of raise stmt can be expressions
2477+
create function raise_exprs() returns void as $$
2478+
declare
2479+
a integer[] = '{10,20,30}';
2480+
c varchar = 'xyz';
2481+
i integer;
2482+
begin
2483+
i := 2;
2484+
raise notice '%; %; %; %; %; %', a, a[i], c, (select c || 'abc'), row(10,'aaa',NULL,30), NULL;
2485+
end;$$ language plpgsql;
2486+
select raise_exprs();
2487+
NOTICE: {10,20,30}; 20; xyz; xyzabc; (10,aaa,,30); <NULL>
2488+
raise_exprs
2489+
-------------
24682490

24692491
(1 row)
24702492

2471-
drop functionexcpt_test();
2493+
drop functionraise_exprs();

‎src/test/regress/sql/plpgsql.sql‎

Lines changed: 26 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2055,24 +2055,26 @@ drop type eitype cascade;
20552055
-- SQLSTATE and SQLERRM test
20562056
--
20572057

2058-
-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2059-
-- blocks
2060-
createfunctionexcpt_test() returns voidas $$
2058+
createfunctionexcpt_test1() returns voidas $$
20612059
begin
20622060
raise notice'% %', sqlstate, sqlerrm;
20632061
end; $$ language plpgsql;
2062+
-- should fail: SQLSTATE and SQLERRM are only in defined EXCEPTION
2063+
-- blocks
2064+
select excpt_test1();
20642065

2065-
-- should fail
2066-
createfunctionexcpt_test() returns voidas $$
2066+
createfunctionexcpt_test2() returns voidas $$
20672067
begin
20682068
begin
20692069
begin
20702070
raise notice'% %', sqlstate, sqlerrm;
20712071
end;
20722072
end;
20732073
end; $$ language plpgsql;
2074+
-- should fail
2075+
select excpt_test2();
20742076

2075-
createfunctionexcpt_test() returns voidas $$
2077+
createfunctionexcpt_test3() returns voidas $$
20762078
begin
20772079
begin
20782080
raise exception'user exception';
@@ -2092,5 +2094,21 @@ begin
20922094
end;
20932095
end; $$ language plpgsql;
20942096

2095-
select excpt_test();
2096-
dropfunction excpt_test();
2097+
select excpt_test3();
2098+
dropfunction excpt_test1();
2099+
dropfunction excpt_test2();
2100+
dropfunction excpt_test3();
2101+
2102+
-- parameters of raise stmt can be expressions
2103+
createfunctionraise_exprs() returns voidas $$
2104+
declare
2105+
ainteger[]='{10,20,30}';
2106+
cvarchar='xyz';
2107+
iinteger;
2108+
begin
2109+
i :=2;
2110+
raise notice'%; %; %; %; %; %', a, a[i], c, (select c||'abc'), row(10,'aaa',NULL,30),NULL;
2111+
end;$$ language plpgsql;
2112+
2113+
select raise_exprs();
2114+
dropfunction raise_exprs();

0 commit comments

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