Skip to content

Commit e56bce5

Browse files
tglsfdctglsfdc
committed
Reconsider the handling of procedure OUT parameters.
Commit 2453ea1 redefined pg_proc.proargtypes to include the types of OUT parameters, for procedures only. While that had some advantages for implementing the SQL-spec behavior of DROP PROCEDURE, it was pretty disastrous from a number of other perspectives. Notably, since the primary key of pg_proc is name + proargtypes, this made it possible to have multiple procedures with identical names + input arguments and differing output argument types. That would make it impossible to call any one of the procedures by writing just NULL (or "?", or any other data-type-free notation) for the output argument(s). The change also seems likely to cause grave confusion for client applications that examine pg_proc and expect the traditional definition of proargtypes. Hence, revert the definition of proargtypes to what it was, and undo a number of complications that had been added to support that. To support the SQL-spec behavior of DROP PROCEDURE, when there are no argmode markers in the command's parameter list, we perform the lookup both ways (that is, matching against both proargtypes and proallargtypes), succeeding if we get just one unique match. In principle this could result in ambiguous-function failures that would not happen when using only one of the two rules. However, overloading of procedure names is thought to be a pretty rare usage, so this shouldn't cause many problems in practice. Postgres-specific code such as pg_dump can defend against any possibility of such failures by being careful to specify argmodes for all procedure arguments. This also fixes a few other bugs in the area of CALL statements with named parameters, and improves the documentation a little. catversion bump forced because the representation of procedures with OUT arguments changes. Discussion: https://postgr.es/m/3742981.1621533210@sss.pgh.pa.us
1 parent 3a09d75 commit e56bce5

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

44 files changed

+1069
-392
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5905,9 +5905,8 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
59055905
<para>
59065906
An array of the data types of the function arguments. This includes
59075907
only input arguments (including <literal>INOUT</literal> and
5908-
<literal>VARIADIC</literal> arguments), as well as
5909-
<literal>OUT</literal> parameters of procedures, and thus represents
5910-
the call signature of the function or procedure.
5908+
<literal>VARIADIC</literal> arguments), and thus represents
5909+
the call signature of the function.
59115910
</para></entry>
59125911
</row>
59135912

‎doc/src/sgml/plpgsql.sgml

Lines changed: 12 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -480,7 +480,7 @@ $$ LANGUAGE plpgsql;
480480

481481
<para>
482482
To call a function with <literal>OUT</literal> parameters, omit the
483-
output parameter in the function call:
483+
output parameter(s) in the function call:
484484
<programlisting>
485485
SELECT sales_tax(100.00);
486486
</programlisting>
@@ -523,16 +523,20 @@ $$ LANGUAGE plpgsql;
523523
</programlisting>
524524

525525
In a call to a procedure, all the parameters must be specified. For
526-
output parameters, <literal>NULL</literal> may be specified.
526+
output parameters, <literal>NULL</literal> may be specified when
527+
calling the procedure from plain SQL:
527528
<programlisting>
528529
CALL sum_n_product(2, 4, NULL, NULL);
529530
sum | prod
530531
-----+------
531532
6 | 8
532533
</programlisting>
533-
Output parameters in procedures become more interesting in nested calls,
534-
where they can be assigned to variables. See <xref
535-
linkend="plpgsql-statements-calling-procedure"/> for details.
534+
535+
However, when calling a procedure
536+
from <application>PL/pgSQL</application>, you should instead write a
537+
variable for any output parameter; the variable will receive the result
538+
of the call. See <xref linkend="plpgsql-statements-calling-procedure"/>
539+
for details.
536540
</para>
537541

538542
<para>
@@ -2030,6 +2034,9 @@ BEGIN
20302034
END;
20312035
$$;
20322036
</programlisting>
2037+
The variable corresponding to an output parameter can be a simple
2038+
variable or a field of a composite-type variable. Currently,
2039+
it cannot be an element of an array.
20332040
</para>
20342041
</sect2>
20352042

‎doc/src/sgml/ref/alter_extension.sgml

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -212,12 +212,11 @@ ALTER EXTENSION <replaceable class="parameter">name</replaceable> DROP <replacea
212212
argument: <literal>IN</literal>, <literal>OUT</literal>,
213213
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
214214
If omitted, the default is <literal>IN</literal>.
215-
Note that <command>ALTER EXTENSION</command> does not actually pay any
216-
attention to <literal>OUT</literal> arguments for functions and
217-
aggregates (but not procedures), since only the input arguments are
218-
needed to determine the function's identity. So it is sufficient to
219-
list the <literal>IN</literal>, <literal>INOUT</literal>, and
220-
<literal>VARIADIC</literal> arguments for functions and aggregates.
215+
Note that <command>ALTER EXTENSION</command> does not actually pay
216+
any attention to <literal>OUT</literal> arguments, since only the input
217+
arguments are needed to determine the function's identity.
218+
So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
219+
and <literal>VARIADIC</literal> arguments.
221220
</para>
222221
</listitem>
223222
</varlistentry>

‎doc/src/sgml/ref/alter_procedure.sgml

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -96,7 +96,7 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="para
9696
The name of an argument.
9797
Note that <command>ALTER PROCEDURE</command> does not actually pay
9898
any attention to argument names, since only the argument data
99-
types are needed to determine the procedure's identity.
99+
types are used to determine the procedure's identity.
100100
</para>
101101
</listitem>
102102
</varlistentry>
@@ -108,6 +108,8 @@ ALTER PROCEDURE <replaceable>name</replaceable> [ ( [ [ <replaceable class="para
108108
<para>
109109
The data type(s) of the procedure's arguments (optionally
110110
schema-qualified), if any.
111+
See <xref linkend="sql-dropprocedure"/> for the details of how
112+
the procedure is looked up using the argument data type(s).
111113
</para>
112114
</listitem>
113115
</varlistentry>

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

Lines changed: 22 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -55,9 +55,24 @@ CALL <replaceable class="parameter">name</replaceable> ( [ <replaceable class="p
5555
<term><replaceable class="parameter">argument</replaceable></term>
5656
<listitem>
5757
<para>
58-
An input argument for the procedure call.
59-
See <xref linkend="sql-syntax-calling-funcs"/> for the full details on
60-
function and procedure call syntax, including use of named parameters.
58+
An argument expression for the procedure call.
59+
</para>
60+
61+
<para>
62+
Arguments can include parameter names, using the syntax
63+
<literal><replaceable class="parameter">name</replaceable> =&gt; <replaceable class="parameter">value</replaceable></literal>.
64+
This works the same as in ordinary function calls; see
65+
<xref linkend="sql-syntax-calling-funcs"/> for details.
66+
</para>
67+
68+
<para>
69+
Arguments must be supplied for all procedure parameters that lack
70+
defaults, including <literal>OUT</literal> parameters. However,
71+
arguments matching <literal>OUT</literal> parameters are not evaluated,
72+
so it's customary to just write <literal>NULL</literal> for them.
73+
(Writing something else for an <literal>OUT</literal> parameter
74+
might cause compatibility problems with
75+
future <productname>PostgreSQL</productname> versions.)
6176
</para>
6277
</listitem>
6378
</varlistentry>
@@ -101,7 +116,10 @@ CALL do_db_maintenance();
101116
<title>Compatibility</title>
102117

103118
<para>
104-
<command>CALL</command> conforms to the SQL standard.
119+
<command>CALL</command> conforms to the SQL standard,
120+
except for the handling of output parameters. The standard
121+
says that users should write variables to receive the values
122+
of output parameters.
105123
</para>
106124
</refsect1>
107125

‎doc/src/sgml/ref/comment.sgml

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -176,12 +176,11 @@ COMMENT ON
176176
argument: <literal>IN</literal>, <literal>OUT</literal>,
177177
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
178178
If omitted, the default is <literal>IN</literal>.
179-
Note that <command>COMMENT</command> does not actually pay any attention
180-
to <literal>OUT</literal> arguments for functions and aggregates (but
181-
not procedures), since only the input arguments are needed to determine
182-
the function's identity. So it is sufficient to list the
183-
<literal>IN</literal>, <literal>INOUT</literal>, and
184-
<literal>VARIADIC</literal> arguments for functions and aggregates.
179+
Note that <command>COMMENT</command> does not actually pay
180+
any attention to <literal>OUT</literal> arguments, since only the input
181+
arguments are needed to determine the function's identity.
182+
So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
183+
and <literal>VARIADIC</literal> arguments.
185184
</para>
186185
</listitem>
187186
</varlistentry>

‎doc/src/sgml/ref/drop_procedure.sgml

Lines changed: 81 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -30,10 +30,10 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
3030
<title>Description</title>
3131

3232
<para>
33-
<command>DROP PROCEDURE</command> removes the definition of an existing
34-
procedure. To execute this command the user must be the
35-
owner of the procedure. The argument types to the
36-
procedure must be specified, since several different procedures
33+
<command>DROP PROCEDURE</command> removes the definition of one or more
34+
existing procedures. To execute this command the user must be the
35+
owner of the procedure(s). The argument types to the
36+
procedure(s) usually must be specified, since several different procedures
3737
can exist with the same name and different argument lists.
3838
</para>
3939
</refsect1>
@@ -56,8 +56,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
5656
<term><replaceable class="parameter">name</replaceable></term>
5757
<listitem>
5858
<para>
59-
The name (optionally schema-qualified) of an existing procedure. If no
60-
argument list is specified, the name must be unique in its schema.
59+
The name (optionally schema-qualified) of an existing procedure.
6160
</para>
6261
</listitem>
6362
</varlistentry>
@@ -69,7 +68,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
6968
<para>
7069
The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
7170
<literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
72-
the default is <literal>IN</literal>.
71+
the default is <literal>IN</literal> (but see below).
7372
</para>
7473
</listitem>
7574
</varlistentry>
@@ -82,7 +81,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
8281
The name of an argument.
8382
Note that <command>DROP PROCEDURE</command> does not actually pay
8483
any attention to argument names, since only the argument data
85-
types are needed to determine the procedure's identity.
84+
types are used to determine the procedure's identity.
8685
</para>
8786
</listitem>
8887
</varlistentry>
@@ -94,6 +93,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
9493
<para>
9594
The data type(s) of the procedure's arguments (optionally
9695
schema-qualified), if any.
96+
See below for details.
9797
</para>
9898
</listitem>
9999
</varlistentry>
@@ -121,12 +121,81 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [
121121
</variablelist>
122122
</refsect1>
123123

124+
<refsect1 id="sql-dropprocedure-notes">
125+
<title>Notes</title>
126+
127+
<para>
128+
If there is only one procedure of the given name, the argument list
129+
can be omitted. Omit the parentheses too in this case.
130+
</para>
131+
132+
<para>
133+
In <productname>PostgreSQL</productname>, it's sufficient to list the
134+
input (including <literal>INOUT</literal>) arguments,
135+
because no two routines of the same name are allowed to share the same
136+
input-argument list. Moreover, the <command>DROP</command> command
137+
will not actually check that you wrote the types
138+
of <literal>OUT</literal> arguments correctly; so any arguments that
139+
are explicitly marked <literal>OUT</literal> are just noise. But
140+
writing them is recommendable for consistency with the
141+
corresponding <command>CREATE</command> command.
142+
</para>
143+
144+
<para>
145+
For compatibility with the SQL standard, it is also allowed to write
146+
all the argument data types (including those of <literal>OUT</literal>
147+
arguments) without
148+
any <replaceable class="parameter">argmode</replaceable> markers.
149+
When this is done, the types of the procedure's <literal>OUT</literal>
150+
argument(s) <emphasis>will</emphasis> be verified against the command.
151+
This provision creates an ambiguity, in that when the argument list
152+
contains no <replaceable class="parameter">argmode</replaceable>
153+
markers, it's unclear which rule is intended.
154+
The <command>DROP</command> command will attempt the lookup both ways,
155+
and will throw an error if two different procedures are found.
156+
To avoid the risk of such ambiguity, it's recommendable to
157+
write <literal>IN</literal> markers explicitly rather than letting them
158+
be defaulted, thus forcing the
159+
traditional <productname>PostgreSQL</productname> interpretation to be
160+
used.
161+
</para>
162+
163+
<para>
164+
The lookup rules just explained are also used by other commands that
165+
act on existing procedures, such as <command>ALTER PROCEDURE</command>
166+
and <command>COMMENT ON PROCEDURE</command>.
167+
</para>
168+
</refsect1>
169+
124170
<refsect1 id="sql-dropprocedure-examples">
125171
<title>Examples</title>
126172

173+
<para>
174+
If there is only one procedure <literal>do_db_maintenance</literal>,
175+
this command is sufficient to drop it:
176+
<programlisting>
177+
DROP PROCEDURE do_db_maintenance;
178+
</programlisting>
179+
</para>
180+
181+
<para>
182+
Given this procedure definition:
183+
<programlisting>
184+
CREATE PROCEDURE do_db_maintenance(IN target_schema text, OUT results text) ...
185+
</programlisting>
186+
any one of these commands would work to drop it:
127187
<programlisting>
128-
DROP PROCEDURE do_db_maintenance();
188+
DROP PROCEDURE do_db_maintenance(IN target_schema text, OUT results text);
189+
DROP PROCEDURE do_db_maintenance(IN text, OUT text);
190+
DROP PROCEDURE do_db_maintenance(IN text);
191+
DROP PROCEDURE do_db_maintenance(text);
192+
DROP PROCEDURE do_db_maintenance(text, text); -- potentially ambiguous
129193
</programlisting>
194+
However, the last example would be ambiguous if there is also, say,
195+
<programlisting>
196+
CREATE PROCEDURE do_db_maintenance(IN target_schema text, IN options text) ...
197+
</programlisting>
198+
</para>
130199
</refsect1>
131200

132201
<refsect1 id="sql-dropprocedure-compatibility">
@@ -140,10 +209,11 @@ DROP PROCEDURE do_db_maintenance();
140209
<para>The standard only allows one procedure to be dropped per command.</para>
141210
</listitem>
142211
<listitem>
143-
<para>The <literal>IF EXISTS</literal> option</para>
212+
<para>The <literal>IF EXISTS</literal> option is an extension.</para>
144213
</listitem>
145214
<listitem>
146-
<para>The ability to specify argument modes and names</para>
215+
<para>The ability to specify argument modes and names is an
216+
extension, and the lookup rules differ when modes are given.</para>
147217
</listitem>
148218
</itemizedlist></para>
149219
</refsect1>

‎doc/src/sgml/ref/drop_routine.sgml

Lines changed: 36 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -30,15 +30,44 @@ DROP ROUTINE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ (
3030
<title>Description</title>
3131

3232
<para>
33-
<command>DROP ROUTINE</command> removes the definition of an existing
34-
routine, which can be an aggregate function, a normal function, or a
35-
procedure. See
33+
<command>DROP ROUTINE</command> removes the definition of one or more
34+
existing routines. The term <quote>routine</quote> includes
35+
aggregate functions, normal functions, and procedures. See
3636
under <xref linkend="sql-dropaggregate"/>, <xref linkend="sql-dropfunction"/>,
3737
and <xref linkend="sql-dropprocedure"/> for the description of the
3838
parameters, more examples, and further details.
3939
</para>
4040
</refsect1>
4141

42+
<refsect1 id="sql-droproutine-notes">
43+
<title>Notes</title>
44+
45+
<para>
46+
The lookup rules used by <command>DROP ROUTINE</command> are
47+
fundamentally the same as for <command>DROP PROCEDURE</command>; in
48+
particular, <command>DROP ROUTINE</command> shares that command's
49+
behavior of considering an argument list that has
50+
no <replaceable class="parameter">argmode</replaceable> markers to be
51+
possibly using the SQL standard's definition that <literal>OUT</literal>
52+
arguments are included in the list. (<command>DROP AGGREGATE</command>
53+
and <command>DROP FUNCTION</command> do not do that.)
54+
</para>
55+
56+
<para>
57+
In some cases where the same name is shared by routines of different
58+
kinds, it is possible for <command>DROP ROUTINE</command> to fail with
59+
an ambiguity error when a more specific command (<command>DROP
60+
FUNCTION</command>, etc.) would work. Specifying the argument type
61+
list more carefully will also resolve such problems.
62+
</para>
63+
64+
<para>
65+
These lookup rules are also used by other commands that
66+
act on existing routines, such as <command>ALTER ROUTINE</command>
67+
and <command>COMMENT ON ROUTINE</command>.
68+
</para>
69+
</refsect1>
70+
4271
<refsect1 id="sql-droproutine-examples">
4372
<title>Examples</title>
4473

@@ -64,13 +93,14 @@ DROP ROUTINE foo(integer);
6493
<para>The standard only allows one routine to be dropped per command.</para>
6594
</listitem>
6695
<listitem>
67-
<para>The <literal>IF EXISTS</literal> option</para>
96+
<para>The <literal>IF EXISTS</literal> option is an extension.</para>
6897
</listitem>
6998
<listitem>
70-
<para>The ability to specify argument modes and names</para>
99+
<para>The ability to specify argument modes and names is an
100+
extension, and the lookup rules differ when modes are given.</para>
71101
</listitem>
72102
<listitem>
73-
<para>Aggregate functions are an extension.</para>
103+
<para>User-definable aggregate functions are an extension.</para>
74104
</listitem>
75105
</itemizedlist></para>
76106
</refsect1>

‎doc/src/sgml/ref/security_label.sgml

Lines changed: 5 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -126,12 +126,11 @@ SECURITY LABEL [ FOR <replaceable class="parameter">provider</replaceable> ] ON
126126
argument: <literal>IN</literal>, <literal>OUT</literal>,
127127
<literal>INOUT</literal>, or <literal>VARIADIC</literal>.
128128
If omitted, the default is <literal>IN</literal>.
129-
Note that <command>SECURITY LABEL</command> does not actually pay any
130-
attention to <literal>OUT</literal> arguments for functions and
131-
aggregates (but not procedures), since only the input arguments are
132-
needed to determine the function's identity. So it is sufficient to
133-
list the <literal>IN</literal>, <literal>INOUT</literal>, and
134-
<literal>VARIADIC</literal> arguments for functions and aggregates.
129+
Note that <command>SECURITY LABEL</command> does not actually
130+
pay any attention to <literal>OUT</literal> arguments, since only the input
131+
arguments are needed to determine the function's identity.
132+
So it is sufficient to list the <literal>IN</literal>, <literal>INOUT</literal>,
133+
and <literal>VARIADIC</literal> arguments.
135134
</para>
136135
</listitem>
137136
</varlistentry>

0 commit comments

Comments
 (0)