diff options
Diffstat (limited to 'src/pl/plpgsql')
| -rw-r--r-- | src/pl/plpgsql/src/expected/plpgsql_call.out | 112 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/expected/plpgsql_transaction.out | 2 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_comp.c | 10 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_exec.c | 125 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_funcs.c | 25 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_gram.y | 37 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/pl_scanner.c | 1 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/plpgsql.h | 12 | ||||
| -rw-r--r-- | src/pl/plpgsql/src/sql/plpgsql_call.sql | 107 |
9 files changed, 415 insertions, 16 deletions
diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out index 2f3adcd8d82..1e94a44f2bb 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_call.out +++ b/src/pl/plpgsql/src/expected/plpgsql_call.out @@ -53,6 +53,118 @@ SELECT * FROM test1; 66 (2 rows) +-- output arguments +CREATE PROCEDURE test_proc5(INOUT a text) +LANGUAGE plpgsql +AS $$ +BEGIN + a := a || '+' || a; +END; +$$; +CALL test_proc5('abc'); + a +--------- + abc+abc +(1 row) + +CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int) +LANGUAGE plpgsql +AS $$ +BEGIN + b := b * a; + c := c * a; +END; +$$; +CALL test_proc6(2, 3, 4); + b | c +---+--- + 6 | 8 +(1 row) + +DO +LANGUAGE plpgsql +$$ +DECLARE + x int := 3; + y int := 4; +BEGIN + CALL test_proc6(2, x, y); + RAISE INFO 'x = %, y = %', x, y; +END; +$$; +INFO: x = 6, y = 8 +DO +LANGUAGE plpgsql +$$ +DECLARE + x int := 3; + y int := 4; +BEGIN + CALL test_proc6(2, x + 1, y); -- error + RAISE INFO 'x = %, y = %', x, y; +END; +$$; +ERROR: argument 2 is an output argument but is not writable +CONTEXT: PL/pgSQL function inline_code_block line 6 at CALL +DO +LANGUAGE plpgsql +$$ +DECLARE + x int := 3; + y int := 4; +BEGIN + FOR i IN 1..5 LOOP + CALL test_proc6(i, x, y); + RAISE INFO 'x = %, y = %', x, y; + END LOOP; +END; +$$; +INFO: x = 3, y = 4 +INFO: x = 6, y = 8 +INFO: x = 18, y = 24 +INFO: x = 72, y = 96 +INFO: x = 360, y = 480 +-- recursive with output arguments +CREATE PROCEDURE test_proc7(x int, INOUT a int, INOUT b numeric) +LANGUAGE plpgsql +AS $$ +BEGIN +IF x > 1 THEN + a := x / 10; + b := x / 2; + CALL test_proc7(b::int, a, b); +END IF; +END; +$$; +CALL test_proc7(100, -1, -1); + a | b +---+--- + 0 | 1 +(1 row) + +-- transition variable assignment +TRUNCATE test1; +CREATE FUNCTION triggerfunc1() RETURNS trigger +LANGUAGE plpgsql +AS $$ +DECLARE + z int := 0; +BEGIN + CALL test_proc6(2, NEW.a, NEW.a); + RETURN NEW; +END; +$$; +CREATE TRIGGER t1 BEFORE INSERT ON test1 EXECUTE PROCEDURE triggerfunc1(); +INSERT INTO test1 VALUES (1), (2), (3); +UPDATE test1 SET a = 22 WHERE a = 2; +SELECT * FROM test1 ORDER BY a; + a +---- + 1 + 3 + 22 +(3 rows) + DROP PROCEDURE test_proc1; DROP PROCEDURE test_proc3; DROP PROCEDURE test_proc4; diff --git a/src/pl/plpgsql/src/expected/plpgsql_transaction.out b/src/pl/plpgsql/src/expected/plpgsql_transaction.out index 8ec22c646c2..ce664871370 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_transaction.out +++ b/src/pl/plpgsql/src/expected/plpgsql_transaction.out @@ -98,7 +98,7 @@ SELECT transaction_test3(); ERROR: invalid transaction termination CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT SQL statement "CALL transaction_test1()" -PL/pgSQL function transaction_test3() line 3 at SQL statement +PL/pgSQL function transaction_test3() line 3 at CALL SELECT * FROM test1; a | b ---+--- diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index 391ec41b802..b1a0c1cc4f3 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -475,11 +475,11 @@ do_compile(FunctionCallInfo fcinfo, /* * If there's just one OUT parameter, out_param_varno points * directly to it. If there's more than one, build a row that - * holds all of them. + * holds all of them. Procedures return a row even for one OUT + * parameter. */ - if (num_out_args == 1) - function->out_param_varno = out_arg_variables[0]->dno; - else if (num_out_args > 1) + if (num_out_args > 1 || + (num_out_args == 1 && function->fn_prokind == PROKIND_PROCEDURE)) { PLpgSQL_row *row = build_row_from_vars(out_arg_variables, num_out_args); @@ -487,6 +487,8 @@ do_compile(FunctionCallInfo fcinfo, plpgsql_adddatum((PLpgSQL_datum *) row); function->out_param_varno = row->dno; } + else if (num_out_args == 1) + function->out_param_varno = out_arg_variables[0]->dno; /* * Check for a polymorphic returntype. If found, use the actual diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 489484f184c..827e44019d8 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -24,6 +24,7 @@ #include "catalog/pg_type.h" #include "executor/execExpr.h" #include "executor/spi.h" +#include "executor/spi_priv.h" #include "funcapi.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" @@ -40,6 +41,7 @@ #include "utils/memutils.h" #include "utils/rel.h" #include "utils/snapmgr.h" +#include "utils/syscache.h" #include "utils/typcache.h" #include "plpgsql.h" @@ -253,6 +255,8 @@ static int exec_stmt_assign(PLpgSQL_execstate *estate, PLpgSQL_stmt_assign *stmt); static int exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt); +static int exec_stmt_call(PLpgSQL_execstate *estate, + PLpgSQL_stmt_call *stmt); static int exec_stmt_getdiag(PLpgSQL_execstate *estate, PLpgSQL_stmt_getdiag *stmt); static int exec_stmt_if(PLpgSQL_execstate *estate, @@ -1901,6 +1905,10 @@ exec_stmt(PLpgSQL_execstate *estate, PLpgSQL_stmt *stmt) rc = exec_stmt_perform(estate, (PLpgSQL_stmt_perform *) stmt); break; + case PLPGSQL_STMT_CALL: + rc = exec_stmt_call(estate, (PLpgSQL_stmt_call *) stmt); + break; + case PLPGSQL_STMT_GETDIAG: rc = exec_stmt_getdiag(estate, (PLpgSQL_stmt_getdiag *) stmt); break; @@ -2041,6 +2049,121 @@ exec_stmt_perform(PLpgSQL_execstate *estate, PLpgSQL_stmt_perform *stmt) return PLPGSQL_RC_OK; } +/* + * exec_stmt_call + */ +static int +exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt) +{ + PLpgSQL_expr *expr = stmt->expr; + ParamListInfo paramLI; + int rc; + + if (expr->plan == NULL) + exec_prepare_plan(estate, expr, 0); + + paramLI = setup_param_list(estate, expr); + + rc = SPI_execute_plan_with_paramlist(expr->plan, paramLI, + estate->readonly_func, 0); + + if (rc < 0) + elog(ERROR, "SPI_execute_plan_with_paramlist failed executing query \"%s\": %s", + expr->query, SPI_result_code_string(rc)); + + if (SPI_processed == 1) + { + SPITupleTable *tuptab = SPI_tuptable; + + /* + * Construct a dummy target row based on the output arguments of the + * procedure call. + */ + if (!stmt->target) + { + Node *node; + ListCell *lc; + FuncExpr *funcexpr; + int i; + HeapTuple tuple; + int numargs; + Oid *argtypes; + char **argnames; + char *argmodes; + MemoryContext oldcontext; + PLpgSQL_row *row; + int nfields; + + /* + * Get the original CallStmt + */ + node = linitial_node(Query, ((CachedPlanSource *) linitial(expr->plan->plancache_list))->query_list)->utilityStmt; + if (!IsA(node, CallStmt)) + elog(ERROR, "returned row from not a CallStmt"); + + funcexpr = castNode(CallStmt, node)->funcexpr; + + /* + * Get the argument modes + */ + tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcexpr->funcid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for function %u", funcexpr->funcid); + numargs = get_func_arg_info(tuple, &argtypes, &argnames, &argmodes); + ReleaseSysCache(tuple); + + Assert(numargs == list_length(funcexpr->args)); + + /* + * Construct row + */ + oldcontext = MemoryContextSwitchTo(estate->func->fn_cxt); + + row = palloc0(sizeof(*row)); + row->dtype = PLPGSQL_DTYPE_ROW; + row->lineno = -1; + row->varnos = palloc(sizeof(int) * FUNC_MAX_ARGS); + + nfields = 0; + i = 0; + foreach (lc, funcexpr->args) + { + Node *n = lfirst(lc); + + if (argmodes && argmodes[i] == PROARGMODE_INOUT) + { + Param *param; + + if (!IsA(n, Param)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("argument %d is an output argument but is not writable", i + 1))); + + param = castNode(Param, n); + /* paramid is offset by 1 (see make_datum_param()) */ + row->varnos[nfields++] = param->paramid - 1; + } + i++; + } + + row->nfields = nfields; + + MemoryContextSwitchTo(oldcontext); + + stmt->target = (PLpgSQL_variable *) row; + } + + exec_move_row(estate, stmt->target, tuptab->vals[0], tuptab->tupdesc); + } + else if (SPI_processed > 1) + elog(ERROR, "procedure call returned more than one row"); + + exec_eval_cleanup(estate); + SPI_freetuptable(SPI_tuptable); + + return PLPGSQL_RC_OK; +} + /* ---------- * exec_stmt_getdiag Put internal PG information into * specified variables. @@ -6763,7 +6886,7 @@ exec_move_row_from_fields(PLpgSQL_execstate *estate, return; } - elog(ERROR, "unsupported target"); + elog(ERROR, "unsupported target type: %d", target->dtype); } /* diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index b986fc39b38..39d6a546632 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -284,6 +284,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt) return "CLOSE"; case PLPGSQL_STMT_PERFORM: return "PERFORM"; + case PLPGSQL_STMT_CALL: + return "CALL"; case PLPGSQL_STMT_COMMIT: return "COMMIT"; case PLPGSQL_STMT_ROLLBACK: @@ -367,6 +369,7 @@ static void free_open(PLpgSQL_stmt_open *stmt); static void free_fetch(PLpgSQL_stmt_fetch *stmt); static void free_close(PLpgSQL_stmt_close *stmt); static void free_perform(PLpgSQL_stmt_perform *stmt); +static void free_call(PLpgSQL_stmt_call *stmt); static void free_commit(PLpgSQL_stmt_commit *stmt); static void free_rollback(PLpgSQL_stmt_rollback *stmt); static void free_expr(PLpgSQL_expr *expr); @@ -449,6 +452,9 @@ free_stmt(PLpgSQL_stmt *stmt) case PLPGSQL_STMT_PERFORM: free_perform((PLpgSQL_stmt_perform *) stmt); break; + case PLPGSQL_STMT_CALL: + free_call((PLpgSQL_stmt_call *) stmt); + break; case PLPGSQL_STMT_COMMIT: free_commit((PLpgSQL_stmt_commit *) stmt); break; @@ -603,6 +609,12 @@ free_perform(PLpgSQL_stmt_perform *stmt) } static void +free_call(PLpgSQL_stmt_call *stmt) +{ + free_expr(stmt->expr); +} + +static void free_commit(PLpgSQL_stmt_commit *stmt) { } @@ -805,6 +817,7 @@ static void dump_fetch(PLpgSQL_stmt_fetch *stmt); static void dump_cursor_direction(PLpgSQL_stmt_fetch *stmt); static void dump_close(PLpgSQL_stmt_close *stmt); static void dump_perform(PLpgSQL_stmt_perform *stmt); +static void dump_call(PLpgSQL_stmt_call *stmt); static void dump_commit(PLpgSQL_stmt_commit *stmt); static void dump_rollback(PLpgSQL_stmt_rollback *stmt); static void dump_expr(PLpgSQL_expr *expr); @@ -897,6 +910,9 @@ dump_stmt(PLpgSQL_stmt *stmt) case PLPGSQL_STMT_PERFORM: dump_perform((PLpgSQL_stmt_perform *) stmt); break; + case PLPGSQL_STMT_CALL: + dump_call((PLpgSQL_stmt_call *) stmt); + break; case PLPGSQL_STMT_COMMIT: dump_commit((PLpgSQL_stmt_commit *) stmt); break; @@ -1276,6 +1292,15 @@ dump_perform(PLpgSQL_stmt_perform *stmt) } static void +dump_call(PLpgSQL_stmt_call *stmt) +{ + dump_ind(); + printf("CALL expr = "); + dump_expr(stmt->expr); + printf("\n"); +} + +static void dump_commit(PLpgSQL_stmt_commit *stmt) { dump_ind(); diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index 9fcf2424dae..4c80936678f 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -197,7 +197,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type <stmt> proc_stmt pl_block %type <stmt> stmt_assign stmt_if stmt_loop stmt_while stmt_exit %type <stmt> stmt_return stmt_raise stmt_assert stmt_execsql -%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_getdiag +%type <stmt> stmt_dynexecute stmt_for stmt_perform stmt_call stmt_getdiag %type <stmt> stmt_open stmt_fetch stmt_move stmt_close stmt_null %type <stmt> stmt_commit stmt_rollback %type <stmt> stmt_case stmt_foreach_a @@ -257,6 +257,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token <keyword> K_BACKWARD %token <keyword> K_BEGIN %token <keyword> K_BY +%token <keyword> K_CALL %token <keyword> K_CASE %token <keyword> K_CLOSE %token <keyword> K_COLLATE @@ -872,6 +873,8 @@ proc_stmt : pl_block ';' { $$ = $1; } | stmt_perform { $$ = $1; } + | stmt_call + { $$ = $1; } | stmt_getdiag { $$ = $1; } | stmt_open @@ -903,6 +906,19 @@ stmt_perform : K_PERFORM expr_until_semi } ; +stmt_call : K_CALL + { + PLpgSQL_stmt_call *new; + + new = palloc0(sizeof(PLpgSQL_stmt_call)); + new->cmd_type = PLPGSQL_STMT_CALL; + new->lineno = plpgsql_location_to_lineno(@1); + new->expr = read_sql_stmt("CALL "); + + $$ = (PLpgSQL_stmt *)new; + } + ; + stmt_assign : assign_var assign_operator expr_until_semi { PLpgSQL_stmt_assign *new; @@ -2401,6 +2417,7 @@ unreserved_keyword : | K_ARRAY | K_ASSERT | K_BACKWARD + | K_CALL | K_CLOSE | K_COLLATE | K_COLUMN @@ -3129,15 +3146,6 @@ make_return_stmt(int location) errhint("Use RETURN NEXT or RETURN QUERY."), parser_errposition(yylloc))); } - else if (plpgsql_curr_compile->out_param_varno >= 0) - { - if (yylex() != ';') - ereport(ERROR, - (errcode(ERRCODE_DATATYPE_MISMATCH), - errmsg("RETURN cannot have a parameter in function with OUT parameters"), - parser_errposition(yylloc))); - new->retvarno = plpgsql_curr_compile->out_param_varno; - } else if (plpgsql_curr_compile->fn_rettype == VOIDOID) { if (yylex() != ';') @@ -3154,6 +3162,15 @@ make_return_stmt(int location) parser_errposition(yylloc))); } } + else if (plpgsql_curr_compile->out_param_varno >= 0) + { + if (yylex() != ';') + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("RETURN cannot have a parameter in function with OUT parameters"), + parser_errposition(yylloc))); + new->retvarno = plpgsql_curr_compile->out_param_varno; + } else { /* diff --git a/src/pl/plpgsql/src/pl_scanner.c b/src/pl/plpgsql/src/pl_scanner.c index 12a3e6b818f..65774f99028 100644 --- a/src/pl/plpgsql/src/pl_scanner.c +++ b/src/pl/plpgsql/src/pl_scanner.c @@ -102,6 +102,7 @@ static const ScanKeyword unreserved_keywords[] = { PG_KEYWORD("array", K_ARRAY, UNRESERVED_KEYWORD) PG_KEYWORD("assert", K_ASSERT, UNRESERVED_KEYWORD) PG_KEYWORD("backward", K_BACKWARD, UNRESERVED_KEYWORD) + PG_KEYWORD("call", K_CALL, UNRESERVED_KEYWORD) PG_KEYWORD("close", K_CLOSE, UNRESERVED_KEYWORD) PG_KEYWORD("collate", K_COLLATE, UNRESERVED_KEYWORD) PG_KEYWORD("column", K_COLUMN, UNRESERVED_KEYWORD) diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index dd59036de09..f7619a63f9b 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -125,6 +125,7 @@ typedef enum PLpgSQL_stmt_type PLPGSQL_STMT_FETCH, PLPGSQL_STMT_CLOSE, PLPGSQL_STMT_PERFORM, + PLPGSQL_STMT_CALL, PLPGSQL_STMT_COMMIT, PLPGSQL_STMT_ROLLBACK } PLpgSQL_stmt_type; @@ -509,6 +510,17 @@ typedef struct PLpgSQL_stmt_perform } PLpgSQL_stmt_perform; /* + * CALL statement + */ +typedef struct PLpgSQL_stmt_call +{ + PLpgSQL_stmt_type cmd_type; + int lineno; + PLpgSQL_expr *expr; + PLpgSQL_variable *target; +} PLpgSQL_stmt_call; + +/* * COMMIT statement */ typedef struct PLpgSQL_stmt_commit diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql index e580e5fea07..f1eed9975a1 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_call.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql @@ -55,6 +55,113 @@ CALL test_proc4(66); SELECT * FROM test1; +-- output arguments + +CREATE PROCEDURE test_proc5(INOUT a text) +LANGUAGE plpgsql +AS $$ +BEGIN + a := a || '+' || a; +END; +$$; + +CALL test_proc5('abc'); + + +CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int) +LANGUAGE plpgsql +AS $$ +BEGIN + b := b * a; + c := c * a; +END; +$$; + +CALL test_proc6(2, 3, 4); + + +DO +LANGUAGE plpgsql +$$ +DECLARE + x int := 3; + y int := 4; +BEGIN + CALL test_proc6(2, x, y); + RAISE INFO 'x = %, y = %', x, y; +END; +$$; + + +DO +LANGUAGE plpgsql +$$ +DECLARE + x int := 3; + y int := 4; +BEGIN + CALL test_proc6(2, x + 1, y); -- error + RAISE INFO 'x = %, y = %', x, y; +END; +$$; + + +DO +LANGUAGE plpgsql +$$ +DECLARE + x int := 3; + y int := 4; +BEGIN + FOR i IN 1..5 LOOP + CALL test_proc6(i, x, y); + RAISE INFO 'x = %, y = %', x, y; + END LOOP; +END; +$$; + + +-- recursive with output arguments + +CREATE PROCEDURE test_proc7(x int, INOUT a int, INOUT b numeric) +LANGUAGE plpgsql +AS $$ +BEGIN +IF x > 1 THEN + a := x / 10; + b := x / 2; + CALL test_proc7(b::int, a, b); +END IF; +END; +$$; + +CALL test_proc7(100, -1, -1); + + +-- transition variable assignment + +TRUNCATE test1; + +CREATE FUNCTION triggerfunc1() RETURNS trigger +LANGUAGE plpgsql +AS $$ +DECLARE + z int := 0; +BEGIN + CALL test_proc6(2, NEW.a, NEW.a); + RETURN NEW; +END; +$$; + +CREATE TRIGGER t1 BEFORE INSERT ON test1 EXECUTE PROCEDURE triggerfunc1(); + +INSERT INTO test1 VALUES (1), (2), (3); + +UPDATE test1 SET a = 22 WHERE a = 2; + +SELECT * FROM test1 ORDER BY a; + + DROP PROCEDURE test_proc1; DROP PROCEDURE test_proc3; DROP PROCEDURE test_proc4; |