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

Commit00f9413

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 parentc030e26 commit00f9413

File tree

5 files changed

+122
-11
lines changed

5 files changed

+122
-11
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/meson.build

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -76,12 +76,13 @@ tests += {
7676
'regress': {
7777
'sql': [
7878
'plpgsql_array',
79+
'plpgsql_cache',
7980
'plpgsql_call',
8081
'plpgsql_control',
8182
'plpgsql_copy',
8283
'plpgsql_domain',
84+
'plpgsql_misc',
8385
'plpgsql_record',
84-
'plpgsql_cache',
8586
'plpgsql_simple',
8687
'plpgsql_transaction',
8788
'plpgsql_trap',

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

Lines changed: 64 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -76,7 +76,8 @@ staticPLpgSQL_expr*read_sql_expression2(int until, int until2,
7676
int *endtoken);
7777
staticPLpgSQL_expr*read_sql_stmt(void);
7878
staticPLpgSQL_type*read_datatype(int tok);
79-
staticPLpgSQL_stmt*make_execsql_stmt(int firsttoken,int location);
79+
staticPLpgSQL_stmt*make_execsql_stmt(int firsttoken,int location,
80+
PLword *word);
8081
staticPLpgSQL_stmt_fetch *read_fetch_direction(void);
8182
staticvoidcomplete_direction(PLpgSQL_stmt_fetch *fetch,
8283
bool *check_FROM);
@@ -1971,15 +1972,15 @@ loop_body: proc_sect K_END K_LOOP opt_label ';'
19711972
*/
19721973
stmt_execsql: K_IMPORT
19731974
{
1974-
$$ =make_execsql_stmt(K_IMPORT, @1);
1975+
$$ =make_execsql_stmt(K_IMPORT, @1,NULL);
19751976
}
19761977
| K_INSERT
19771978
{
1978-
$$ =make_execsql_stmt(K_INSERT, @1);
1979+
$$ =make_execsql_stmt(K_INSERT, @1,NULL);
19791980
}
19801981
| K_MERGE
19811982
{
1982-
$$ =make_execsql_stmt(K_MERGE, @1);
1983+
$$ =make_execsql_stmt(K_MERGE, @1,NULL);
19831984
}
19841985
| T_WORD
19851986
{
@@ -1990,7 +1991,7 @@ stmt_execsql: K_IMPORT
19901991
if (tok =='=' || tok == COLON_EQUALS ||
19911992
tok =='[' || tok =='.')
19921993
word_is_not_variable(&($1), @1);
1993-
$$ =make_execsql_stmt(T_WORD, @1);
1994+
$$ =make_execsql_stmt(T_WORD, @1, &($1));
19941995
}
19951996
| T_CWORD
19961997
{
@@ -2001,7 +2002,7 @@ stmt_execsql: K_IMPORT
20012002
if (tok =='=' || tok == COLON_EQUALS ||
20022003
tok =='[' || tok =='.')
20032004
cword_is_not_variable(&($1), @1);
2004-
$$ =make_execsql_stmt(T_CWORD, @1);
2005+
$$ =make_execsql_stmt(T_CWORD, @1,NULL);
20052006
}
20062007
;
20072008

@@ -2919,8 +2920,13 @@ read_datatype(int tok)
29192920
return result;
29202921
}
29212922

2923+
/*
2924+
* Read a generic SQL statement. We have already read its first token;
2925+
* firsttoken is that token's code and location its starting location.
2926+
* If firsttoken == T_WORD, pass its yylval value as "word", else pass NULL.
2927+
*/
29222928
static PLpgSQL_stmt *
2923-
make_execsql_stmt(int firsttoken, int location)
2929+
make_execsql_stmt(int firsttoken, int location, PLword *word)
29242930
{
29252931
StringInfoData ds;
29262932
IdentifierLookup save_IdentifierLookup;
@@ -2933,9 +2939,16 @@ make_execsql_stmt(int firsttoken, int location)
29332939
boolhave_strict = false;
29342940
intinto_start_loc = -1;
29352941
intinto_end_loc = -1;
2942+
intparen_depth = 0;
2943+
intbegin_depth = 0;
2944+
boolin_routine_definition = false;
2945+
inttoken_count = 0;
2946+
chartokens[4];/* records the first few tokens*/
29362947

29372948
initStringInfo(&ds);
29382949

2950+
memset(tokens, 0, sizeof(tokens));
2951+
29392952
/* special lookup mode for identifiers within the SQL text*/
29402953
save_IdentifierLookup = plpgsql_IdentifierLookup;
29412954
plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_EXPR;
@@ -2944,6 +2957,12 @@ make_execsql_stmt(int firsttoken, int location)
29442957
* Scan to the end of the SQL command. Identify any INTO-variables
29452958
* clause lurking within it, and parse that via read_into_target().
29462959
*
2960+
* The end of the statement is defined by a semicolon ... except that
2961+
* semicolons within parentheses or BEGIN/END blocks don't terminate a
2962+
* statement. We follow psql's lead in not recognizing BEGIN/END except
2963+
* after CREATE [OR REPLACE] {FUNCTION|PROCEDURE}. END can also appear
2964+
* within a CASE construct, so we treat CASE/END like BEGIN/END.
2965+
*
29472966
* Because INTO is sometimes used in the main SQL grammar, we have to be
29482967
* careful not to take any such usage of INTO as a PL/pgSQL INTO clause.
29492968
* There are currently three such cases:
@@ -2969,13 +2988,50 @@ make_execsql_stmt(int firsttoken, int location)
29692988
* break this logic again ... beware!
29702989
*/
29712990
tok = firsttoken;
2991+
if (tok == T_WORD && strcmp(word->ident, "create") == 0)
2992+
tokens[token_count] = 'c';
2993+
token_count++;
2994+
29722995
for (;;)
29732996
{
29742997
prev_tok = tok;
29752998
tok = yylex();
29762999
if (have_into && into_end_loc < 0)
29773000
into_end_loc = yylloc;/* token after the INTO part*/
2978-
if (tok == ';')
3001+
/* Detect CREATE [OR REPLACE] {FUNCTION|PROCEDURE}*/
3002+
if (tokens[0] == 'c' && token_count < sizeof(tokens))
3003+
{
3004+
if (tok == K_OR)
3005+
tokens[token_count] = 'o';
3006+
else if (tok == T_WORD &&
3007+
strcmp(yylval.word.ident, "replace") == 0)
3008+
tokens[token_count] = 'r';
3009+
else if (tok == T_WORD &&
3010+
strcmp(yylval.word.ident, "function") == 0)
3011+
tokens[token_count] = 'f';
3012+
else if (tok == T_WORD &&
3013+
strcmp(yylval.word.ident, "procedure") == 0)
3014+
tokens[token_count] = 'f';/* treat same as "function"*/
3015+
if (tokens[1] == 'f' ||
3016+
(tokens[1] == 'o' && tokens[2] == 'r' && tokens[3] == 'f'))
3017+
in_routine_definition = true;
3018+
token_count++;
3019+
}
3020+
/* Track paren nesting (needed for CREATE RULE syntax)*/
3021+
if (tok == '(')
3022+
paren_depth++;
3023+
else if (tok == ')' && paren_depth > 0)
3024+
paren_depth--;
3025+
/* We need track BEGIN/END nesting only in a routine definition*/
3026+
if (in_routine_definition && paren_depth == 0)
3027+
{
3028+
if (tok == K_BEGIN || tok == K_CASE)
3029+
begin_depth++;
3030+
else if (tok == K_END && begin_depth > 0)
3031+
begin_depth--;
3032+
}
3033+
/* Command-ending semicolon?*/
3034+
if (tok == ';' && paren_depth == 0 && begin_depth == 0)
29793035
break;
29803036
if (tok == 0)
29813037
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