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

Commit15c7293

Browse files
committed
Fix bugs in plpgsql's handling of CALL argument lists.
exec_stmt_call() tried to extract information out of a CALL statement'sargument list without using expand_function_arguments(), apparently inthe hope of saving a few nanoseconds by not processing defaultedarguments. It got that quite wrong though, leading to crashes withnamed arguments, as well as failure to enforce writability of theargument for a defaulted INOUT parameter. Fix and simplify the logicby using expand_function_arguments() before examining the list.Also, move the argument-examination to just after producing the CALLcommand's plan, before invoking the called procedure. This ensuresthat we'll track possible changes in the procedure's argument listcorrectly, and avoids a hazard of the plan cache being flushed whilethe procedure executes.Also fix assorted falsehoods and omissions in associated documentation.Per bug #15477 from Alexey Stepanov.Patch by me, with some help from Pavel Stehule. Back-patch to v11.Discussion:https://postgr.es/m/15477-86075b1d1d319e0a@postgresql.orgDiscussion:https://postgr.es/m/CAFj8pRA6UsujpTs9Sdwmk-R6yQykPx46wgjj+YZ7zxm4onrDyw@mail.gmail.com
1 parent3e0b05a commit15c7293

File tree

5 files changed

+297
-123
lines changed

5 files changed

+297
-123
lines changed

‎doc/src/sgml/plpgsql.sgml

Lines changed: 27 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1864,15 +1864,29 @@ SELECT * FROM get_available_flightid(CURRENT_DATE);
18641864

18651865
<para>
18661866
A procedure does not have a return value. A procedure can therefore end
1867-
without a <command>RETURN</command> statement. If
1868-
a <command>RETURN</command> statement is desired to exit the code early,
1869-
then <symbol>NULL</symbol> must be returned. Returning any other value
1870-
will result in an error.
1867+
without a <command>RETURN</command> statement. If you wish to use
1868+
a <command>RETURN</command> statement to exit the code early, write
1869+
just <command>RETURN</command> with no expression.
18711870
</para>
18721871

18731872
<para>
1874-
If a procedure has output parameters, then the output values can be
1875-
assigned to the parameters as if they were variables. For example:
1873+
If the procedure has output parameters, the final values of the output
1874+
parameter variables will be returned to the caller.
1875+
</para>
1876+
</sect2>
1877+
1878+
<sect2 id="plpgsql-statements-calling-procedure">
1879+
<title>Calling a Procedure</title>
1880+
1881+
<para>
1882+
A <application>PL/pgSQL</application> function, procedure,
1883+
or <command>DO</command> block can call a procedure
1884+
using <command>CALL</command>. Output parameters are handled
1885+
differently from the way that <command>CALL</command> works in plain
1886+
SQL. Each <literal>INOUT</literal> parameter of the procedure must
1887+
correspond to a variable in the <command>CALL</command> statement, and
1888+
whatever the procedure returns is assigned back to that variable after
1889+
it returns. For example:
18761890
<programlisting>
18771891
CREATE PROCEDURE triple(INOUT x int)
18781892
LANGUAGE plpgsql
@@ -1882,7 +1896,13 @@ BEGIN
18821896
END;
18831897
$$;
18841898

1885-
CALL triple(5);
1899+
DO $$
1900+
DECLARE myvar int := 5;
1901+
BEGIN
1902+
CALL triple(myvar);
1903+
RAISE NOTICE 'myvar = %', myvar; -- prints 15
1904+
END
1905+
$$;
18861906
</programlisting>
18871907
</para>
18881908
</sect2>

‎doc/src/sgml/ref/call.sgml

Lines changed: 9 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -33,7 +33,8 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
3333
</para>
3434

3535
<para>
36-
If the procedure has output arguments, then a result row will be returned.
36+
If the procedure has any output parameters, then a result row will be
37+
returned, containing the values of those parameters.
3738
</para>
3839
</refsect1>
3940

@@ -54,7 +55,7 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
5455
<term><replaceable class="parameter">argument</replaceable></term>
5556
<listitem>
5657
<para>
57-
An argument for the procedure call.
58+
Aninputargument for the procedure call.
5859
See <xref linkend="sql-syntax-calling-funcs"/> for the full details on
5960
function and procedure call syntax, including use of named parameters.
6061
</para>
@@ -81,6 +82,12 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
8182
Transaction control statements are only allowed if <command>CALL</command>
8283
is executed in its own transaction.
8384
</para>
85+
86+
<para>
87+
<application>PL/pgSQL</application> handles output parameters
88+
in <command>CALL</command> commands differently;
89+
see <xref linkend="plpgsql-statements-calling-procedure"/>.
90+
</para>
8491
</refsect1>
8592

8693
<refsect1>

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

Lines changed: 72 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -114,7 +114,7 @@ BEGIN
114114
RAISE INFO 'x = %, y = %', x, y;
115115
END;
116116
$$;
117-
ERROR:argument 2is an outputargument but is not writable
117+
ERROR:procedure parameter "b"is an outputparameter but corresponding argument is not writable
118118
CONTEXT: PL/pgSQL function inline_code_block line 6 at CALL
119119
DO
120120
LANGUAGE plpgsql
@@ -228,27 +228,42 @@ DO $$
228228
DECLARE _a int; _b int; _c int;
229229
BEGIN
230230
_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);
231+
CALL test_proc8c(_a, _b, _c);
235232
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
236233
_a := 10; _b := 30; _c := 50;
237-
CALL test_proc8c(_a,_b, _c);
234+
CALL test_proc8c(_a,c => _c, b => _b);
238235
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
239236
_a := 10; _b := 30; _c := 50;
240237
CALL test_proc8c(c => _c, b => _b, a => _a);
241238
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
242239
END
243240
$$;
244-
NOTICE: a: 10, b: 30, c: 11
245-
NOTICE: _a: 100, _b: 40, _c: 50
246-
NOTICE: a: 10, b: 30, c: 11
247-
NOTICE: _a: 100, _b: 40, _c: 50
248241
NOTICE: a: 10, b: 30, c: 50
249242
NOTICE: _a: 100, _b: 40, _c: -500
250243
NOTICE: a: 10, b: 30, c: 50
251244
NOTICE: _a: 100, _b: 40, _c: -500
245+
NOTICE: a: 10, b: 30, c: 50
246+
NOTICE: _a: 100, _b: 40, _c: -500
247+
DO $$
248+
DECLARE _a int; _b int; _c int;
249+
BEGIN
250+
_a := 10; _b := 30; _c := 50;
251+
CALL test_proc8c(_a, _b); -- fail, no output argument for c
252+
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
253+
END
254+
$$;
255+
ERROR: procedure parameter "c" is an output parameter but corresponding argument is not writable
256+
CONTEXT: PL/pgSQL function inline_code_block line 5 at CALL
257+
DO $$
258+
DECLARE _a int; _b int; _c int;
259+
BEGIN
260+
_a := 10; _b := 30; _c := 50;
261+
CALL test_proc8c(_a, b => _b); -- fail, no output argument for c
262+
RAISE NOTICE '_a: %, _b: %, _c: %', _a, _b, _c;
263+
END
264+
$$;
265+
ERROR: procedure parameter "c" is an output parameter but corresponding argument is not writable
266+
CONTEXT: PL/pgSQL function inline_code_block line 5 at CALL
252267
-- transition variable assignment
253268
TRUNCATE test1;
254269
CREATE FUNCTION triggerfunc1() RETURNS trigger
@@ -276,3 +291,50 @@ DROP PROCEDURE test_proc1;
276291
DROP PROCEDURE test_proc3;
277292
DROP PROCEDURE test_proc4;
278293
DROP TABLE test1;
294+
-- more checks for named-parameter handling
295+
CREATE PROCEDURE p1(v_cnt int, v_Text inout text = NULL)
296+
AS $$
297+
BEGIN
298+
v_Text := 'v_cnt = ' || v_cnt;
299+
END
300+
$$ LANGUAGE plpgsql;
301+
DO $$
302+
DECLARE
303+
v_Text text;
304+
v_cnt integer := 42;
305+
BEGIN
306+
CALL p1(v_cnt := v_cnt); -- error, must supply something for v_Text
307+
RAISE NOTICE '%', v_Text;
308+
END;
309+
$$;
310+
ERROR: procedure parameter "v_text" is an output parameter but corresponding argument is not writable
311+
CONTEXT: PL/pgSQL function inline_code_block line 6 at CALL
312+
DO $$
313+
DECLARE
314+
v_Text text;
315+
v_cnt integer := 42;
316+
BEGIN
317+
CALL p1(v_cnt := v_cnt, v_Text := v_Text);
318+
RAISE NOTICE '%', v_Text;
319+
END;
320+
$$;
321+
NOTICE: v_cnt = 42
322+
DO $$
323+
DECLARE
324+
v_Text text;
325+
BEGIN
326+
CALL p1(10, v_Text := v_Text);
327+
RAISE NOTICE '%', v_Text;
328+
END;
329+
$$;
330+
NOTICE: v_cnt = 10
331+
DO $$
332+
DECLARE
333+
v_Text text;
334+
v_cnt integer;
335+
BEGIN
336+
CALL p1(v_Text := v_Text, v_cnt := v_cnt);
337+
RAISE NOTICE '%', v_Text;
338+
END;
339+
$$;
340+
NOTICE: <NULL>

0 commit comments

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