Skip to content

Commit 3d1e01c

Browse files
tglsfdctglsfdc
committed
Support INSERT/UPDATE/DELETE RETURNING in plpgsql, with rowcount checking
as per yesterday's proposal. Also make things a tad more orthogonal by adding the recent STRICT addition to EXECUTE INTO. Jonah Harris and Tom Lane
1 parent 29fa051 commit 3d1e01c

File tree

8 files changed

+800
-451
lines changed

8 files changed

+800
-451
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 185 additions & 109 deletions
Large diffs are not rendered by default.

‎src/pl/plpgsql/src/gram.y

Lines changed: 104 additions & 145 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.94 2006/08/14 00:46:53 tgl Exp $
12+
* $PostgreSQL: pgsql/src/pl/plpgsql/src/gram.y,v 1.95 2006/08/14 21:14:41 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -28,11 +28,13 @@ static PLpgSQL_expr *read_sql_construct(int until,
2828
int *endtoken);
2929
static PLpgSQL_expr *read_sql_stmt(const char *sqlstart);
3030
static PLpgSQL_type *read_datatype(int tok);
31-
static PLpgSQL_stmt *make_select_stmt(int lineno);
31+
static PLpgSQL_stmt *make_execsql_stmt(const char *sqlstart, int lineno);
3232
static PLpgSQL_stmt *make_fetch_stmt(int lineno, int curvar);
3333
static PLpgSQL_stmt *make_return_stmt(int lineno);
3434
static PLpgSQL_stmt *make_return_next_stmt(int lineno);
3535
static void check_assignable(PLpgSQL_datum *datum);
36+
static void read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row,
37+
bool *strict);
3638
static PLpgSQL_row *read_into_scalar_list(const char *initial_name,
3739
PLpgSQL_datum *initial_datum);
3840
static PLpgSQL_row *make_scalar_list1(const char *initial_name,
@@ -120,9 +122,8 @@ static void check_labels(const char *start_label,
120122
%type <loop_body> loop_body
121123
%type <stmt> proc_stmt pl_block
122124
%type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit
123-
%type <stmt> stmt_return stmt_raise stmt_execsql
124-
%type <stmt> stmt_for stmt_select stmt_perform
125-
%type <stmt> stmt_dynexecute stmt_getdiag
125+
%type <stmt> stmt_return stmt_raise stmt_execsql stmt_execsql_insert
126+
%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag
126127
%type <stmt> stmt_open stmt_fetch stmt_close stmt_null
127128

128129
%type <list> proc_exceptions
@@ -169,6 +170,7 @@ static void check_labels(const char *start_label,
169170
%token K_IF
170171
%token K_IN
171172
%token K_INFO
173+
%token K_INSERT
172174
%token K_INTO
173175
%token K_IS
174176
%token K_LOG
@@ -186,7 +188,6 @@ static void check_labels(const char *start_label,
186188
%token K_RESULT_OID
187189
%token K_RETURN
188190
%token K_REVERSE
189-
%token K_SELECT
190191
%token K_STRICT
191192
%token K_THEN
192193
%token K_TO
@@ -591,8 +592,6 @@ proc_stmt : pl_block ';'
591592
{ $$ = $1; }
592593
| stmt_for
593594
{ $$ = $1; }
594-
| stmt_select
595-
{ $$ = $1; }
596595
| stmt_exit
597596
{ $$ = $1; }
598597
| stmt_return
@@ -601,6 +600,8 @@ proc_stmt : pl_block ';'
601600
{ $$ = $1; }
602601
| stmt_execsql
603602
{ $$ = $1; }
603+
| stmt_execsql_insert
604+
{ $$ = $1; }
604605
| stmt_dynexecute
605606
{ $$ = $1; }
606607
| stmt_perform
@@ -1127,12 +1128,6 @@ for_variable : T_SCALAR
11271128
}
11281129
;
11291130

1130-
stmt_select : K_SELECT lno
1131-
{
1132-
$$ = make_select_stmt($2);
1133-
}
1134-
;
1135-
11361131
stmt_exit : exit_type lno opt_label opt_exitcond
11371132
{
11381133
PLpgSQL_stmt_exit *new;
@@ -1259,14 +1254,28 @@ loop_body : proc_sect K_END K_LOOP opt_label ';'
12591254

12601255
stmt_execsql : execsql_start lno
12611256
{
1262-
PLpgSQL_stmt_execsql *new;
1257+
$$ = make_execsql_stmt($1, $2);
1258+
}
1259+
;
12631260

1264-
new = palloc(sizeof(PLpgSQL_stmt_execsql));
1265-
new->cmd_type = PLPGSQL_STMT_EXECSQL;
1266-
new->lineno = $2;
1267-
new->sqlstmt = read_sql_stmt($1);
1261+
/* this matches any otherwise-unrecognized starting keyword */
1262+
execsql_start : T_WORD
1263+
{ $$ = pstrdup(yytext); }
1264+
| T_ERROR
1265+
{ $$ = pstrdup(yytext); }
1266+
;
12681267

1269-
$$ = (PLpgSQL_stmt *)new;
1268+
stmt_execsql_insert : K_INSERT lno K_INTO
1269+
{
1270+
/*
1271+
* We have to special-case INSERT so that its INTO
1272+
* won't be treated as an INTO-variables clause.
1273+
*
1274+
* Fortunately, this is the only valid use of INTO
1275+
* in a pl/pgsql SQL command, and INTO is already
1276+
* a fully reserved word in the main grammar.
1277+
*/
1278+
$$ = make_execsql_stmt("INSERT INTO", $2);
12701279
}
12711280
;
12721281

@@ -1276,46 +1285,24 @@ stmt_dynexecute : K_EXECUTE lno
12761285
PLpgSQL_expr *expr;
12771286
int endtoken;
12781287

1279-
expr = read_sql_construct(K_INTO, ';', "INTO|;", "SELECT ",
1288+
expr = read_sql_construct(K_INTO, ';', "INTO|;",
1289+
"SELECT ",
12801290
true, true, &endtoken);
12811291

12821292
new = palloc(sizeof(PLpgSQL_stmt_dynexecute));
12831293
new->cmd_type = PLPGSQL_STMT_DYNEXECUTE;
1284-
new->lineno = $2;
1285-
new->query = expr;
1294+
new->lineno = $2;
1295+
new->query = expr;
1296+
new->into = false;
1297+
new->strict = false;
12861298
new->rec = NULL;
12871299
new->row = NULL;
12881300

1289-
/*
1290-
* If we saw "INTO", look for a following row
1291-
* var, record var, or list of scalars.
1292-
*/
1301+
/* If we found "INTO", collect the argument */
12931302
if (endtoken == K_INTO)
12941303
{
1295-
switch (yylex())
1296-
{
1297-
case T_ROW:
1298-
new->row = yylval.row;
1299-
check_assignable((PLpgSQL_datum *) new->row);
1300-
break;
1301-
1302-
case T_RECORD:
1303-
new->rec = yylval.rec;
1304-
check_assignable((PLpgSQL_datum *) new->rec);
1305-
break;
1306-
1307-
case T_SCALAR:
1308-
new->row = read_into_scalar_list(yytext, yylval.scalar);
1309-
break;
1310-
1311-
default:
1312-
plpgsql_error_lineno = $2;
1313-
ereport(ERROR,
1314-
(errcode(ERRCODE_SYNTAX_ERROR),
1315-
errmsg("syntax error at \"%s\"", yytext),
1316-
errdetail("Expected record variable, row variable, "
1317-
"or list of scalar variables.")));
1318-
}
1304+
new->into = true;
1305+
read_into_target(&new->rec, &new->row, &new->strict);
13191306
if (yylex() != ';')
13201307
yyerror("syntax error");
13211308
}
@@ -1502,12 +1489,6 @@ cursor_variable : T_SCALAR
15021489
}
15031490
;
15041491

1505-
execsql_start : T_WORD
1506-
{ $$ = pstrdup(yytext); }
1507-
| T_ERROR
1508-
{ $$ = pstrdup(yytext); }
1509-
;
1510-
15111492
exception_sect :
15121493
{ $$ = NULL; }
15131494
| K_EXCEPTION lno
@@ -1892,12 +1873,13 @@ read_datatype(int tok)
18921873
}
18931874

18941875
static PLpgSQL_stmt *
1895-
make_select_stmt(int lineno)
1876+
make_execsql_stmt(const char *sqlstart, int lineno)
18961877
{
18971878
PLpgSQL_dstring ds;
18981879
int nparams = 0;
18991880
int params[MAX_EXPR_PARAMS];
19001881
char buf[32];
1882+
PLpgSQL_stmt_execsql *execsql;
19011883
PLpgSQL_expr *expr;
19021884
PLpgSQL_row *row = NULL;
19031885
PLpgSQL_rec *rec = NULL;
@@ -1906,12 +1888,11 @@ make_select_stmt(int lineno)
19061888
bool have_strict = false;
19071889

19081890
plpgsql_dstring_init(&ds);
1909-
plpgsql_dstring_append(&ds, "SELECT ");
1891+
plpgsql_dstring_append(&ds, sqlstart);
19101892

1911-
while (1)
1893+
for (;;)
19121894
{
19131895
tok = yylex();
1914-
19151896
if (tok == ';')
19161897
break;
19171898
if (tok == 0)
@@ -1930,37 +1911,8 @@ make_select_stmt(int lineno)
19301911
(errcode(ERRCODE_SYNTAX_ERROR),
19311912
errmsg("INTO specified more than once")));
19321913
}
1933-
tok = yylex();
1934-
if (tok == K_STRICT)
1935-
{
1936-
have_strict = true;
1937-
tok = yylex();
1938-
}
1939-
switch (tok)
1940-
{
1941-
case T_ROW:
1942-
row = yylval.row;
1943-
check_assignable((PLpgSQL_datum *) row);
1944-
have_into = true;
1945-
break;
1946-
1947-
case T_RECORD:
1948-
rec = yylval.rec;
1949-
check_assignable((PLpgSQL_datum *) rec);
1950-
have_into = true;
1951-
break;
1952-
1953-
case T_SCALAR:
1954-
row = read_into_scalar_list(yytext, yylval.scalar);
1955-
have_into = true;
1956-
break;
1957-
1958-
default:
1959-
/* Treat the INTO as non-special */
1960-
plpgsql_dstring_append(&ds, " INTO ");
1961-
plpgsql_push_back_token(tok);
1962-
break;
1963-
}
1914+
have_into = true;
1915+
read_into_target(&rec, &row, &have_strict);
19641916
continue;
19651917
}
19661918

@@ -2007,70 +1959,29 @@ make_select_stmt(int lineno)
20071959

20081960
check_sql_expr(expr->query);
20091961

2010-
if (have_into)
2011-
{
2012-
PLpgSQL_stmt_select *select;
2013-
2014-
select = palloc0(sizeof(PLpgSQL_stmt_select));
2015-
select->cmd_type = PLPGSQL_STMT_SELECT;
2016-
select->lineno = lineno;
2017-
select->rec = rec;
2018-
select->row = row;
2019-
select->query = expr;
2020-
select->strict = have_strict;
2021-
2022-
return (PLpgSQL_stmt *)select;
2023-
}
2024-
else
2025-
{
2026-
PLpgSQL_stmt_execsql *execsql;
2027-
2028-
execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
2029-
execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
2030-
execsql->lineno = lineno;
2031-
execsql->sqlstmt = expr;
1962+
execsql = palloc(sizeof(PLpgSQL_stmt_execsql));
1963+
execsql->cmd_type = PLPGSQL_STMT_EXECSQL;
1964+
execsql->lineno = lineno;
1965+
execsql->sqlstmt = expr;
1966+
execsql->into = have_into;
1967+
execsql->strict = have_strict;
1968+
execsql->rec = rec;
1969+
execsql->row = row;
20321970

2033-
return (PLpgSQL_stmt *)execsql;
2034-
}
1971+
return (PLpgSQL_stmt *) execsql;
20351972
}
20361973

20371974

20381975
static PLpgSQL_stmt *
20391976
make_fetch_stmt(int lineno, int curvar)
20401977
{
20411978
int tok;
2042-
PLpgSQL_row *row = NULL;
2043-
PLpgSQL_rec *rec = NULL;
1979+
PLpgSQL_rec *rec;
1980+
PLpgSQL_row *row;
20441981
PLpgSQL_stmt_fetch *fetch;
20451982

20461983
/* We have already parsed everything through the INTO keyword */
2047-
2048-
tok = yylex();
2049-
switch (tok)
2050-
{
2051-
case T_ROW:
2052-
row = yylval.row;
2053-
check_assignable((PLpgSQL_datum *) row);
2054-
break;
2055-
2056-
case T_RECORD:
2057-
rec = yylval.rec;
2058-
check_assignable((PLpgSQL_datum *) rec);
2059-
break;
2060-
2061-
case T_SCALAR:
2062-
row = read_into_scalar_list(yytext, yylval.scalar);
2063-
break;
2064-
2065-
default:
2066-
plpgsql_error_lineno = plpgsql_scanner_lineno();
2067-
ereport(ERROR,
2068-
(errcode(ERRCODE_SYNTAX_ERROR),
2069-
errmsg("syntax error at \"%s\"", yytext),
2070-
errdetail("Expected record variable, row variable, "
2071-
"or list of scalar variables.")));
2072-
}
2073-
1984+
read_into_target(&rec, &row, NULL);
20741985
tok = yylex();
20751986
if (tok != ';')
20761987
yyerror("syntax error");
@@ -2232,6 +2143,54 @@ check_assignable(PLpgSQL_datum *datum)
22322143
}
22332144
}
22342145

2146+
/*
2147+
* Read the argument of an INTO clause. On entry, we have just read the
2148+
* INTO keyword.
2149+
*/
2150+
static void
2151+
read_into_target(PLpgSQL_rec **rec, PLpgSQL_row **row, bool *strict)
2152+
{
2153+
int tok;
2154+
2155+
/* Set default results */
2156+
*rec = NULL;
2157+
*row = NULL;
2158+
if (strict)
2159+
*strict = false;
2160+
2161+
tok = yylex();
2162+
if (strict && tok == K_STRICT)
2163+
{
2164+
*strict = true;
2165+
tok = yylex();
2166+
}
2167+
2168+
switch (tok)
2169+
{
2170+
case T_ROW:
2171+
*row = yylval.row;
2172+
check_assignable((PLpgSQL_datum *) *row);
2173+
break;
2174+
2175+
case T_RECORD:
2176+
*rec = yylval.rec;
2177+
check_assignable((PLpgSQL_datum *) *rec);
2178+
break;
2179+
2180+
case T_SCALAR:
2181+
*row = read_into_scalar_list(yytext, yylval.scalar);
2182+
break;
2183+
2184+
default:
2185+
plpgsql_error_lineno = plpgsql_scanner_lineno();
2186+
ereport(ERROR,
2187+
(errcode(ERRCODE_SYNTAX_ERROR),
2188+
errmsg("syntax error at \"%s\"", yytext),
2189+
errdetail("Expected record variable, row variable, "
2190+
"or list of scalar variables following INTO.")));
2191+
}
2192+
}
2193+
22352194
/*
22362195
* Given the first datum and name in the INTO list, continue to read
22372196
* comma-separated scalar variables until we run out. Then construct

0 commit comments

Comments
 (0)