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

Commitb7e8f27

Browse files
committed
Fix plpgsql to allow new-style SQL CREATE FUNCTION as a SQL command.
plpgsql fails on new-style CREATE FUNCTION/PROCEDURE commands withina routine or DO block, because make_execsql_stmt believes that asemicolon token always terminates a SQL command. Now, that's actuallybeen wrong since the day it was written, because CREATE RULE has longallowed multiple rule actions separated by semicolons. But there arefew enough people using multi-action rules that there was never anattempt to fix it. New-style SQL functions, though, are popular.psql has this same problem of "does this semicolon really terminatethe command?". It deals with CREATE RULE by counting parenthesisnesting depth: a semicolon within parens doesn't end a command.Commitse717a9a and029c5ac created a similar heuristic to countmatching BEGIN/END pairs (but only within CREATEs, so as not to befooled by plain BEGIN). That's survived several releases now withouttrouble reports, so let's just absorb those heuristics into plpgsql.Per report from Samuel Dussault. Back-patch to v14 where new-styleSQL function syntax came in.Discussion:https://postgr.es/m/YT2PR01MB88552C3E9AD40A6C038774A781722@YT2PR01MB8855.CANPRD01.PROD.OUTLOOK.COM
1 parentb6f2e01 commitb7e8f27

File tree

4 files changed

+119
-9
lines changed

4 files changed

+119
-9
lines changed

‎src/pl/plpgsql/src/Makefile

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -32,8 +32,9 @@ DATA = plpgsql.control plpgsql--1.0.sql
3232

3333
REGRESS_OPTS = --dbname=$(PL_TESTDB)
3434

35-
REGRESS = plpgsql_array plpgsql_call plpgsql_control plpgsql_copy plpgsql_domain\
36-
plpgsql_record plpgsql_cache plpgsql_simple plpgsql_transaction\
35+
REGRESS = plpgsql_array plpgsql_cache plpgsql_call plpgsql_control\
36+
plpgsql_copy plpgsql_domain plpgsql_misc\
37+
plpgsql_record plpgsql_simple plpgsql_transaction\
3738
plpgsql_trap plpgsql_trigger plpgsql_varprops
3839

3940
# where to find gen_keywordlist.pl and subsidiary files
Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
--
2+
-- Miscellaneous topics
3+
--
4+
-- Verify that we can parse new-style CREATE FUNCTION/PROCEDURE
5+
do
6+
$$
7+
declare procedure int; -- check we still recognize non-keywords as vars
8+
begin
9+
create function test1() returns int
10+
begin atomic
11+
select 2 + 2;
12+
end;
13+
create or replace procedure test2(x int)
14+
begin atomic
15+
select x + 2;
16+
end;
17+
end
18+
$$;
19+
\sf test1
20+
CREATE OR REPLACE FUNCTION public.test1()
21+
RETURNS integer
22+
LANGUAGE sql
23+
BEGIN ATOMIC
24+
SELECT (2 + 2);
25+
END
26+
\sf test2
27+
CREATE OR REPLACE PROCEDURE public.test2(IN x integer)
28+
LANGUAGE sql
29+
BEGIN ATOMIC
30+
SELECT (x + 2);
31+
END

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

Lines changed: 63 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -79,7 +79,8 @@ staticPLpgSQL_expr*read_sql_expression2(int until, int until2,
7979
int *endtoken);
8080
staticPLpgSQL_expr*read_sql_stmt(void);
8181
staticPLpgSQL_type*read_datatype(int tok);
82-
staticPLpgSQL_stmt*make_execsql_stmt(int firsttoken,int location);
82+
staticPLpgSQL_stmt*make_execsql_stmt(int firsttoken,int location,
83+
PLword *word);
8384
staticPLpgSQL_stmt_fetch *read_fetch_direction(void);
8485
staticvoidcomplete_direction(PLpgSQL_stmt_fetch *fetch,
8586
bool *check_FROM);
@@ -1994,11 +1995,11 @@ loop_body: proc_sect K_END K_LOOP opt_label ';'
19941995
*/
19951996
stmt_execsql: K_IMPORT
19961997
{
1997-
$$ =make_execsql_stmt(K_IMPORT, @1);
1998+
$$ =make_execsql_stmt(K_IMPORT, @1,NULL);
19981999
}
19992000
| K_INSERT
20002001
{
2001-
$$ =make_execsql_stmt(K_INSERT, @1);
2002+
$$ =make_execsql_stmt(K_INSERT, @1,NULL);
20022003
}
20032004
| T_WORD
20042005
{
@@ -2009,7 +2010,7 @@ stmt_execsql: K_IMPORT
20092010
if (tok =='=' || tok == COLON_EQUALS ||
20102011
tok =='[' || tok =='.')
20112012
word_is_not_variable(&($1), @1);
2012-
$$ =make_execsql_stmt(T_WORD, @1);
2013+
$$ =make_execsql_stmt(T_WORD, @1, &($1));
20132014
}
20142015
| T_CWORD
20152016
{
@@ -2020,7 +2021,7 @@ stmt_execsql: K_IMPORT
20202021
if (tok =='=' || tok == COLON_EQUALS ||
20212022
tok =='[' || tok =='.')
20222023
cword_is_not_variable(&($1), @1);
2023-
$$ =make_execsql_stmt(T_CWORD, @1);
2024+
$$ =make_execsql_stmt(T_CWORD, @1,NULL);
20242025
}
20252026
;
20262027

@@ -2936,8 +2937,13 @@ read_datatype(int tok)
29362937
return result;
29372938
}
29382939

2940+
/*
2941+
* Read a generic SQL statement. We have already read its first token;
2942+
* firsttoken is that token's code and location its starting location.
2943+
* If firsttoken == T_WORD, pass its yylval value as "word", else pass NULL.
2944+
*/
29392945
static PLpgSQL_stmt *
2940-
make_execsql_stmt(int firsttoken, int location)
2946+
make_execsql_stmt(int firsttoken, int location, PLword *word)
29412947
{
29422948
StringInfoDatads;
29432949
IdentifierLookupsave_IdentifierLookup;
@@ -2950,9 +2956,16 @@ make_execsql_stmt(int firsttoken, int location)
29502956
boolhave_strict = false;
29512957
intinto_start_loc = -1;
29522958
intinto_end_loc = -1;
2959+
intparen_depth = 0;
2960+
intbegin_depth = 0;
2961+
boolin_routine_definition = false;
2962+
inttoken_count = 0;
2963+
chartokens[4];/* records the first few tokens*/
29532964

29542965
initStringInfo(&ds);
29552966

2967+
memset(tokens, 0, sizeof(tokens));
2968+
29562969
/* special lookup mode for identifiers within the SQL text*/
29572970
save_IdentifierLookup = plpgsql_IdentifierLookup;
29582971
plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_EXPR;
@@ -2961,6 +2974,12 @@ make_execsql_stmt(int firsttoken, int location)
29612974
* Scan to the end of the SQL command. Identify any INTO-variables
29622975
* clause lurking within it, and parse that via read_into_target().
29632976
*
2977+
* The end of the statement is defined by a semicolon ... except that
2978+
* semicolons within parentheses or BEGIN/END blocks don't terminate a
2979+
* statement. We follow psql's lead in not recognizing BEGIN/END except
2980+
* after CREATE [OR REPLACE] {FUNCTION|PROCEDURE}. END can also appear
2981+
* within a CASE construct, so we treat CASE/END like BEGIN/END.
2982+
*
29642983
* Because INTO is sometimes used in the main SQL grammar, we have to be
29652984
* careful not to take any such usage of INTO as a PL/pgSQL INTO clause.
29662985
* There are currently three such cases:
@@ -2986,13 +3005,50 @@ make_execsql_stmt(int firsttoken, int location)
29863005
* break this logic again ... beware!
29873006
*/
29883007
tok = firsttoken;
3008+
if (tok == T_WORD && strcmp(word->ident, "create") == 0)
3009+
tokens[token_count] = 'c';
3010+
token_count++;
3011+
29893012
for (;;)
29903013
{
29913014
prev_tok = tok;
29923015
tok = yylex();
29933016
if (have_into && into_end_loc < 0)
29943017
into_end_loc = yylloc;/* token after the INTO part*/
2995-
if (tok == ';')
3018+
/* Detect CREATE [OR REPLACE] {FUNCTION|PROCEDURE}*/
3019+
if (tokens[0] == 'c' && token_count < sizeof(tokens))
3020+
{
3021+
if (tok == K_OR)
3022+
tokens[token_count] = 'o';
3023+
else if (tok == T_WORD &&
3024+
strcmp(yylval.word.ident, "replace") == 0)
3025+
tokens[token_count] = 'r';
3026+
else if (tok == T_WORD &&
3027+
strcmp(yylval.word.ident, "function") == 0)
3028+
tokens[token_count] = 'f';
3029+
else if (tok == T_WORD &&
3030+
strcmp(yylval.word.ident, "procedure") == 0)
3031+
tokens[token_count] = 'f';/* treat same as "function"*/
3032+
if (tokens[1] == 'f' ||
3033+
(tokens[1] == 'o' && tokens[2] == 'r' && tokens[3] == 'f'))
3034+
in_routine_definition = true;
3035+
token_count++;
3036+
}
3037+
/* Track paren nesting (needed for CREATE RULE syntax)*/
3038+
if (tok == '(')
3039+
paren_depth++;
3040+
else if (tok == ')' && paren_depth > 0)
3041+
paren_depth--;
3042+
/* We need track BEGIN/END nesting only in a routine definition*/
3043+
if (in_routine_definition && paren_depth == 0)
3044+
{
3045+
if (tok == K_BEGIN || tok == K_CASE)
3046+
begin_depth++;
3047+
else if (tok == K_END && begin_depth > 0)
3048+
begin_depth--;
3049+
}
3050+
/* Command-ending semicolon?*/
3051+
if (tok == ';' && paren_depth == 0 && begin_depth == 0)
29963052
break;
29973053
if (tok == 0)
29983054
yyerror("unexpected end of function definition");
Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
--
2+
-- Miscellaneous topics
3+
--
4+
5+
-- Verify that we can parse new-style CREATE FUNCTION/PROCEDURE
6+
do
7+
$$
8+
declare procedureint;-- check we still recognize non-keywords as vars
9+
begin
10+
createfunctiontest1() returnsint
11+
begin atomic
12+
select2+2;
13+
end;
14+
createor replace procedure test2(xint)
15+
begin atomic
16+
select x+2;
17+
end;
18+
end
19+
$$;
20+
21+
\sf test1
22+
\sf test2

0 commit comments

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