Thread: BUG #4629: PL/pgSQL issue
The following bug has been logged online: Bug reference: 4629 Logged by: Martin Blazek Email address: mblazek@8bc.com PostgreSQL version: 8.3.5 Operating system: Windows XP Description: PL/pgSQL issue Details: I try to create the following rule. It doesn't make much sense, but the syntax is ok and if the table "test" exists, it is created. CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" VALUES (1); The next step is creating a function that contains only the following command: CREATE FUNCTION test() RETURNS integer AS $$ BEGIN CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" VALUES (1); END;$$ LANGUAGE plpgsql; Wow! Here's the result (already on function create, not during runtime): ERROR: syntax error at ""test"" DETAIL: Expected record variable, row variable, or list of scalar variables following INTO. KONTEXT: compile of PL/pgSQL function "test" near line 2 It appears that only insert rules have this issue - update and delete work as expected.
"Martin Blazek" <mblazek@8bc.com> writes:
> CREATE FUNCTION test() RETURNS integer AS $$
> BEGIN
> CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test"
> VALUES (1);
> END;$$ LANGUAGE plpgsql;
Hm, I guess nobody ever tried to do that in plpgsql before. It's
taking the INTO as starting a clause that returns values into plpgsql
variables :-(.
There's a special case in there to prevent INTO just after INSERT
from being taken that way, but it only works when the INSERT is at
the start of the statement :-(. Guess we need to change that.
In the meantime, you can probably work around this by using EXECUTE,
ie
EXECUTE 'CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" VALUES (1)';
regards, tom lane
Hello 2009/2/2 Tom Lane <tgl@sss.pgh.pa.us>: > "Martin Blazek" <mblazek@8bc.com> writes: >> CREATE FUNCTION test() RETURNS integer AS $$ >> BEGIN >> CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" >> VALUES (1); >> END;$$ LANGUAGE plpgsql; > > Hm, I guess nobody ever tried to do that in plpgsql before. It's > taking the INTO as starting a clause that returns values into plpgsql > variables :-(. > > There's a special case in there to prevent INTO just after INSERT > from being taken that way, but it only works when the INSERT is at > the start of the statement :-(. Guess we need to change that. > > In the meantime, you can probably work around this by using EXECUTE, > ie > We should ignore INTO keyword when statement starts with CREATE keyword. This patch have to simple. I'll prepare it. Regards Pavel Stehule > EXECUTE 'CREATE RULE "rule" AS ON INSERT TO "test" DO INSTEAD INSERT INTO "test" VALUES (1)'; > > regards, tom lane > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> We should ignore INTO keyword when statement starts with CREATE
> keyword. This patch have to simple. I'll prepare it.
I'm already on it...
regards, tom lane
Index: gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
retrieving revision 1.119
diff -c -r1.119 gram.y
*** gram.y 7 Jan 2009 13:44:37 -0000 1.119
--- gram.y 2 Feb 2009 19:57:59 -0000
***************
*** 149,155 ****
%type <loop_body> loop_body
%type <stmt> proc_stmt pl_block
%type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
! %type <stmt> stmt_return stmt_raise stmt_execsql stmt_execsql_insert
%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
%type <stmt> stmt_case
--- 149,155 ----
%type <loop_body> loop_body
%type <stmt> proc_stmt pl_block
%type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
! %type <stmt> stmt_return stmt_raise stmt_execsql
%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
%type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
%type <stmt> stmt_case
***************
*** 646,653 ****
{ $$ = $1; }
| stmt_execsql
{ $$ = $1; }
- | stmt_execsql_insert
- { $$ = $1; }
| stmt_dynexecute
{ $$ = $1; }
| stmt_perform
--- 646,651 ----
***************
*** 1482,1508 ****
}
;
! /* this matches any otherwise-unrecognized starting keyword */
! execsql_start : T_WORD
{ $$ = pstrdup(yytext); }
| T_ERROR
{ $$ = pstrdup(yytext); }
;
- stmt_execsql_insert : K_INSERT lno K_INTO
- {
- /*
- * We have to special-case INSERT so that its INTO
- * won't be treated as an INTO-variables clause.
- *
- * Fortunately, this is the only valid use of INTO
- * in a pl/pgsql SQL command, and INTO is already
- * a fully reserved word in the main grammar.
- */
- $$ = make_execsql_stmt("INSERT INTO", $2);
- }
- ;
-
stmt_dynexecute : K_EXECUTE lno
{
PLpgSQL_stmt_dynexecute *new;
--- 1480,1494 ----
}
;
! /* T_WORD+T_ERROR match any otherwise-unrecognized starting keyword */
! execsql_start : K_INSERT
! { $$ = pstrdup(yytext); }
! | T_WORD
{ $$ = pstrdup(yytext); }
| T_ERROR
{ $$ = pstrdup(yytext); }
;
stmt_dynexecute : K_EXECUTE lno
{
PLpgSQL_stmt_dynexecute *new;
***************
*** 2156,2175 ****
PLpgSQL_row *row = NULL;
PLpgSQL_rec *rec = NULL;
int tok;
bool have_into = false;
bool have_strict = false;
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, sqlstart);
for (;;)
{
tok = yylex();
if (tok == ';')
break;
if (tok == 0)
yyerror("unexpected end of function definition");
! if (tok == K_INTO)
{
if (have_into)
yyerror("INTO specified more than once");
--- 2142,2177 ----
PLpgSQL_row *row = NULL;
PLpgSQL_rec *rec = NULL;
int tok;
+ int prev_tok;
bool have_into = false;
bool have_strict = false;
plpgsql_dstring_init(&ds);
plpgsql_dstring_append(&ds, sqlstart);
+ /*
+ * We have to special-case the sequence INSERT INTO, because we don't want
+ * that to be taken as an INTO-variables clause. Fortunately, this is the
+ * only valid use of INTO in a pl/pgsql SQL command, and INTO is already a
+ * fully reserved word in the main grammar. We have to treat it that way
+ * anywhere in the string, not only at the start; consider CREATE RULE
+ * containing an INSERT statement.
+ */
+ if (pg_strcasecmp(sqlstart, "insert") == 0)
+ tok = K_INSERT;
+ else
+ tok = 0;
+
for (;;)
{
+ prev_tok = tok;
tok = yylex();
if (tok == ';')
break;
if (tok == 0)
yyerror("unexpected end of function definition");
!
! if (tok == K_INTO && prev_tok != K_INSERT)
{
if (have_into)
yyerror("INTO specified more than once");
2009/2/2 Tom Lane <tgl@sss.pgh.pa.us>:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> We should ignore INTO keyword when statement starts with CREATE
>> keyword. This patch have to simple. I'll prepare it.
>
> I'm already on it...
>
> regards, tom lane
ok
Regards
Pavel Stehule
>
> Index: gram.y
> ===================================================================
> RCS file: /cvsroot/pgsql/src/pl/plpgsql/src/gram.y,v
> retrieving revision 1.119
> diff -c -r1.119 gram.y
> *** gram.y 7 Jan 2009 13:44:37 -0000 1.119
> --- gram.y 2 Feb 2009 19:57:59 -0000
> ***************
> *** 149,155 ****
> %type <loop_body> loop_body
> %type <stmt> proc_stmt pl_block
> %type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
> ! %type <stmt> stmt_return stmt_raise stmt_execsql stmt_execsql_insert
> %type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
> %type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
> %type <stmt> stmt_case
> --- 149,155 ----
> %type <loop_body> loop_body
> %type <stmt> proc_stmt pl_block
> %type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
> ! %type <stmt> stmt_return stmt_raise stmt_execsql
> %type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
> %type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null
> %type <stmt> stmt_case
> ***************
> *** 646,653 ****
> { $$ = $1; }
> | stmt_execsql
> { $$ = $1; }
> - | stmt_execsql_insert
> - { $$ = $1; }
> | stmt_dynexecute
> { $$ = $1; }
> | stmt_perform
> --- 646,651 ----
> ***************
> *** 1482,1508 ****
> }
> ;
>
> ! /* this matches any otherwise-unrecognized starting keyword */
> ! execsql_start : T_WORD
> { $$ = pstrdup(yytext); }
> | T_ERROR
> { $$ = pstrdup(yytext); }
> ;
>
> - stmt_execsql_insert : K_INSERT lno K_INTO
> - {
> - /*
> - * We have to special-case INSERT so that its INTO
> - * won't be treated as an INTO-variables clause.
> - *
> - * Fortunately, this is the only valid use of INTO
> - * in a pl/pgsql SQL command, and INTO is already
> - * a fully reserved word in the main grammar.
> - */
> - $$ = make_execsql_stmt("INSERT INTO", $2);
> - }
> - ;
> -
> stmt_dynexecute : K_EXECUTE lno
> {
> PLpgSQL_stmt_dynexecute *new;
> --- 1480,1494 ----
> }
> ;
>
> ! /* T_WORD+T_ERROR match any otherwise-unrecognized starting keyword */
> ! execsql_start : K_INSERT
> ! { $$ = pstrdup(yytext); }
> ! | T_WORD
> { $$ = pstrdup(yytext); }
> | T_ERROR
> { $$ = pstrdup(yytext); }
> ;
>
> stmt_dynexecute : K_EXECUTE lno
> {
> PLpgSQL_stmt_dynexecute *new;
> ***************
> *** 2156,2175 ****
> PLpgSQL_row *row = NULL;
> PLpgSQL_rec *rec = NULL;
> int tok;
> bool have_into = false;
> bool have_strict = false;
>
> plpgsql_dstring_init(&ds);
> plpgsql_dstring_append(&ds, sqlstart);
>
> for (;;)
> {
> tok = yylex();
> if (tok == ';')
> break;
> if (tok == 0)
> yyerror("unexpected end of function definition");
> ! if (tok == K_INTO)
> {
> if (have_into)
> yyerror("INTO specified more than once");
> --- 2142,2177 ----
> PLpgSQL_row *row = NULL;
> PLpgSQL_rec *rec = NULL;
> int tok;
> + int prev_tok;
> bool have_into = false;
> bool have_strict = false;
>
> plpgsql_dstring_init(&ds);
> plpgsql_dstring_append(&ds, sqlstart);
>
> + /*
> + * We have to special-case the sequence INSERT INTO, because we don't want
> + * that to be taken as an INTO-variables clause. Fortunately, this is the
> + * only valid use of INTO in a pl/pgsql SQL command, and INTO is already a
> + * fully reserved word in the main grammar. We have to treat it that way
> + * anywhere in the string, not only at the start; consider CREATE RULE
> + * containing an INSERT statement.
> + */
> + if (pg_strcasecmp(sqlstart, "insert") == 0)
> + tok = K_INSERT;
> + else
> + tok = 0;
> +
> for (;;)
> {
> + prev_tok = tok;
> tok = yylex();
> if (tok == ';')
> break;
> if (tok == 0)
> yyerror("unexpected end of function definition");
> !
> ! if (tok == K_INTO && prev_tok != K_INSERT)
> {
> if (have_into)
> yyerror("INTO specified more than once");
>