Skip to content

Commit 15c7293

Browse files
tglsfdctglsfdc
committed
Fix bugs in plpgsql's handling of CALL argument lists.
exec_stmt_call() tried to extract information out of a CALL statement's argument list without using expand_function_arguments(), apparently in the hope of saving a few nanoseconds by not processing defaulted arguments. It got that quite wrong though, leading to crashes with named arguments, as well as failure to enforce writability of the argument for a defaulted INOUT parameter. Fix and simplify the logic by using expand_function_arguments() before examining the list. Also, move the argument-examination to just after producing the CALL command's plan, before invoking the called procedure. This ensures that we'll track possible changes in the procedure's argument list correctly, and avoids a hazard of the plan cache being flushed while the 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.org Discussion: https://postgr.es/m/CAFj8pRA6UsujpTs9Sdwmk-R6yQykPx46wgjj+YZ7zxm4onrDyw@mail.gmail.com
1 parent 3e0b05a commit 15c7293

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+
An input argument 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 2 is an output argument but is not writable
117+
ERROR: procedure parameter "b" is an output parameter 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)