commit 108d99d96cffe5468ec95308a34f9164d8310119 Author: okbob@github.com Date: Sat Jun 8 13:09:40 2024 +0200 use strict rules for parsing PL/pgSQL expressions Originaly the rule PLpgSQL_Expr allows almost all SQL clauses. It was designed to allow old undocummented syntax var := col FROM tab; The reason for support of this "strange" syntax was technical. The PLpgSQL parser cannot to use SQL parser accurately (it was really primitive), and people found this undocumented syntax. Lattery, when it was possible to do exact parsing, from compatibility reasons, the parsing of PL/pgSQL expressions allows described syntax. Unfortunately, with support almost all SQL clauses, the PLpgSQL can accept really broken code like DO $$ DECLARE l_cnt int; BEGIN l_cnt := 1 DELETE FROM foo3 WHERE id=1; END; $$; proposed patch use strictly a_expr for PLpgSQL expressions, and solve this issue. The change of definition of PLpgSQL_Expr has an impact on OPEN and CASE PLpgSQL statements that use multicolumn results. This patch has lot of impacts on regress tests - it can be zero, if the strict syntax will be enabled by using plpgsql.extra_errors instead. Now, the changes of regress tests can be interesting for visibility of impact of strict mode. diff --git a/contrib/basic_archive/expected/basic_archive.out b/contrib/basic_archive/expected/basic_archive.out index 0015053e0f..280ff3e022 100644 --- a/contrib/basic_archive/expected/basic_archive.out +++ b/contrib/basic_archive/expected/basic_archive.out @@ -11,8 +11,8 @@ DECLARE loops int := 0; BEGIN LOOP - archived := count(*) > 0 FROM pg_ls_dir('.', false, false) a - WHERE a ~ '^[0-9A-F]{24}$'; + archived := (SELECT count(*) > 0 FROM pg_ls_dir('.', false, false) a + WHERE a ~ '^[0-9A-F]{24}$'); IF archived OR loops > 120 * 10 THEN EXIT; END IF; PERFORM pg_sleep(0.1); loops := loops + 1; diff --git a/contrib/basic_archive/sql/basic_archive.sql b/contrib/basic_archive/sql/basic_archive.sql index 14e236d57a..2c127a821f 100644 --- a/contrib/basic_archive/sql/basic_archive.sql +++ b/contrib/basic_archive/sql/basic_archive.sql @@ -7,8 +7,8 @@ DECLARE loops int := 0; BEGIN LOOP - archived := count(*) > 0 FROM pg_ls_dir('.', false, false) a - WHERE a ~ '^[0-9A-F]{24}$'; + archived := (SELECT count(*) > 0 FROM pg_ls_dir('.', false, false) a + WHERE a ~ '^[0-9A-F]{24}$'); IF archived OR loops > 120 * 10 THEN EXIT; END IF; PERFORM pg_sleep(0.1); loops := loops + 1; diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index e516c0a67c..40d98da628 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -2983,6 +2983,10 @@ _SPI_error_callback(void *arg) case RAW_PARSE_PLPGSQL_ASSIGN3: errcontext("PL/pgSQL assignment \"%s\"", query); break; + case RAW_PARSE_PLPGSQL_EXPR_LIST: + case RAW_PARSE_PLPGSQL_EXPR_WITH_LABEL_LIST: + errcontext("PL/pgSQL stetement OPEN \"%s\"", query); + break; default: errcontext("SQL statement \"%s\"", query); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 4d582950b7..e5409bb7f2 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -65,7 +65,6 @@ #include "utils/numeric.h" #include "utils/xml.h" - /* * Location tracking support --- simpler than bison's default, since we only * want to track the start position not the end position of each nonterminal. @@ -323,7 +322,11 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type select_no_parens select_with_parens select_clause simple_select values_clause - PLpgSQL_Expr PLAssignStmt + PLpgSQL_Expr PLAssignStmt PLpgSQL_Expr_list + PLpgSQL_Expr_with_label_list + +%type plpgsql_unnamed_target_el +%type plpgsql_unnamed_target_list %type opt_single_name %type opt_qualified_name @@ -439,7 +442,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); name_list role_list from_clause from_list opt_array_bounds qualified_name_list any_name any_name_list type_name_list any_operator expr_list attrs - distinct_clause opt_distinct_clause + distinct_clause target_list opt_target_list insert_column_list set_target_list merge_values_clause set_clause_list set_clause @@ -827,7 +830,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %token MODE_PLPGSQL_ASSIGN1 %token MODE_PLPGSQL_ASSIGN2 %token MODE_PLPGSQL_ASSIGN3 - +%token MODE_PLPGSQL_EXPR_LIST +%token MODE_PLPGSQL_EXPR_WITH_LABEL_LIST /* Precedence: lowest to highest */ %left UNION EXCEPT @@ -958,6 +962,16 @@ parse_toplevel: pg_yyget_extra(yyscanner)->parsetree = list_make1(makeRawStmt((Node *) n, 0)); } + | MODE_PLPGSQL_EXPR_LIST PLpgSQL_Expr_list + { + pg_yyget_extra(yyscanner)->parsetree = + list_make1(makeRawStmt($2, 0)); + } + | MODE_PLPGSQL_EXPR_WITH_LABEL_LIST PLpgSQL_Expr_with_label_list + { + pg_yyget_extra(yyscanner)->parsetree = + list_make1(makeRawStmt($2, 0)); + } ; /* @@ -13118,11 +13132,6 @@ opt_all_clause: | /*EMPTY*/ ; -opt_distinct_clause: - distinct_clause { $$ = $1; } - | opt_all_clause { $$ = NIL; } - ; - opt_sort_clause: sort_clause { $$ = $1; } | /*EMPTY*/ { $$ = NIL; } @@ -17429,7 +17438,6 @@ role_list: RoleSpec { $$ = lappend($1, $3); } ; - /***************************************************************************** * * PL/pgSQL extensions @@ -17439,34 +17447,61 @@ role_list: RoleSpec * Therefore the returned struct is a SelectStmt. *****************************************************************************/ -PLpgSQL_Expr: opt_distinct_clause opt_target_list - from_clause where_clause - group_clause having_clause window_clause - opt_sort_clause opt_select_limit opt_for_locking_clause +PLpgSQL_Expr: a_expr { SelectStmt *n = makeNode(SelectStmt); + ResTarget *rt; + + rt = makeNode(ResTarget); + rt->name = NULL; + rt->indirection = NIL; + rt->val = (Node *) $1; + rt->location = @1; + + n->targetList = list_make1((Node *) rt); + + $$ = (Node *) n; + } + ; + +plpgsql_unnamed_target_el: a_expr + { + $$ = makeNode(ResTarget); + $$->name = NULL; + $$->indirection = NIL; + $$->val = (Node *) $1; + $$->location = @1; + } + ; + +plpgsql_unnamed_target_list: + plpgsql_unnamed_target_el + { + $$ = list_make1($1); + } + | plpgsql_unnamed_target_list ',' plpgsql_unnamed_target_el + { + $$ = lappend($1, $3); + } + ; + +PLpgSQL_Expr_list: plpgsql_unnamed_target_list + { + SelectStmt *n = makeNode(SelectStmt); + + n->targetList = $1; + + $$ = (Node *) n; + + } + ; + +PLpgSQL_Expr_with_label_list: target_list + { + SelectStmt *n = makeNode(SelectStmt); + + n->targetList = $1; - n->distinctClause = $1; - n->targetList = $2; - n->fromClause = $3; - n->whereClause = $4; - n->groupClause = ($5)->list; - n->groupDistinct = ($5)->distinct; - n->havingClause = $6; - n->windowClause = $7; - n->sortClause = $8; - if ($9) - { - n->limitOffset = $9->limitOffset; - n->limitCount = $9->limitCount; - if (!n->sortClause && - $9->limitOption == LIMIT_OPTION_WITH_TIES) - ereport(ERROR, - (errcode(ERRCODE_SYNTAX_ERROR), - errmsg("WITH TIES cannot be specified without ORDER BY clause"))); - n->limitOption = $9->limitOption; - } - n->lockingClause = $10; $$ = (Node *) n; } ; diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index 118488c3f3..5fc94695fb 100644 --- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -62,6 +62,8 @@ raw_parser(const char *str, RawParseMode mode) [RAW_PARSE_PLPGSQL_ASSIGN1] = MODE_PLPGSQL_ASSIGN1, [RAW_PARSE_PLPGSQL_ASSIGN2] = MODE_PLPGSQL_ASSIGN2, [RAW_PARSE_PLPGSQL_ASSIGN3] = MODE_PLPGSQL_ASSIGN3, + [RAW_PARSE_PLPGSQL_EXPR_LIST] = MODE_PLPGSQL_EXPR_LIST, + [RAW_PARSE_PLPGSQL_EXPR_WITH_LABEL_LIST] = MODE_PLPGSQL_EXPR_WITH_LABEL_LIST, }; yyextra.have_lookahead = true; diff --git a/src/include/parser/parser.h b/src/include/parser/parser.h index be184ec506..890b9451e2 100644 --- a/src/include/parser/parser.h +++ b/src/include/parser/parser.h @@ -33,6 +33,12 @@ * RAW_PARSE_PLPGSQL_ASSIGNn: parse a PL/pgSQL assignment statement, * and return a one-element List containing a RawStmt node. "n" * gives the number of dotted names comprising the target ColumnRef. + * + * RAW_PARSE_PLPGSQL_EXPR_LIST is used by PLpgSQL cursors when we detect + * no named argument is used. + * + * RAW_PARSE_PLPGSQL_EXPR_WITH_LABEL_LIST is used for cursor's argumet + * when named arguments are used. */ typedef enum { @@ -42,6 +48,8 @@ typedef enum RAW_PARSE_PLPGSQL_ASSIGN1, RAW_PARSE_PLPGSQL_ASSIGN2, RAW_PARSE_PLPGSQL_ASSIGN3, + RAW_PARSE_PLPGSQL_EXPR_LIST, + RAW_PARSE_PLPGSQL_EXPR_WITH_LABEL_LIST } RawParseMode; /* Values for the backslash_quote GUC */ diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl index fe8d3e5178..9c96d6e017 100644 --- a/src/interfaces/ecpg/preproc/parse.pl +++ b/src/interfaces/ecpg/preproc/parse.pl @@ -89,7 +89,11 @@ my %replace_types = ( 'PLpgSQL_Expr' => 'ignore', 'PLAssignStmt' => 'ignore', 'plassign_target' => 'ignore', - 'plassign_equals' => 'ignore',); + 'plassign_equals' => 'ignore', + 'PLpgSQL_Expr_list' => 'ignore', + 'PLpgSQL_Expr_with_label_list' => 'ignore', + 'plpgsql_unnamed_target_el' => 'ignore', + 'plpgsql_unnamed_target_list' => 'ignore'); # these replace_line commands excise certain keywords from the core keyword # lists. Be sure to account for these in ColLabel and related productions. diff --git a/src/pl/plpgsql/src/expected/plpgsql_array.out b/src/pl/plpgsql/src/expected/plpgsql_array.out index ad60e0e8be..6819c0435f 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_array.out +++ b/src/pl/plpgsql/src/expected/plpgsql_array.out @@ -50,34 +50,38 @@ do $$ declare a quadarray; begin a.c1[1].i := 11; raise notice 'a = %, a.c1[1].i = %', a, a.c1[1].i; end$$; NOTICE: a = ("{""(,11)""}",), a.c1[1].i = 11 do $$ declare a int[]; -begin a := array_agg(x) from (values(1),(2),(3)) v(x); raise notice 'a = %', a; end$$; +begin a := (select array_agg(x) from (values(1),(2),(3)) v(x)); raise notice 'a = %', a; end$$; NOTICE: a = {1,2,3} create temp table onecol as select array[1,2] as f1; do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; NOTICE: a = {1,2} do $$ declare a int[]; -begin a := * from onecol for update; raise notice 'a = %', a; end$$; +begin a := (select * from onecol for update); raise notice 'a = %', a; end$$; NOTICE: a = {1,2} -- error cases: do $$ declare a int[]; -begin a := from onecol; raise notice 'a = %', a; end$$; -ERROR: assignment source returned 0 columns -CONTEXT: PL/pgSQL assignment "a := from onecol" -PL/pgSQL function inline_code_block line 2 at assignment +begin a := (select from onecol); raise notice 'a = %', a; end$$; +ERROR: subquery must return only one column +LINE 1: a := (select from onecol) + ^ +QUERY: a := (select from onecol) +CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment do $$ declare a int[]; -begin a := f1, f1 from onecol; raise notice 'a = %', a; end$$; -ERROR: assignment source returned 2 columns -CONTEXT: PL/pgSQL assignment "a := f1, f1 from onecol" -PL/pgSQL function inline_code_block line 2 at assignment +begin a := (select f1, f1 from onecol); raise notice 'a = %', a; end$$; +ERROR: subquery must return only one column +LINE 1: a := (select f1, f1 from onecol) + ^ +QUERY: a := (select f1, f1 from onecol) +CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment insert into onecol values(array[11]); do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; -ERROR: query returned more than one row -CONTEXT: query: a := f1 from onecol +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; +ERROR: more than one row returned by a subquery used as an expression +CONTEXT: PL/pgSQL assignment "a := (select f1 from onecol)" PL/pgSQL function inline_code_block line 2 at assignment do $$ declare a int[]; -begin a := f1 from onecol limit 1; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol limit 1); raise notice 'a = %', a; end$$; NOTICE: a = {1,2} do $$ declare a real; begin a[1] := 2; raise notice 'a = %', a; end$$; diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index a29d2dfacd..2c36bc6800 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -70,6 +70,8 @@ static PLpgSQL_expr *read_sql_construct(int until, int *endtoken); static PLpgSQL_expr *read_sql_expression(int until, const char *expected); +static PLpgSQL_expr *read_sql_expressions(int until, + const char *expected); static PLpgSQL_expr *read_sql_expression2(int until, int until2, const char *expected, int *endtoken); @@ -176,7 +178,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type decl_aliasitem %type expr_until_semi -%type expr_until_then expr_until_loop opt_expr_until_when +%type expr_until_then expr_until_loop opt_expr_until_when exprs_until_then %type opt_exitcond %type cursor_variable @@ -1239,7 +1241,7 @@ case_when_list : case_when_list case_when } ; -case_when : K_WHEN expr_until_then proc_sect +case_when : K_WHEN exprs_until_then proc_sect { PLpgSQL_case_when *new = palloc(sizeof(PLpgSQL_case_when)); @@ -1250,6 +1252,10 @@ case_when : K_WHEN expr_until_then proc_sect } ; +exprs_until_then : + { $$ = read_sql_expressions(K_THEN, "THEN"); } + ; + opt_case_else : { $$ = NIL; @@ -2641,6 +2647,15 @@ read_sql_expression(int until, const char *expected) true, true, NULL, NULL); } +/* Convenience routine to read an expression with one possible terminator */ +static PLpgSQL_expr * +read_sql_expressions(int until, const char *expected) +{ + return read_sql_construct(until, 0, 0, expected, + RAW_PARSE_PLPGSQL_EXPR_LIST, + true, true, NULL, NULL); +} + /* Convenience routine to read an expression with two possible terminators */ static PLpgSQL_expr * read_sql_expression2(int until, int until2, const char *expected, @@ -3977,7 +3992,7 @@ read_cursor_args(PLpgSQL_var *cursor, int until) expr = palloc0(sizeof(PLpgSQL_expr)); expr->query = pstrdup(ds.data); - expr->parseMode = RAW_PARSE_PLPGSQL_EXPR; + expr->parseMode = any_named ? RAW_PARSE_PLPGSQL_EXPR_WITH_LABEL_LIST : RAW_PARSE_PLPGSQL_EXPR_LIST; expr->plan = NULL; expr->paramnos = NULL; expr->target_param = -1; @@ -4151,7 +4166,7 @@ make_case(int location, PLpgSQL_expr *t_expr, StringInfoData ds; /* We expect to have expressions not statements */ - Assert(expr->parseMode == RAW_PARSE_PLPGSQL_EXPR); + Assert(expr->parseMode == RAW_PARSE_PLPGSQL_EXPR_LIST); /* Do the string hacking */ initStringInfo(&ds); diff --git a/src/pl/plpgsql/src/sql/plpgsql_array.sql b/src/pl/plpgsql/src/sql/plpgsql_array.sql index 4b9ff51594..699713696d 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_array.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_array.sql @@ -46,31 +46,31 @@ do $$ declare a quadarray; begin a.c1[1].i := 11; raise notice 'a = %, a.c1[1].i = %', a, a.c1[1].i; end$$; do $$ declare a int[]; -begin a := array_agg(x) from (values(1),(2),(3)) v(x); raise notice 'a = %', a; end$$; +begin a := (select array_agg(x) from (values(1),(2),(3)) v(x)); raise notice 'a = %', a; end$$; create temp table onecol as select array[1,2] as f1; do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; do $$ declare a int[]; -begin a := * from onecol for update; raise notice 'a = %', a; end$$; +begin a := (select * from onecol for update); raise notice 'a = %', a; end$$; -- error cases: do $$ declare a int[]; -begin a := from onecol; raise notice 'a = %', a; end$$; +begin a := (select from onecol); raise notice 'a = %', a; end$$; do $$ declare a int[]; -begin a := f1, f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1, f1 from onecol); raise notice 'a = %', a; end$$; insert into onecol values(array[11]); do $$ declare a int[]; -begin a := f1 from onecol; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol); raise notice 'a = %', a; end$$; do $$ declare a int[]; -begin a := f1 from onecol limit 1; raise notice 'a = %', a; end$$; +begin a := (select f1 from onecol limit 1); raise notice 'a = %', a; end$$; do $$ declare a real; begin a[1] := 2; raise notice 'a = %', a; end$$; diff --git a/src/test/recovery/t/026_overwrite_contrecord.pl b/src/test/recovery/t/026_overwrite_contrecord.pl index f3e27c19af..80124c084f 100644 --- a/src/test/recovery/t/026_overwrite_contrecord.pl +++ b/src/test/recovery/t/026_overwrite_contrecord.pl @@ -33,7 +33,7 @@ $node->safe_psql( 'postgres', q{ DO $$ DECLARE - wal_segsize int := setting::int FROM pg_settings WHERE name = 'wal_segment_size'; + wal_segsize int := (select setting::int FROM pg_settings WHERE name = 'wal_segment_size'); remain int; iters int := 0; BEGIN @@ -43,7 +43,7 @@ BEGIN from generate_series(1, 10) g; remain := wal_segsize - (pg_current_wal_insert_lsn() - '0/0') % wal_segsize; - IF remain < 2 * setting::int from pg_settings where name = 'block_size' THEN + IF (select remain < 2 * setting::int from pg_settings where name = 'block_size') THEN RAISE log 'exiting after % iterations, % bytes to end of WAL segment', iters, remain; EXIT; END IF; diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 673361e840..79893df484 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2627,7 +2627,7 @@ LANGUAGE plpgsql AS $$ DECLARE v_relfilenode oid; BEGIN - v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename; + v_relfilenode := (select relfilenode FROM pg_class WHERE oid = p_tablename); EXECUTE p_ddl; diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out index 4e59188196..faae99515f 100644 --- a/src/test/regress/expected/plancache.out +++ b/src/test/regress/expected/plancache.out @@ -139,7 +139,7 @@ create temp view v1 as select 2+2 as f1; create function cache_test_2() returns int as $$ begin - return f1 from v1; + return (select f1 from v1); end$$ language plpgsql; select cache_test_2(); cache_test_2 diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index 074af8f33a..f2eaac9d69 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -121,7 +121,7 @@ create trigger tg_room_ad after delete -- ************************************************************ create function tg_wslot_biu() returns trigger as $$ begin - if count(*) = 0 from Room where roomno = new.roomno then + if (select count(*) = 0 from Room where roomno = new.roomno) then raise exception 'Room % does not exist', new.roomno; end if; return new; @@ -286,7 +286,7 @@ begin raise exception ''no manual manipulation of HSlot''; end if; if tg_op = ''UPDATE'' and new.hubname != old.hubname then - if count(*) > 0 from Hub where name = old.hubname then + if (select count(*) > 0 from Hub where name = old.hubname) then raise exception ''no manual manipulation of HSlot''; end if; end if; @@ -942,12 +942,12 @@ begin return retval || pslot_backlink_view(psrec.slotlink); end if; if sltype = ''HS'' then - retval := comment from Hub H, HSlot HS + retval := (select comment from Hub H, HSlot HS where HS.slotname = psrec.slotlink - and H.name = HS.hubname; + and H.name = HS.hubname); retval := retval || '' slot ''; - retval := retval || slotno::text from HSlot - where slotname = psrec.slotlink; + retval := (select retval || slotno::text from HSlot + where slotname = psrec.slotlink); return retval; end if; return psrec.slotlink; @@ -2388,7 +2388,7 @@ begin end $$ language plpgsql; select namedparmcursor_test7(); ERROR: division by zero -CONTEXT: SQL expression "42/0 AS p1, 77 AS p2" +CONTEXT: PL/pgSQL stetement OPEN "42/0 AS p1, 77 AS p2" PL/pgSQL function namedparmcursor_test7() line 6 at OPEN -- check that line comments work correctly within the argument list -- (this used to require a special hack in the code; it no longer does, diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 8c4da95508..6335487546 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -327,7 +327,7 @@ CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt; CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1; DO $$ DECLARE - relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass; + relname text := (select reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass); BEGIN EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname; EXCEPTION WHEN wrong_object_type THEN diff --git a/src/test/regress/expected/transactions.out b/src/test/regress/expected/transactions.out index 7f5757e89c..3458dad174 100644 --- a/src/test/regress/expected/transactions.out +++ b/src/test/regress/expected/transactions.out @@ -543,7 +543,7 @@ select * from xacttest; rollback; -- Now the same test with plpgsql (since it depends on SPI which is different) create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' stable; +'begin return (select max(a) from xacttest); end' stable; begin; update xacttest set a = max_xacttest() + 10 where a > 0; select * from xacttest; @@ -558,7 +558,7 @@ select * from xacttest; rollback; create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' volatile; +'begin return (select max(a) from xacttest); end' volatile; begin; update xacttest set a = max_xacttest() + 10 where a > 0; select * from xacttest; diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index 8c8fa27a6a..62a6c49d81 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1667,7 +1667,7 @@ LANGUAGE plpgsql AS $$ DECLARE v_relfilenode oid; BEGIN - v_relfilenode := relfilenode FROM pg_class WHERE oid = p_tablename; + v_relfilenode := (select relfilenode FROM pg_class WHERE oid = p_tablename); EXECUTE p_ddl; diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql index 4b2f11dcc6..a3dbd93468 100644 --- a/src/test/regress/sql/plancache.sql +++ b/src/test/regress/sql/plancache.sql @@ -81,7 +81,7 @@ create temp view v1 as create function cache_test_2() returns int as $$ begin - return f1 from v1; + return (select f1 from v1); end$$ language plpgsql; select cache_test_2(); diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 18c91572ae..fbc21b1f52 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -161,7 +161,7 @@ create trigger tg_room_ad after delete -- ************************************************************ create function tg_wslot_biu() returns trigger as $$ begin - if count(*) = 0 from Room where roomno = new.roomno then + if (select count(*) = 0 from Room where roomno = new.roomno) then raise exception 'Room % does not exist', new.roomno; end if; return new; @@ -348,7 +348,7 @@ begin raise exception ''no manual manipulation of HSlot''; end if; if tg_op = ''UPDATE'' and new.hubname != old.hubname then - if count(*) > 0 from Hub where name = old.hubname then + if (select count(*) > 0 from Hub where name = old.hubname) then raise exception ''no manual manipulation of HSlot''; end if; end if; @@ -1071,12 +1071,12 @@ begin return retval || pslot_backlink_view(psrec.slotlink); end if; if sltype = ''HS'' then - retval := comment from Hub H, HSlot HS + retval := (select comment from Hub H, HSlot HS where HS.slotname = psrec.slotlink - and H.name = HS.hubname; + and H.name = HS.hubname); retval := retval || '' slot ''; - retval := retval || slotno::text from HSlot - where slotname = psrec.slotlink; + retval := (select retval || slotno::text from HSlot + where slotname = psrec.slotlink); return retval; end if; return psrec.slotlink; diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 0c08a6cc42..36cfa31c9f 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -209,7 +209,7 @@ CREATE STATISTICS tststats.s8 ON a, b FROM tststats.pt; CREATE STATISTICS tststats.s9 ON a, b FROM tststats.pt1; DO $$ DECLARE - relname text := reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass; + relname text := (select reltoastrelid::regclass FROM pg_class WHERE oid = 'tststats.t'::regclass); BEGIN EXECUTE 'CREATE STATISTICS tststats.s10 ON a, b FROM ' || relname; EXCEPTION WHEN wrong_object_type THEN diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sql index 51ae1b31b3..1981955ac6 100644 --- a/src/test/regress/sql/transactions.sql +++ b/src/test/regress/sql/transactions.sql @@ -320,7 +320,7 @@ rollback; -- Now the same test with plpgsql (since it depends on SPI which is different) create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' stable; +'begin return (select max(a) from xacttest); end' stable; begin; update xacttest set a = max_xacttest() + 10 where a > 0; @@ -328,7 +328,7 @@ select * from xacttest; rollback; create or replace function max_xacttest() returns smallint language plpgsql as -'begin return max(a) from xacttest; end' volatile; +'begin return (select max(a) from xacttest); end' volatile; begin; update xacttest set a = max_xacttest() + 10 where a > 0;