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

Commitde2d393

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 parenta0c19de commitde2d393

File tree

4 files changed

+120
-10
lines changed

4 files changed

+120
-10
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: 64 additions & 8 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);
@@ -1996,15 +1997,15 @@ loop_body: proc_sect K_END K_LOOP opt_label ';'
19961997
*/
19971998
stmt_execsql: K_IMPORT
19981999
{
1999-
$$ =make_execsql_stmt(K_IMPORT, @1);
2000+
$$ =make_execsql_stmt(K_IMPORT, @1,NULL);
20002001
}
20012002
| K_INSERT
20022003
{
2003-
$$ =make_execsql_stmt(K_INSERT, @1);
2004+
$$ =make_execsql_stmt(K_INSERT, @1,NULL);
20042005
}
20052006
| K_MERGE
20062007
{
2007-
$$ =make_execsql_stmt(K_MERGE, @1);
2008+
$$ =make_execsql_stmt(K_MERGE, @1,NULL);
20082009
}
20092010
| T_WORD
20102011
{
@@ -2015,7 +2016,7 @@ stmt_execsql: K_IMPORT
20152016
if (tok =='=' || tok == COLON_EQUALS ||
20162017
tok =='[' || tok =='.')
20172018
word_is_not_variable(&($1), @1);
2018-
$$ =make_execsql_stmt(T_WORD, @1);
2019+
$$ =make_execsql_stmt(T_WORD, @1, &($1));
20192020
}
20202021
| T_CWORD
20212022
{
@@ -2026,7 +2027,7 @@ stmt_execsql: K_IMPORT
20262027
if (tok =='=' || tok == COLON_EQUALS ||
20272028
tok =='[' || tok =='.')
20282029
cword_is_not_variable(&($1), @1);
2029-
$$ =make_execsql_stmt(T_CWORD, @1);
2030+
$$ =make_execsql_stmt(T_CWORD, @1,NULL);
20302031
}
20312032
;
20322033

@@ -2943,8 +2944,13 @@ read_datatype(int tok)
29432944
return result;
29442945
}
29452946

2947+
/*
2948+
* Read a generic SQL statement. We have already read its first token;
2949+
* firsttoken is that token's code and location its starting location.
2950+
* If firsttoken == T_WORD, pass its yylval value as "word", else pass NULL.
2951+
*/
29462952
static PLpgSQL_stmt *
2947-
make_execsql_stmt(int firsttoken, int location)
2953+
make_execsql_stmt(int firsttoken, int location, PLword *word)
29482954
{
29492955
StringInfoData ds;
29502956
IdentifierLookup save_IdentifierLookup;
@@ -2957,9 +2963,16 @@ make_execsql_stmt(int firsttoken, int location)
29572963
boolhave_strict = false;
29582964
intinto_start_loc = -1;
29592965
intinto_end_loc = -1;
2966+
intparen_depth = 0;
2967+
intbegin_depth = 0;
2968+
boolin_routine_definition = false;
2969+
inttoken_count = 0;
2970+
chartokens[4];/* records the first few tokens*/
29602971

29612972
initStringInfo(&ds);
29622973

2974+
memset(tokens, 0, sizeof(tokens));
2975+
29632976
/* special lookup mode for identifiers within the SQL text*/
29642977
save_IdentifierLookup = plpgsql_IdentifierLookup;
29652978
plpgsql_IdentifierLookup = IDENTIFIER_LOOKUP_EXPR;
@@ -2968,6 +2981,12 @@ make_execsql_stmt(int firsttoken, int location)
29682981
* Scan to the end of the SQL command. Identify any INTO-variables
29692982
* clause lurking within it, and parse that via read_into_target().
29702983
*
2984+
* The end of the statement is defined by a semicolon ... except that
2985+
* semicolons within parentheses or BEGIN/END blocks don't terminate a
2986+
* statement. We follow psql's lead in not recognizing BEGIN/END except
2987+
* after CREATE [OR REPLACE] {FUNCTION|PROCEDURE}. END can also appear
2988+
* within a CASE construct, so we treat CASE/END like BEGIN/END.
2989+
*
29712990
* Because INTO is sometimes used in the main SQL grammar, we have to be
29722991
* careful not to take any such usage of INTO as a PL/pgSQL INTO clause.
29732992
* There are currently three such cases:
@@ -2993,13 +3012,50 @@ make_execsql_stmt(int firsttoken, int location)
29933012
* break this logic again ... beware!
29943013
*/
29953014
tok = firsttoken;
3015+
if (tok == T_WORD && strcmp(word->ident, "create") == 0)
3016+
tokens[token_count] = 'c';
3017+
token_count++;
3018+
29963019
for (;;)
29973020
{
29983021
prev_tok = tok;
29993022
tok = yylex();
30003023
if (have_into && into_end_loc < 0)
30013024
into_end_loc = yylloc;/* token after the INTO part*/
3002-
if (tok == ';')
3025+
/* Detect CREATE [OR REPLACE] {FUNCTION|PROCEDURE}*/
3026+
if (tokens[0] == 'c' && token_count < sizeof(tokens))
3027+
{
3028+
if (tok == K_OR)
3029+
tokens[token_count] = 'o';
3030+
else if (tok == T_WORD &&
3031+
strcmp(yylval.word.ident, "replace") == 0)
3032+
tokens[token_count] = 'r';
3033+
else if (tok == T_WORD &&
3034+
strcmp(yylval.word.ident, "function") == 0)
3035+
tokens[token_count] = 'f';
3036+
else if (tok == T_WORD &&
3037+
strcmp(yylval.word.ident, "procedure") == 0)
3038+
tokens[token_count] = 'f';/* treat same as "function"*/
3039+
if (tokens[1] == 'f' ||
3040+
(tokens[1] == 'o' && tokens[2] == 'r' && tokens[3] == 'f'))
3041+
in_routine_definition = true;
3042+
token_count++;
3043+
}
3044+
/* Track paren nesting (needed for CREATE RULE syntax)*/
3045+
if (tok == '(')
3046+
paren_depth++;
3047+
else if (tok == ')' && paren_depth > 0)
3048+
paren_depth--;
3049+
/* We need track BEGIN/END nesting only in a routine definition*/
3050+
if (in_routine_definition && paren_depth == 0)
3051+
{
3052+
if (tok == K_BEGIN || tok == K_CASE)
3053+
begin_depth++;
3054+
else if (tok == K_END && begin_depth > 0)
3055+
begin_depth--;
3056+
}
3057+
/* Command-ending semicolon?*/
3058+
if (tok == ';' && paren_depth == 0 && begin_depth == 0)
30033059
break;
30043060
if (tok == 0)
30053061
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