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 <pavel.stehule@gmail.com>

diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 80cbbf94b47c0f97354a81bd4d570ee65d544b51..3c74873eeb6984b7a5d109dedf6263a10738f33b 100644 (file)
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -52,6 +52,7 @@
 #include "executor/execdesc.h"
 #include "executor/executor.h"
 #include "miscadmin.h"
+#include "optimizer/clauses.h"
 #include "optimizer/var.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_collate.h"
@@ -2226,34 +2227,40 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
    if (aclresult != ACLCHECK_OK)
        aclcheck_error(aclresult, OBJECT_PROCEDURE, get_func_name(fexpr->funcid));
 
-   nargs = list_length(fexpr->args);
-
-   /* safety check; see ExecInitFunc() */
-   if (nargs > FUNC_MAX_ARGS)
-       ereport(ERROR,
-               (errcode(ERRCODE_TOO_MANY_ARGUMENTS),
-                errmsg_plural("cannot pass more than %d argument to a procedure",
-                              "cannot pass more than %d arguments to a procedure",
-                              FUNC_MAX_ARGS,
-                              FUNC_MAX_ARGS)));
-
    /* Prep the context object we'll pass to the procedure */
    callcontext = makeNode(CallContext);
    callcontext->atomic = atomic;
 
+   tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(fexpr->funcid));
+   if (!HeapTupleIsValid(tp))
+       elog(ERROR, "cache lookup failed for function %u", fexpr->funcid);
+
    /*
     * If proconfig is set we can't allow transaction commands because of the
     * way the GUC stacking works: The transaction boundary would have to pop
     * the proconfig setting off the stack.  That restriction could be lifted
     * by redesigning the GUC nesting mechanism a bit.
     */
-   tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(fexpr->funcid));
-   if (!HeapTupleIsValid(tp))
-       elog(ERROR, "cache lookup failed for function %u", fexpr->funcid);
    if (!heap_attisnull(tp, Anum_pg_proc_proconfig, NULL))
        callcontext->atomic = true;
+
+   /*
+    * Expand named arguments, defaults, etc.
+    */
+   fexpr->args = expand_function_arguments(fexpr->args, fexpr->funcresulttype, tp);
+   nargs = list_length(fexpr->args);
+
    ReleaseSysCache(tp);
 
+   /* safety check; see ExecInitFunc() */
+   if (nargs > FUNC_MAX_ARGS)
+       ereport(ERROR,
+               (errcode(ERRCODE_TOO_MANY_ARGUMENTS),
+                errmsg_plural("cannot pass more than %d argument to a procedure",
+                              "cannot pass more than %d arguments to a procedure",
+                              FUNC_MAX_ARGS,
+                              FUNC_MAX_ARGS)));
+
    /* Initialize function call structure */
    InvokeFunctionExecuteHook(fexpr->funcid);
    fmgr_info(fexpr->funcid, &flinfo);

diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index ed6b680ed8601fe18d6cc7eed4362cc9dc331c38..505ae0af85db78e7d8390e6f62057a53dd43b6db 100644 (file)
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -130,8 +130,6 @@ static Expr *simplify_function(Oid funcid,
                  Oid result_collid, Oid input_collid, List **args_p,
                  bool funcvariadic, bool process_args, bool allow_non_const,
                  eval_const_expressions_context *context);
-static List *expand_function_arguments(List *args, Oid result_type,
-                         HeapTuple func_tuple);
 static List *reorder_function_arguments(List *args, HeapTuple func_tuple);
 static List *add_function_defaults(List *args, HeapTuple func_tuple);
 static List *fetch_function_defaults(HeapTuple func_tuple);
@@ -4112,7 +4110,7 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
  * cases it handles should never occur there.  This should be OK since it
  * will fall through very quickly if there's nothing to do.
  */
-static List *
+List *
 expand_function_arguments(List *args, Oid result_type, HeapTuple func_tuple)
 {
    Form_pg_proc funcform = (Form_pg_proc) GETSTRUCT(func_tuple);

diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index ba4fa4b68b3fa22224d9e4ca5d81a30c9d1c9d8c..ed854fdd405b0aee265971a0802974277b48249d 100644 (file)
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -14,9 +14,9 @@
 #ifndef CLAUSES_H
 #define CLAUSES_H
 
+#include "access/htup.h"
 #include "nodes/relation.h"
 
-
 #define is_opclause(clause)        ((clause) != NULL && IsA(clause, OpExpr))
 #define is_funcclause(clause)  ((clause) != NULL && IsA(clause, FuncExpr))
 
@@ -85,4 +85,7 @@ extern Node *estimate_expression_value(PlannerInfo *root, Node *node);
 extern Query *inline_set_returning_function(PlannerInfo *root,
                              RangeTblEntry *rte);
 
+extern List *expand_function_arguments(List *args, Oid result_type,
+                         HeapTuple func_tuple);
+
 #endif                         /* CLAUSES_H */

diff --git a/src/pl/plpgsql/src/expected/plpgsql_call.out b/src/pl/plpgsql/src/expected/plpgsql_call.out
index ab9d3bbc701fe41f9b9c8f4b750a127b11ea7c2d..a3592d7b821e914d2f4c5b1f3a6b1dbde3b82b99 100644 (file)
--- 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 99f167a0a8c14c57ce19e9463e3fd57048773416..ae1898ec184f6f3a2372b6695580273e52f70f1c 100644 (file)
--- 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 551bb77c7034193acbaf354370cfa8957a1d823d..a0b7bcb6e7c945968a87f9e60289f1da6f77aaea 100644 (file)
--- 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;

diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index 66cdad760ca6552242f50fff609079b61b6cb9f0..67d671727c7708eb9551c50e1231f3b0eb2d26fa 100644 (file)
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -91,6 +91,31 @@ $$;
 ERROR:  calling procedures with output arguments is not supported in SQL functions
 CONTEXT:  SQL function "ptest4b"
 DROP PROCEDURE ptest4a;
+-- named and default parameters
+CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100)
+LANGUAGE SQL
+AS $$
+INSERT INTO cp_test VALUES(a, b);
+INSERT INTO cp_test VALUES(c, b);
+$$;
+TRUNCATE cp_test;
+CALL ptest5(10, 'Hello', 20);
+CALL ptest5(10, 'Hello');
+CALL ptest5(10, b => 'Hello');
+CALL ptest5(b => 'Hello', a => 10);
+SELECT * FROM cp_test;
+  a  |   b   
+-----+-------
+  10 | Hello
+  20 | Hello
+  10 | Hello
+ 100 | Hello
+  10 | Hello
+ 100 | Hello
+  10 | Hello
+ 100 | Hello
+(8 rows)
+
 -- various error cases
 CALL version();  -- error: not a procedure
 ERROR:  version() is not a procedure

diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index 1be9c6fd78f52db291bcb7f8a697996d61c65901..22cc497ebeeaa30cfd99b5a591925425bf3c7d13 100644 (file)
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -65,6 +65,25 @@ $$;
 DROP PROCEDURE ptest4a;
 
 
+-- named and default parameters
+
+CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100)
+LANGUAGE SQL
+AS $$
+INSERT INTO cp_test VALUES(a, b);
+INSERT INTO cp_test VALUES(c, b);
+$$;
+
+TRUNCATE cp_test;
+
+CALL ptest5(10, 'Hello', 20);
+CALL ptest5(10, 'Hello');
+CALL ptest5(10, b => 'Hello');
+CALL ptest5(b => 'Hello', a => 10);
+
+SELECT * FROM cp_test;
+
+
 -- various error cases
 
 CALL version();  -- error: not a procedure