Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita8677e3

Browse files
committed
Support named and default arguments in CALL
We need to call expand_function_arguments() to expand named and defaultarguments.In PL/pgSQL, we also need to deal with named and default INOUT argumentswhen receiving the output values into variables.Author: Pavel Stehule <pavel.stehule@gmail.com>
1 parent7c44c46 commita8677e3

File tree

8 files changed

+258
-28
lines changed

8 files changed

+258
-28
lines changed

‎src/backend/commands/functioncmds.c

Lines changed: 21 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -52,6 +52,7 @@
5252
#include"executor/execdesc.h"
5353
#include"executor/executor.h"
5454
#include"miscadmin.h"
55+
#include"optimizer/clauses.h"
5556
#include"optimizer/var.h"
5657
#include"parser/parse_coerce.h"
5758
#include"parser/parse_collate.h"
@@ -2226,34 +2227,40 @@ ExecuteCallStmt(CallStmt *stmt, ParamListInfo params, bool atomic, DestReceiver
22262227
if (aclresult!=ACLCHECK_OK)
22272228
aclcheck_error(aclresult,OBJECT_PROCEDURE,get_func_name(fexpr->funcid));
22282229

2229-
nargs=list_length(fexpr->args);
2230-
2231-
/* safety check; see ExecInitFunc() */
2232-
if (nargs>FUNC_MAX_ARGS)
2233-
ereport(ERROR,
2234-
(errcode(ERRCODE_TOO_MANY_ARGUMENTS),
2235-
errmsg_plural("cannot pass more than %d argument to a procedure",
2236-
"cannot pass more than %d arguments to a procedure",
2237-
FUNC_MAX_ARGS,
2238-
FUNC_MAX_ARGS)));
2239-
22402230
/* Prep the context object we'll pass to the procedure */
22412231
callcontext=makeNode(CallContext);
22422232
callcontext->atomic=atomic;
22432233

2234+
tp=SearchSysCache1(PROCOID,ObjectIdGetDatum(fexpr->funcid));
2235+
if (!HeapTupleIsValid(tp))
2236+
elog(ERROR,"cache lookup failed for function %u",fexpr->funcid);
2237+
22442238
/*
22452239
* If proconfig is set we can't allow transaction commands because of the
22462240
* way the GUC stacking works: The transaction boundary would have to pop
22472241
* the proconfig setting off the stack. That restriction could be lifted
22482242
* by redesigning the GUC nesting mechanism a bit.
22492243
*/
2250-
tp=SearchSysCache1(PROCOID,ObjectIdGetDatum(fexpr->funcid));
2251-
if (!HeapTupleIsValid(tp))
2252-
elog(ERROR,"cache lookup failed for function %u",fexpr->funcid);
22532244
if (!heap_attisnull(tp,Anum_pg_proc_proconfig,NULL))
22542245
callcontext->atomic= true;
2246+
2247+
/*
2248+
* Expand named arguments, defaults, etc.
2249+
*/
2250+
fexpr->args=expand_function_arguments(fexpr->args,fexpr->funcresulttype,tp);
2251+
nargs=list_length(fexpr->args);
2252+
22552253
ReleaseSysCache(tp);
22562254

2255+
/* safety check; see ExecInitFunc() */
2256+
if (nargs>FUNC_MAX_ARGS)
2257+
ereport(ERROR,
2258+
(errcode(ERRCODE_TOO_MANY_ARGUMENTS),
2259+
errmsg_plural("cannot pass more than %d argument to a procedure",
2260+
"cannot pass more than %d arguments to a procedure",
2261+
FUNC_MAX_ARGS,
2262+
FUNC_MAX_ARGS)));
2263+
22572264
/* Initialize function call structure */
22582265
InvokeFunctionExecuteHook(fexpr->funcid);
22592266
fmgr_info(fexpr->funcid,&flinfo);

‎src/backend/optimizer/util/clauses.c

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -130,8 +130,6 @@ static Expr *simplify_function(Oid funcid,
130130
Oidresult_collid,Oidinput_collid,List**args_p,
131131
boolfuncvariadic,boolprocess_args,boolallow_non_const,
132132
eval_const_expressions_context*context);
133-
staticList*expand_function_arguments(List*args,Oidresult_type,
134-
HeapTuplefunc_tuple);
135133
staticList*reorder_function_arguments(List*args,HeapTuplefunc_tuple);
136134
staticList*add_function_defaults(List*args,HeapTuplefunc_tuple);
137135
staticList*fetch_function_defaults(HeapTuplefunc_tuple);
@@ -4112,7 +4110,7 @@ simplify_function(Oid funcid, Oid result_type, int32 result_typmod,
41124110
* cases it handles should never occur there. This should be OK since it
41134111
* will fall through very quickly if there's nothing to do.
41144112
*/
4115-
staticList*
4113+
List*
41164114
expand_function_arguments(List*args,Oidresult_type,HeapTuplefunc_tuple)
41174115
{
41184116
Form_pg_procfuncform= (Form_pg_proc)GETSTRUCT(func_tuple);

‎src/include/optimizer/clauses.h

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,9 +14,9 @@
1414
#ifndefCLAUSES_H
1515
#defineCLAUSES_H
1616

17+
#include"access/htup.h"
1718
#include"nodes/relation.h"
1819

19-
2020
#defineis_opclause(clause)((clause) != NULL && IsA(clause, OpExpr))
2121
#defineis_funcclause(clause)((clause) != NULL && IsA(clause, FuncExpr))
2222

@@ -85,4 +85,7 @@ extern Node *estimate_expression_value(PlannerInfo *root, Node *node);
8585
externQuery*inline_set_returning_function(PlannerInfo*root,
8686
RangeTblEntry*rte);
8787

88+
externList*expand_function_arguments(List*args,Oidresult_type,
89+
HeapTuplefunc_tuple);
90+
8891
#endif/* CLAUSES_H */

‎src/pl/plpgsql/src/expected/plpgsql_call.out

Lines changed: 87 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -152,6 +152,93 @@ CALL test_proc7(100, -1, -1);
152152
0 | 1
153153
(1 row)
154154

155+
-- named parameters and defaults
156+
CREATE PROCEDURE test_proc8a(INOUT a int, INOUT b int)
157+
LANGUAGE plpgsql
158+
AS $$
159+
BEGIN
160+
RAISE NOTICE 'a: %, b: %', a, b;
161+
a := a * 10;
162+
b := b + 10;
163+
END;
164+
$$;
165+
CALL test_proc8a(10, 20);
166+
NOTICE: a: 10, b: 20
167+
a | b
168+
-----+----
169+
100 | 30
170+
(1 row)
171+
172+
CALL test_proc8a(b => 20, a => 10);
173+
NOTICE: a: 10, b: 20
174+
a | b
175+
-----+----
176+
100 | 30
177+
(1 row)
178+
179+
DO $$
180+
DECLARE _a int; _b int;
181+
BEGIN
182+
_a := 10; _b := 30;
183+
CALL test_proc8a(_a, _b);
184+
RAISE NOTICE '_a: %, _b: %', _a, _b;
185+
CALL test_proc8a(b => _b, a => _a);
186+
RAISE NOTICE '_a: %, _b: %', _a, _b;
187+
END
188+
$$;
189+
NOTICE: a: 10, b: 30
190+
NOTICE: _a: 100, _b: 40
191+
NOTICE: a: 100, b: 40
192+
NOTICE: _a: 1000, _b: 50
193+
CREATE PROCEDURE test_proc8b(INOUT a int, INOUT b int, INOUT c int)
194+
LANGUAGE plpgsql
195+
AS $$
196+
BEGIN
197+
RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
198+
a := a * 10;
199+
b := b + 10;
200+
c := c * -10;
201+
END;
202+
$$;
203+
DO $$
204+
DECLARE _a int; _b int; _c int;
205+
BEGIN
206+
_a := 10; _b := 30; _c := 50;
207+
CALL test_proc8b(_a, _b, _c);
208+
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
209+
CALL test_proc8b(_a, c => _c, b => _b);
210+
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
211+
END
212+
$$;
213+
NOTICE: a: 10, b: 30, c: 50
214+
NOTICE: _a: 100, _b: 40, _c: -500
215+
NOTICE: a: 100, b: 40, c: -500
216+
NOTICE: _a: 1000, _b: 50, _c: 5000
217+
CREATE PROCEDURE test_proc8c(INOUT a int, INOUT b int, INOUT c int DEFAULT 11)
218+
LANGUAGE plpgsql
219+
AS $$
220+
BEGIN
221+
RAISE NOTICE 'a: %, b: %, c: %', a, b, c;
222+
a := a * 10;
223+
b := b + 10;
224+
c := c * -10;
225+
END;
226+
$$;
227+
DO $$
228+
DECLARE _a int; _b int; _c int;
229+
BEGIN
230+
_a := 10; _b := 30; _c := 50;
231+
CALL test_proc8c(_a, _b);
232+
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
233+
_a := 10; _b := 30; _c := 50;
234+
CALL test_proc8c(_a, b => _b);
235+
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
236+
END
237+
$$;
238+
NOTICE: a: 10, b: 30, c: 11
239+
NOTICE: _a: 100, _b: 40, _c: 50
240+
NOTICE: a: 10, b: 30, c: 11
241+
NOTICE: _a: 100, _b: 40, _c: 50
155242
-- transition variable assignment
156243
TRUNCATE test1;
157244
CREATE FUNCTION triggerfunc1() RETURNS trigger

‎src/pl/plpgsql/src/pl_exec.c

Lines changed: 27 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -2146,7 +2146,6 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
21462146
FuncExpr*funcexpr;
21472147
inti;
21482148
HeapTupletuple;
2149-
intnumargsPG_USED_FOR_ASSERTS_ONLY;
21502149
Oid*argtypes;
21512150
char**argnames;
21522151
char*argmodes;
@@ -2169,11 +2168,9 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
21692168
tuple=SearchSysCache1(PROCOID,ObjectIdGetDatum(funcexpr->funcid));
21702169
if (!HeapTupleIsValid(tuple))
21712170
elog(ERROR,"cache lookup failed for function %u",funcexpr->funcid);
2172-
numargs=get_func_arg_info(tuple,&argtypes,&argnames,&argmodes);
2171+
get_func_arg_info(tuple,&argtypes,&argnames,&argmodes);
21732172
ReleaseSysCache(tuple);
21742173

2175-
Assert(numargs==list_length(funcexpr->args));
2176-
21772174
/*
21782175
* Construct row
21792176
*/
@@ -2192,16 +2189,36 @@ exec_stmt_call(PLpgSQL_execstate *estate, PLpgSQL_stmt_call *stmt)
21922189

21932190
if (argmodes&&argmodes[i]==PROARGMODE_INOUT)
21942191
{
2195-
Param*param;
2192+
if (IsA(n,Param))
2193+
{
2194+
Param*param=castNode(Param,n);
2195+
2196+
/* paramid is offset by 1 (see make_datum_param()) */
2197+
row->varnos[nfields++]=param->paramid-1;
2198+
}
2199+
elseif (IsA(n,NamedArgExpr))
2200+
{
2201+
NamedArgExpr*nexpr=castNode(NamedArgExpr,n);
2202+
Param*param;
2203+
2204+
if (!IsA(nexpr->arg,Param))
2205+
ereport(ERROR,
2206+
(errcode(ERRCODE_SYNTAX_ERROR),
2207+
errmsg("argument %d is an output argument but is not writable",i+1)));
21962208

2197-
if (!IsA(n,Param))
2209+
param=castNode(Param,nexpr->arg);
2210+
2211+
/*
2212+
* Named arguments must be after positional arguments,
2213+
* so we can increase nfields.
2214+
*/
2215+
row->varnos[nexpr->argnumber]=param->paramid-1;
2216+
nfields++;
2217+
}
2218+
else
21982219
ereport(ERROR,
21992220
(errcode(ERRCODE_SYNTAX_ERROR),
22002221
errmsg("argument %d is an output argument but is not writable",i+1)));
2201-
2202-
param=castNode(Param,n);
2203-
/* paramid is offset by 1 (see make_datum_param()) */
2204-
row->varnos[nfields++]=param->paramid-1;
22052222
}
22062223
i++;
22072224
}

‎src/pl/plpgsql/src/sql/plpgsql_call.sql

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,6 +142,80 @@ $$;
142142
CALL test_proc7(100,-1,-1);
143143

144144

145+
-- named parameters and defaults
146+
147+
CREATE PROCEDURE test_proc8a(INOUT aint, INOUT bint)
148+
LANGUAGE plpgsql
149+
AS $$
150+
BEGIN
151+
RAISE NOTICE'a: %, b: %', a, b;
152+
a := a*10;
153+
b := b+10;
154+
END;
155+
$$;
156+
157+
CALL test_proc8a(10,20);
158+
CALL test_proc8a(b=>20, a=>10);
159+
160+
DO $$
161+
DECLARE _aint; _bint;
162+
BEGIN
163+
_a :=10; _b :=30;
164+
CALL test_proc8a(_a, _b);
165+
RAISE NOTICE'_a: %, _b: %', _a, _b;
166+
CALL test_proc8a(b=> _b, a=> _a);
167+
RAISE NOTICE'_a: %, _b: %', _a, _b;
168+
END
169+
$$;
170+
171+
172+
CREATE PROCEDURE test_proc8b(INOUT aint, INOUT bint, INOUT cint)
173+
LANGUAGE plpgsql
174+
AS $$
175+
BEGIN
176+
RAISE NOTICE'a: %, b: %, c: %', a, b, c;
177+
a := a*10;
178+
b := b+10;
179+
c := c*-10;
180+
END;
181+
$$;
182+
183+
DO $$
184+
DECLARE _aint; _bint; _cint;
185+
BEGIN
186+
_a :=10; _b :=30; _c :=50;
187+
CALL test_proc8b(_a, _b, _c);
188+
RAISE NOTICE'_a: %, _b: %, _c: %', _a, _b, _c;
189+
CALL test_proc8b(_a, c=> _c, b=> _b);
190+
RAISE NOTICE'_a: %, _b: %, _c: %', _a, _b, _c;
191+
END
192+
$$;
193+
194+
195+
CREATE PROCEDURE test_proc8c(INOUT aint, INOUT bint, INOUT cint DEFAULT11)
196+
LANGUAGE plpgsql
197+
AS $$
198+
BEGIN
199+
RAISE NOTICE'a: %, b: %, c: %', a, b, c;
200+
a := a*10;
201+
b := b+10;
202+
c := c*-10;
203+
END;
204+
$$;
205+
206+
DO $$
207+
DECLARE _aint; _bint; _cint;
208+
BEGIN
209+
_a :=10; _b :=30; _c :=50;
210+
CALL test_proc8c(_a, _b);
211+
RAISE NOTICE'_a: %, _b: %, _c: %', _a, _b, _c;
212+
_a :=10; _b :=30; _c :=50;
213+
CALL test_proc8c(_a, b=> _b);
214+
RAISE NOTICE'_a: %, _b: %, _c: %', _a, _b, _c;
215+
END
216+
$$;
217+
218+
145219
-- transition variable assignment
146220

147221
TRUNCATE test1;

‎src/test/regress/expected/create_procedure.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -91,6 +91,31 @@ $$;
9191
ERROR: calling procedures with output arguments is not supported in SQL functions
9292
CONTEXT: SQL function "ptest4b"
9393
DROP PROCEDURE ptest4a;
94+
-- named and default parameters
95+
CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100)
96+
LANGUAGE SQL
97+
AS $$
98+
INSERT INTO cp_test VALUES(a, b);
99+
INSERT INTO cp_test VALUES(c, b);
100+
$$;
101+
TRUNCATE cp_test;
102+
CALL ptest5(10, 'Hello', 20);
103+
CALL ptest5(10, 'Hello');
104+
CALL ptest5(10, b => 'Hello');
105+
CALL ptest5(b => 'Hello', a => 10);
106+
SELECT * FROM cp_test;
107+
a | b
108+
-----+-------
109+
10 | Hello
110+
20 | Hello
111+
10 | Hello
112+
100 | Hello
113+
10 | Hello
114+
100 | Hello
115+
10 | Hello
116+
100 | Hello
117+
(8 rows)
118+
94119
-- various error cases
95120
CALL version(); -- error: not a procedure
96121
ERROR: version() is not a procedure

‎src/test/regress/sql/create_procedure.sql

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,25 @@ $$;
6565
DROP PROCEDURE ptest4a;
6666

6767

68+
-- named and default parameters
69+
70+
CREATEOR REPLACE PROCEDURE ptest5(aint, btext, cint default100)
71+
LANGUAGE SQL
72+
AS $$
73+
INSERT INTO cp_testVALUES(a, b);
74+
INSERT INTO cp_testVALUES(c, b);
75+
$$;
76+
77+
TRUNCATE cp_test;
78+
79+
CALL ptest5(10,'Hello',20);
80+
CALL ptest5(10,'Hello');
81+
CALL ptest5(10, b=>'Hello');
82+
CALL ptest5(b=>'Hello', a=>10);
83+
84+
SELECT*FROM cp_test;
85+
86+
6887
-- various error cases
6988

7089
CALL version();-- error: not a procedure

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp