missing support of named convention for procedures

Started by Pavel Stehulealmost 8 years ago11 messages

pavel.stehule@gmail.com

almost 8 years ago

create or replace procedure proc2(in a int, in b int)
as $$
begin
a := a * 10;
b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR: XX000: unrecognized node type: 107
LOCATION: ExecInitExprRec, execExpr.c:2114

Regards

Pavel

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Pavel Stehule (#1)

Re: missing support of named convention for procedures

2018-03-15 22:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

create or replace procedure proc2(in a int, in b int)
as $$
begin
a := a * 10;
b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR: XX000: unrecognized node type: 107
LOCATION: ExecInitExprRec, execExpr.c:2114

Defaults are not supported too:

postgres=# create or replace procedure foo1(a int, b int, c int default 10)
as $$
begin
raise notice 'a: %, b: %, c: %', a, b, c;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call foo1(10,20);
NOTICE: 00000: a: 10, b: 20, c: -778600432
LOCATION: exec_stmt_raise, pl_exec.c:3643
CALL

Show quoted text

Regards

Pavel

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Pavel Stehule (#2)

1 attachment(s)

Re: missing support of named convention for procedures

2018-03-16 8:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-03-15 22:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

create or replace procedure proc2(in a int, in b int)
as $$
begin
a := a * 10;
b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR: XX000: unrecognized node type: 107
LOCATION: ExecInitExprRec, execExpr.c:2114

Defaults are not supported too:

postgres=# create or replace procedure foo1(a int, b int, c int default
10)
as $$
begin
raise notice 'a: %, b: %, c: %', a, b, c;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call foo1(10,20);
NOTICE: 00000: a: 10, b: 20, c: -778600432
LOCATION: exec_stmt_raise, pl_exec.c:3643
CALL

attached patch fixes it

Regards

Pavel

Show quoted text

Regards

Pavel

Attachments:

plpgsql-call-named-default-args.patchtext/x-patch; charset=US-ASCII; name=plpgsql-call-named-default-args.patchDownload

diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 86fa8c0dd7..c7a44d858b 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -55,6 +55,7 @@
 #include "executor/executor.h"
 #include "miscadmin.h"
 #include "optimizer/var.h"
+#include "optimizer/clauses.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_collate.h"
 #include "parser/parse_expr.h"
@@ -2254,6 +2255,9 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 		elog(ERROR, "cache lookup failed for function %u", fexpr->funcid);
 	if (!heap_attisnull(tp, Anum_pg_proc_proconfig))
 		callcontext->atomic = true;
+
+	fexpr->args = expand_function_arguments(fexpr->args, fexpr->funcresulttype, tp);
+
 	ReleaseSysCache(tp);
 
 	/* Initialize function call structure */
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a9a09afd2b..40eae3a835 100644
--- 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 ba4fa4b68b..ed854fdd40 100644
--- 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 1e94a44f2b..9d7d7da5b0 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -169,3 +169,80 @@ DROP PROCEDURE test_proc1;
 DROP PROCEDURE test_proc3;
 DROP PROCEDURE test_proc4;
 DROP TABLE test1;
+-- named parameters and defaults
+CREATE PROCEDURE test_proc(a int, b int, c int DEFAULT -1)
+AS $$
+BEGIN
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END;
+$$ LANGUAGE plpgsql;
+CALL test_proc(10,20,30);
+NOTICE:  a: 10, b: 20, c: 30
+CALL test_proc(10,20);
+NOTICE:  a: 10, b: 20, c: -1
+CALL test_proc(c=>1, a=>3, b=>2);
+NOTICE:  a: 3, b: 2, c: 1
+DROP PROCEDURE test_proc;
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %', _a, _b;
+  _a := _a * 10;
+  _b := _b + 10;
+END;
+$$ LANGUAGE plpgsql;
+CALL test_proc1(10,20);
+NOTICE:  test_proc1: a: 10, b: 20
+ _a  | _b 
+-----+----
+ 100 | 30
+(1 row)
+
+CALL test_proc1(_b=>20, _a=>10);
+NOTICE:  test_proc1: a: 10, b: 20
+ _a  | _b 
+-----+----
+ 100 | 30
+(1 row)
+
+DO $$
+DECLARE a int; b int;
+BEGIN
+  a := 10; b := 30;
+  CALL test_proc1(a, b);
+  RAISE NOTICE 'a: %, b: %', a, b;
+  a := 10; b := 30;
+  CALL test_proc1(_b=>b, _a=>a);
+  RAISE NOTICE 'a: %, b: %', a, b;
+END
+$$;
+NOTICE:  test_proc1: a: 10, b: 30
+NOTICE:  a: 100, b: 40
+NOTICE:  test_proc1: a: 10, b: 30
+NOTICE:  a: 100, b: 40
+DROP PROCEDURE test_proc1;
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int, INOUT _c int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %, c: %', _a, _b, _c;
+  _a := _a * 10;
+  _b := _b + 10;
+  _c := _c * -10;
+END;
+$$ LANGUAGE plpgsql;
+DO $$
+DECLARE a int; b int; c int;
+BEGIN
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, b, c);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, _c=>c, _b=>b);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END
+$$;
+NOTICE:  test_proc1: a: 10, b: 30, c: 50
+NOTICE:  a: 100, b: 40, c: -500
+NOTICE:  test_proc1: a: 10, b: 30, c: 50
+NOTICE:  a: 100, b: 40, c: -500
+DROP PROCEDURE test_proc1;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 68da7cf669..e073b15d60 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2134,14 +2134,36 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
 				{
 					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)));
+					if (IsA(n,NamedArgExpr))
+					{
+						NamedArgExpr *nexpr = (NamedArgExpr *) n;
+
+						if (!IsA(nexpr->arg, 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;
+						param = castNode(Param, nexpr->arg);
+
+						/* paramid is offset by 1 (see make_datum_param()) */
+						/* should be ensured so this target varno is not used yet */
+						row->varnos[nexpr->argnumber] = param->paramid - 1;
+
+						/* named arguments must be after possition arguments, so I can increase nfields */
+						nfields++;
+					}
+					else
+					{
+						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++;
 			}
diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql
index f1eed9975a..3f21dd9ea6 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -167,3 +167,67 @@ DROP PROCEDURE test_proc3;
 DROP PROCEDURE test_proc4;
 
 DROP TABLE test1;
+
+-- named parameters and defaults
+CREATE PROCEDURE test_proc(a int, b int, c int DEFAULT -1)
+AS $$
+BEGIN
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END;
+$$ LANGUAGE plpgsql;
+
+CALL test_proc(10,20,30);
+CALL test_proc(10,20);
+CALL test_proc(c=>1, a=>3, b=>2);
+
+DROP PROCEDURE test_proc;
+
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %', _a, _b;
+  _a := _a * 10;
+  _b := _b + 10;
+END;
+$$ LANGUAGE plpgsql;
+
+CALL test_proc1(10,20);
+CALL test_proc1(_b=>20, _a=>10);
+
+DO $$
+DECLARE a int; b int;
+BEGIN
+  a := 10; b := 30;
+  CALL test_proc1(a, b);
+  RAISE NOTICE 'a: %, b: %', a, b;
+  a := 10; b := 30;
+  CALL test_proc1(_b=>b, _a=>a);
+  RAISE NOTICE 'a: %, b: %', a, b;
+END
+$$;
+
+DROP PROCEDURE test_proc1;
+
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int, INOUT _c int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %, c: %', _a, _b, _c;
+  _a := _a * 10;
+  _b := _b + 10;
+  _c := _c * -10;
+END;
+$$ LANGUAGE plpgsql;
+
+DO $$
+DECLARE a int; b int; c int;
+BEGIN
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, b, c);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, _c=>c, _b=>b);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END
+$$;
+
+DROP PROCEDURE test_proc1;

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Pavel Stehule (#3)

Re: missing support of named convention for procedures

2018-03-16 11:29 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-03-16 8:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-03-15 22:13 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

create or replace procedure proc2(in a int, in b int)
as $$
begin
a := a * 10;
b := b * 10;
end;
$$ language plpgsql;

postgres=# call proc2(a => 10,b => 20);
ERROR: XX000: unrecognized node type: 107
LOCATION: ExecInitExprRec, execExpr.c:2114

Defaults are not supported too:

postgres=# create or replace procedure foo1(a int, b int, c int default
10)
as $$
begin
raise notice 'a: %, b: %, c: %', a, b, c;
end;
$$ language plpgsql;
CREATE PROCEDURE
postgres=# call foo1(10,20);
NOTICE: 00000: a: 10, b: 20, c: -778600432
LOCATION: exec_stmt_raise, pl_exec.c:3643
CALL

attached patch fixes it

Regards

Pavel

variadic parameters are supported too.

Show quoted text

Regards

Pavel

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Pavel Stehule (#3)

Re: missing support of named convention for procedures

On 3/16/18 06:29, Pavel Stehule wrote:

attached patch fixes it

The fix doesn't seem to work for LANGUAGE SQL procedures. For example:

CREATE PROCEDURE ptest5(a int, b int DEFAULT 0)
LANGUAGE SQL
AS $$
INSERT INTO cp_test VALUES (a, 'foo');
INSERT INTO cp_test VALUES (b, 'bar');
$$;
CALL ptest5(a => 1, b => 2); -- ok
CALL ptest5(b => 3, a => 4); -- ok
CALL ptest5(5);
ERROR: no value found for parameter 2
CONTEXT: SQL function "ptest5" statement 2
CALL ptest5(a => 6);
ERROR: no value found for parameter 2
CONTEXT: SQL function "ptest5" statement 2

I'm not quite sure why this behaves differently from plpgsql. Needs
more digging.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Peter Eisentraut (#5)

Re: missing support of named convention for procedures

2018-03-20 17:31 GMT+01:00 Peter Eisentraut <
peter.eisentraut@2ndquadrant.com>:

On 3/16/18 06:29, Pavel Stehule wrote:

attached patch fixes it

The fix doesn't seem to work for LANGUAGE SQL procedures. For example:

CREATE PROCEDURE ptest5(a int, b int DEFAULT 0)
LANGUAGE SQL
AS $$
INSERT INTO cp_test VALUES (a, 'foo');
INSERT INTO cp_test VALUES (b, 'bar');
$$;
CALL ptest5(a => 1, b => 2); -- ok
CALL ptest5(b => 3, a => 4); -- ok
CALL ptest5(5);
ERROR: no value found for parameter 2
CONTEXT: SQL function "ptest5" statement 2
CALL ptest5(a => 6);
ERROR: no value found for parameter 2
CONTEXT: SQL function "ptest5" statement 2

I'm not quite sure why this behaves differently from plpgsql. Needs
more digging.

Do you working on this issue? Maybe tomorrow I'll have a time to look there.

Regards

Pavel

Show quoted text

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Pavel Stehule (#6)

1 attachment(s)

Re: missing support of named convention for procedures

2018-03-21 8:18 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2018-03-20 17:31 GMT+01:00 Peter Eisentraut <peter.eisentraut@2ndquadrant.
com>:

On 3/16/18 06:29, Pavel Stehule wrote:

attached patch fixes it

The fix doesn't seem to work for LANGUAGE SQL procedures. For example:

CREATE PROCEDURE ptest5(a int, b int DEFAULT 0)
LANGUAGE SQL
AS $$
INSERT INTO cp_test VALUES (a, 'foo');
INSERT INTO cp_test VALUES (b, 'bar');
$$;
CALL ptest5(a => 1, b => 2); -- ok
CALL ptest5(b => 3, a => 4); -- ok
CALL ptest5(5);
ERROR: no value found for parameter 2
CONTEXT: SQL function "ptest5" statement 2
CALL ptest5(a => 6);
ERROR: no value found for parameter 2
CONTEXT: SQL function "ptest5" statement 2

I'm not quite sure why this behaves differently from plpgsql. Needs
more digging.

Do you working on this issue? Maybe tomorrow I'll have a time to look
there.

attached patch should to fix it

Regards

Pavel

Show quoted text

Regards

Pavel

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

call-named-default-args.patchtext/x-patch; charset=US-ASCII; name=call-named-default-args.patchDownload

diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index 86fa8c0dd7..75797338f4 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -55,6 +55,7 @@
 #include "executor/executor.h"
 #include "miscadmin.h"
 #include "optimizer/var.h"
+#include "optimizer/clauses.h"
 #include "parser/parse_coerce.h"
 #include "parser/parse_collate.h"
 #include "parser/parse_expr.h"
@@ -2254,6 +2255,15 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
 		elog(ERROR, "cache lookup failed for function %u", fexpr->funcid);
 	if (!heap_attisnull(tp, Anum_pg_proc_proconfig))
 		callcontext->atomic = true;
+
+	/*
+	 * The named and default arguments can be used, so try to reorder
+	 * arguments and append default arguments. The number of arguments
+	 * can be changed, refresh nargs.
+	 */
+	fexpr->args = expand_function_arguments(fexpr->args, fexpr->funcresulttype, tp);
+	nargs = list_length(fexpr->args);
+
 	ReleaseSysCache(tp);
 
 	/* Initialize function call structure */
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 93e5658a35..a1c7c45fae 100644
--- 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 ba4fa4b68b..ed854fdd40 100644
--- 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 1e94a44f2b..9d7d7da5b0 100644
--- a/src/pl/plpgsql/src/expected/plpgsql_call.out
+++ b/src/pl/plpgsql/src/expected/plpgsql_call.out
@@ -169,3 +169,80 @@ DROP PROCEDURE test_proc1;
 DROP PROCEDURE test_proc3;
 DROP PROCEDURE test_proc4;
 DROP TABLE test1;
+-- named parameters and defaults
+CREATE PROCEDURE test_proc(a int, b int, c int DEFAULT -1)
+AS $$
+BEGIN
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END;
+$$ LANGUAGE plpgsql;
+CALL test_proc(10,20,30);
+NOTICE:  a: 10, b: 20, c: 30
+CALL test_proc(10,20);
+NOTICE:  a: 10, b: 20, c: -1
+CALL test_proc(c=>1, a=>3, b=>2);
+NOTICE:  a: 3, b: 2, c: 1
+DROP PROCEDURE test_proc;
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %', _a, _b;
+  _a := _a * 10;
+  _b := _b + 10;
+END;
+$$ LANGUAGE plpgsql;
+CALL test_proc1(10,20);
+NOTICE:  test_proc1: a: 10, b: 20
+ _a  | _b 
+-----+----
+ 100 | 30
+(1 row)
+
+CALL test_proc1(_b=>20, _a=>10);
+NOTICE:  test_proc1: a: 10, b: 20
+ _a  | _b 
+-----+----
+ 100 | 30
+(1 row)
+
+DO $$
+DECLARE a int; b int;
+BEGIN
+  a := 10; b := 30;
+  CALL test_proc1(a, b);
+  RAISE NOTICE 'a: %, b: %', a, b;
+  a := 10; b := 30;
+  CALL test_proc1(_b=>b, _a=>a);
+  RAISE NOTICE 'a: %, b: %', a, b;
+END
+$$;
+NOTICE:  test_proc1: a: 10, b: 30
+NOTICE:  a: 100, b: 40
+NOTICE:  test_proc1: a: 10, b: 30
+NOTICE:  a: 100, b: 40
+DROP PROCEDURE test_proc1;
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int, INOUT _c int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %, c: %', _a, _b, _c;
+  _a := _a * 10;
+  _b := _b + 10;
+  _c := _c * -10;
+END;
+$$ LANGUAGE plpgsql;
+DO $$
+DECLARE a int; b int; c int;
+BEGIN
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, b, c);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, _c=>c, _b=>b);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END
+$$;
+NOTICE:  test_proc1: a: 10, b: 30, c: 50
+NOTICE:  a: 100, b: 40, c: -500
+NOTICE:  test_proc1: a: 10, b: 30, c: 50
+NOTICE:  a: 100, b: 40, c: -500
+DROP PROCEDURE test_proc1;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index 38ea7a091f..36c5b68a05 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2134,14 +2134,36 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
 				{
 					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)));
+					if (IsA(n,NamedArgExpr))
+					{
+						NamedArgExpr *nexpr = (NamedArgExpr *) n;
+
+						if (!IsA(nexpr->arg, 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;
+						param = castNode(Param, nexpr->arg);
+
+						/* paramid is offset by 1 (see make_datum_param()) */
+						/* should be ensured so this target varno is not used yet */
+						row->varnos[nexpr->argnumber] = param->paramid - 1;
+
+						/* named arguments must be after possition arguments, so I can increase nfields */
+						nfields++;
+					}
+					else
+					{
+						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++;
 			}
diff --git a/src/pl/plpgsql/src/sql/plpgsql_call.sql b/src/pl/plpgsql/src/sql/plpgsql_call.sql
index f1eed9975a..3f21dd9ea6 100644
--- a/src/pl/plpgsql/src/sql/plpgsql_call.sql
+++ b/src/pl/plpgsql/src/sql/plpgsql_call.sql
@@ -167,3 +167,67 @@ DROP PROCEDURE test_proc3;
 DROP PROCEDURE test_proc4;
 
 DROP TABLE test1;
+
+-- named parameters and defaults
+CREATE PROCEDURE test_proc(a int, b int, c int DEFAULT -1)
+AS $$
+BEGIN
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END;
+$$ LANGUAGE plpgsql;
+
+CALL test_proc(10,20,30);
+CALL test_proc(10,20);
+CALL test_proc(c=>1, a=>3, b=>2);
+
+DROP PROCEDURE test_proc;
+
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %', _a, _b;
+  _a := _a * 10;
+  _b := _b + 10;
+END;
+$$ LANGUAGE plpgsql;
+
+CALL test_proc1(10,20);
+CALL test_proc1(_b=>20, _a=>10);
+
+DO $$
+DECLARE a int; b int;
+BEGIN
+  a := 10; b := 30;
+  CALL test_proc1(a, b);
+  RAISE NOTICE 'a: %, b: %', a, b;
+  a := 10; b := 30;
+  CALL test_proc1(_b=>b, _a=>a);
+  RAISE NOTICE 'a: %, b: %', a, b;
+END
+$$;
+
+DROP PROCEDURE test_proc1;
+
+CREATE PROCEDURE test_proc1(INOUT _a int, INOUT _b int, INOUT _c int)
+AS $$
+BEGIN
+  RAISE NOTICE 'test_proc1: a: %, b: %, c: %', _a, _b, _c;
+  _a := _a * 10;
+  _b := _b + 10;
+  _c := _c * -10;
+END;
+$$ LANGUAGE plpgsql;
+
+DO $$
+DECLARE a int; b int; c int;
+BEGIN
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, b, c);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+  a := 10; b := 30; c := 50;
+  CALL test_proc1(a, _c=>c, _b=>b);
+  RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
+END
+$$;
+
+DROP PROCEDURE test_proc1;
diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out
index dacb657706..042cf2e799 100644
--- a/src/test/regress/expected/create_procedure.out
+++ b/src/test/regress/expected/create_procedure.out
@@ -146,8 +146,20 @@ ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1;
 ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
 ALTER ROUTINE ptest1a RENAME TO ptest1;
 DROP ROUTINE cp_testfunc1(int);
+-- named and default parameters
+CREATE OR REPLACE PROCEDURE ptest4(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);
+$$;
+CALL ptest4(10, 'Hello', 20);
+CALL ptest4(10, 'Hello');
+CALL ptest4(10, b => 'Hello');
+CALL ptest4(b => 'Hello', a => 10);
 -- cleanup
 DROP PROCEDURE ptest1;
 DROP PROCEDURE ptest2;
+DROP PROCEDURE ptest3;
+DROP PROCEDURE ptest4;
 DROP TABLE cp_test;
 DROP USER regress_cp_user1;
diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql
index a6a935f578..5ecfda5e52 100644
--- a/src/test/regress/sql/create_procedure.sql
+++ b/src/test/regress/sql/create_procedure.sql
@@ -108,11 +108,24 @@ ALTER ROUTINE ptest1a RENAME TO ptest1;
 
 DROP ROUTINE cp_testfunc1(int);
 
+-- named and default parameters
+CREATE OR REPLACE PROCEDURE ptest4(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);
+$$;
+
+CALL ptest4(10, 'Hello', 20);
+CALL ptest4(10, 'Hello');
+CALL ptest4(10, b => 'Hello');
+CALL ptest4(b => 'Hello', a => 10);
 
 -- cleanup
 
 DROP PROCEDURE ptest1;
 DROP PROCEDURE ptest2;
+DROP PROCEDURE ptest3;
+DROP PROCEDURE ptest4;
 
 DROP TABLE cp_test;

Andres Freund

andres@anarazel.de

almost 8 years ago

In reply to: Pavel Stehule (#7)

Re: missing support of named convention for procedures

Hi Peter, Pavel,

On 2018-03-22 15:19:12 +0100, Pavel Stehule wrote:

attached patch should to fix it

This is still broken, and has been an open item for a bit. Peter, Could
you check whether Pavel's fix resolves the issue for you?

Regards,

Andres

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Andres Freund (#8)

Re: missing support of named convention for procedures

On 4/11/18 12:06, Andres Freund wrote:

On 2018-03-22 15:19:12 +0100, Pavel Stehule wrote:

attached patch should to fix it

This is still broken, and has been an open item for a bit. Peter, Could
you check whether Pavel's fix resolves the issue for you?

Yes, I will work on this.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#10

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Pavel Stehule (#7)

Re: missing support of named convention for procedures

On 3/22/18 10:19, Pavel Stehule wrote:

attached patch should to fix it

Committed.

I had to make a small tweak to make INOUT + DEFAULT parameters work in
PL/pgSQL.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#11

Pavel Stehule

pavel.stehule@gmail.com

almost 8 years ago

In reply to: Peter Eisentraut (#10)

Re: missing support of named convention for procedures

2018-04-14 15:58 GMT+02:00 Peter Eisentraut <
peter.eisentraut@2ndquadrant.com>:

On 3/22/18 10:19, Pavel Stehule wrote:

attached patch should to fix it

Committed.

Thank you

Pavel

Show quoted text

I had to make a small tweak to make INOUT + DEFAULT parameters work in
PL/pgSQL.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services