From a8677e3ff6bb8ef78a9ba676faa647bba237b1c4 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 13 Apr 2018 17:06:28 -0400 Subject: Support named and default arguments in CALL We need to call expand_function_arguments() to expand named and default arguments. In PL/pgSQL, we also need to deal with named and default INOUT arguments when receiving the output values into variables. Author: Pavel Stehule --- src/pl/plpgsql/src/expected/plpgsql_call.out | 87 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/pl_exec.c | 37 ++++++++---- src/pl/plpgsql/src/sql/plpgsql_call.sql | 74 +++++++++++++++++++++++ 3 files changed, 188 insertions(+), 10 deletions(-) (limited to 'src/pl') diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out index ab9d3bbc701..a3592d7b821 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_call.out +++ b/src/pl/plpgsql/src/expected/plpgsql_call.out @@ -152,6 +152,93 @@ CALL test_proc7(100, -1, -1); 0 | 1 (1 row) +-- named parameters and defaults +CREATE PROCEDURE test_proc8a(INOUT a int, INOUT b int) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'a: %, b: %', a, b; + a := a * 10; + b := b + 10; +END; +$$; +CALL test_proc8a(10, 20); +NOTICE: a: 10, b: 20 + a | b +-----+---- + 100 | 30 +(1 row) + +CALL test_proc8a(b => 20, a => 10); +NOTICE: a: 10, b: 20 + a | b +-----+---- + 100 | 30 +(1 row) + +DO $$ +DECLARE _a int; _b int; +BEGIN + _a := 10; _b := 30; + CALL test_proc8a(_a, _b); + RAISE NOTICE '_a: %, _b: %', _a, _b; + CALL test_proc8a(b => _b, a => _a); + RAISE NOTICE '_a: %, _b: %', _a, _b; +END +$$; +NOTICE: a: 10, b: 30 +NOTICE: _a: 100, _b: 40 +NOTICE: a: 100, b: 40 +NOTICE: _a: 1000, _b: 50 +CREATE PROCEDURE test_proc8b(INOUT a int, INOUT b int, INOUT c int) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'a: %, b: %, c: %', a, b, c; + a := a * 10; + b := b + 10; + c := c * -10; +END; +$$; +DO $$ +DECLARE _a int; _b int; _c int; +BEGIN + _a := 10; _b := 30; _c := 50; + CALL test_proc8b(_a, _b, _c); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; + CALL test_proc8b(_a, c => _c, b => _b); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; +END +$$; +NOTICE: a: 10, b: 30, c: 50 +NOTICE: _a: 100, _b: 40, _c: -500 +NOTICE: a: 100, b: 40, c: -500 +NOTICE: _a: 1000, _b: 50, _c: 5000 +CREATE PROCEDURE test_proc8c(INOUT a int, INOUT b int, INOUT c int DEFAULT 11) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'a: %, b: %, c: %', a, b, c; + a := a * 10; + b := b + 10; + c := c * -10; +END; +$$; +DO $$ +DECLARE _a int; _b int; _c int; +BEGIN + _a := 10; _b := 30; _c := 50; + CALL test_proc8c(_a, _b); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; + _a := 10; _b := 30; _c := 50; + CALL test_proc8c(_a, b => _b); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; +END +$$; +NOTICE: a: 10, b: 30, c: 11 +NOTICE: _a: 100, _b: 40, _c: 50 +NOTICE: a: 10, b: 30, c: 11 +NOTICE: _a: 100, _b: 40, _c: 50 -- transition variable assignment TRUNCATE test1; CREATE FUNCTION triggerfunc1() RETURNS trigger diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 99f167a0a8c..ae1898ec184 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -2146,7 +2146,6 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt) FuncExpr *funcexpr; int i; HeapTuple tuple; - int numargs PG_USED_FOR_ASSERTS_ONLY; Oid *argtypes; char **argnames; char *argmodes; @@ -2169,11 +2168,9 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt) 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); + get_func_arg_info(tuple, &argtypes, &argnames, &argmodes); ReleaseSysCache(tuple); - Assert(numargs == list_length(funcexpr->args)); - /* * Construct row */ @@ -2192,16 +2189,36 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt) if (argmodes && argmodes[i] == PROARGMODE_INOUT) { - Param *param; + if (IsA(n, Param)) + { + Param *param = castNode(Param, n); + + /* paramid is offset by 1 (see make_datum_param()) */ + row->varnos[nfields++] = param->paramid - 1; + } + else if (IsA(n, NamedArgExpr)) + { + NamedArgExpr *nexpr = castNode(NamedArgExpr, n); + Param *param; + + if (!IsA(nexpr->arg, Param)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("argument %d is an output argument but is not writable", i + 1))); - if (!IsA(n, Param)) + param = castNode(Param, nexpr->arg); + + /* + * Named arguments must be after positional arguments, + * so we can increase nfields. + */ + row->varnos[nexpr->argnumber] = param->paramid - 1; + nfields++; + } + else 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++; } diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql index 551bb77c703..a0b7bcb6e7c 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_call.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql @@ -142,6 +142,80 @@ $$; CALL test_proc7(100, -1, -1); +-- named parameters and defaults + +CREATE PROCEDURE test_proc8a(INOUT a int, INOUT b int) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'a: %, b: %', a, b; + a := a * 10; + b := b + 10; +END; +$$; + +CALL test_proc8a(10, 20); +CALL test_proc8a(b => 20, a => 10); + +DO $$ +DECLARE _a int; _b int; +BEGIN + _a := 10; _b := 30; + CALL test_proc8a(_a, _b); + RAISE NOTICE '_a: %, _b: %', _a, _b; + CALL test_proc8a(b => _b, a => _a); + RAISE NOTICE '_a: %, _b: %', _a, _b; +END +$$; + + +CREATE PROCEDURE test_proc8b(INOUT a int, INOUT b int, INOUT c int) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'a: %, b: %, c: %', a, b, c; + a := a * 10; + b := b + 10; + c := c * -10; +END; +$$; + +DO $$ +DECLARE _a int; _b int; _c int; +BEGIN + _a := 10; _b := 30; _c := 50; + CALL test_proc8b(_a, _b, _c); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; + CALL test_proc8b(_a, c => _c, b => _b); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; +END +$$; + + +CREATE PROCEDURE test_proc8c(INOUT a int, INOUT b int, INOUT c int DEFAULT 11) +LANGUAGE plpgsql +AS $$ +BEGIN + RAISE NOTICE 'a: %, b: %, c: %', a, b, c; + a := a * 10; + b := b + 10; + c := c * -10; +END; +$$; + +DO $$ +DECLARE _a int; _b int; _c int; +BEGIN + _a := 10; _b := 30; _c := 50; + CALL test_proc8c(_a, _b); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; + _a := 10; _b := 30; _c := 50; + CALL test_proc8c(_a, b => _b); + RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c; +END +$$; + + -- transition variable assignment TRUNCATE test1; -- cgit v1.2.3