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

Commit57b440e

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 parente313a61 commit57b440e

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);
@@ -1972,15 +1973,15 @@ loop_body: proc_sect K_END K_LOOP opt_label ';'
19721973
*/
19731974
stmt_execsql: K_IMPORT
19741975
{
1975-
$$ =make_execsql_stmt(K_IMPORT, @1);
1976+
$$ =make_execsql_stmt(K_IMPORT, @1,NULL);
19761977
}
19771978
| K_INSERT
19781979
{
1979-
$$ =make_execsql_stmt(K_INSERT, @1);
1980+
$$ =make_execsql_stmt(K_INSERT, @1,NULL);
19801981
}
19811982
| K_MERGE
19821983
{
1983-
$$ =make_execsql_stmt(K_MERGE, @1);
1984+
$$ =make_execsql_stmt(K_MERGE, @1,NULL);
19841985
}
19851986
| T_WORD
19861987
{
@@ -1991,7 +1992,7 @@ stmt_execsql: K_IMPORT
19911992
if (tok =='=' || tok == COLON_EQUALS ||
19921993
tok =='[' || tok =='.')
19931994
word_is_not_variable(&($1), @1);
1994-
$$ =make_execsql_stmt(T_WORD, @1);
1995+
$$ =make_execsql_stmt(T_WORD, @1, &($1));
19951996
}
19961997
| T_CWORD
19971998
{
@@ -2002,7 +2003,7 @@ stmt_execsql: K_IMPORT
20022003
if (tok =='=' || tok == COLON_EQUALS ||
20032004
tok =='[' || tok =='.')
20042005
cword_is_not_variable(&($1), @1);
2005-
$$ =make_execsql_stmt(T_CWORD, @1);
2006+
$$ =make_execsql_stmt(T_CWORD, @1,NULL);
20062007
}
20072008
;
20082009

@@ -2947,8 +2948,13 @@ read_datatype(int tok)
29472948
return result;
29482949
}
29492950

2951+
/*
2952+
* Read a generic SQL statement. We have already read its first token;
2953+
* firsttoken is that token's code and location its starting location.
2954+
* If firsttoken == T_WORD, pass its yylval value as "word", else pass NULL.
2955+
*/
29502956
static PLpgSQL_stmt *
2951-
make_execsql_stmt(int firsttoken, int location)
2957+
make_execsql_stmt(int firsttoken, int location, PLword *word)
29522958
{
29532959
StringInfoData ds;
29542960
IdentifierLookup save_IdentifierLookup;
@@ -2961,9 +2967,16 @@ make_execsql_stmt(int firsttoken, int location)
29612967
boolhave_strict = false;
29622968
intinto_start_loc = -1;
29632969
intinto_end_loc = -1;
2970+
intparen_depth = 0;
2971+
intbegin_depth = 0;
2972+
boolin_routine_definition = false;
2973+
inttoken_count = 0;
2974+
chartokens[4];/* records the first few tokens*/
29642975

29652976
initStringInfo(&ds);
29662977

2978+
memset(tokens, 0, sizeof(tokens));
2979+
29672980
/* special lookup mode for identifiers within the SQL text*/
29682981
save_IdentifierLookup = plpgsql_IdentifierLookup;
29692982
plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_EXPR;
@@ -2972,6 +2985,12 @@ make_execsql_stmt(int firsttoken, int location)
29722985
* Scan to the end of the SQL command. Identify any INTO-variables
29732986
* clause lurking within it, and parse that via read_into_target().
29742987
*
2988+
* The end of the statement is defined by a semicolon ... except that
2989+
* semicolons within parentheses or BEGIN/END blocks don't terminate a
2990+
* statement. We follow psql's lead in not recognizing BEGIN/END except
2991+
* after CREATE [OR REPLACE] {FUNCTION|PROCEDURE}. END can also appear
2992+
* within a CASE construct, so we treat CASE/END like BEGIN/END.
2993+
*
29752994
* Because INTO is sometimes used in the main SQL grammar, we have to be
29762995
* careful not to take any such usage of INTO as a PL/pgSQL INTO clause.
29772996
* There are currently three such cases:
@@ -2997,13 +3016,50 @@ make_execsql_stmt(int firsttoken, int location)
29973016
* break this logic again ... beware!
29983017
*/
29993018
tok = firsttoken;
3019+
if (tok == T_WORD && strcmp(word->ident, "create") == 0)
3020+
tokens[token_count] = 'c';
3021+
token_count++;
3022+
30003023
for (;;)
30013024
{
30023025
prev_tok = tok;
30033026
tok = yylex();
30043027
if (have_into && into_end_loc < 0)
30053028
into_end_loc = yylloc;/* token after the INTO part*/
3006-
if (tok == ';')
3029+
/* Detect CREATE [OR REPLACE] {FUNCTION|PROCEDURE}*/
3030+
if (tokens[0] == 'c' && token_count < sizeof(tokens))
3031+
{
3032+
if (tok == K_OR)
3033+
tokens[token_count] = 'o';
3034+
else if (tok == T_WORD &&
3035+
strcmp(yylval.word.ident, "replace") == 0)
3036+
tokens[token_count] = 'r';
3037+
else if (tok == T_WORD &&
3038+
strcmp(yylval.word.ident, "function") == 0)
3039+
tokens[token_count] = 'f';
3040+
else if (tok == T_WORD &&
3041+
strcmp(yylval.word.ident, "procedure") == 0)
3042+
tokens[token_count] = 'f';/* treat same as "function"*/
3043+
if (tokens[1] == 'f' ||
3044+
(tokens[1] == 'o' && tokens[2] == 'r' && tokens[3] == 'f'))
3045+
in_routine_definition = true;
3046+
token_count++;
3047+
}
3048+
/* Track paren nesting (needed for CREATE RULE syntax)*/
3049+
if (tok == '(')
3050+
paren_depth++;
3051+
else if (tok == ')' && paren_depth > 0)
3052+
paren_depth--;
3053+
/* We need track BEGIN/END nesting only in a routine definition*/
3054+
if (in_routine_definition && paren_depth == 0)
3055+
{
3056+
if (tok == K_BEGIN || tok == K_CASE)
3057+
begin_depth++;
3058+
else if (tok == K_END && begin_depth > 0)
3059+
begin_depth--;
3060+
}
3061+
/* Command-ending semicolon?*/
3062+
if (tok == ';' && paren_depth == 0 && begin_depth == 0)
30073063
break;
30083064
if (tok == 0)
30093065
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