diff options
Diffstat (limited to 'doc/src/sgml/ref/drop_procedure.sgml')
| -rw-r--r-- | doc/src/sgml/ref/drop_procedure.sgml | 92 |
1 files changed, 81 insertions, 11 deletions
diff --git a/doc/src/sgml/ref/drop_procedure.sgml b/doc/src/sgml/ref/drop_procedure.sgml index bf2c6ce1aa..4c86062f34 100644 --- a/doc/src/sgml/ref/drop_procedure.sgml +++ b/doc/src/sgml/ref/drop_procedure.sgml @@ -30,10 +30,10 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ <title>Description</title> <para> - <command>DROP PROCEDURE</command> removes the definition of an existing - procedure. To execute this command the user must be the - owner of the procedure. The argument types to the - procedure must be specified, since several different procedures + <command>DROP PROCEDURE</command> removes the definition of one or more + existing procedures. To execute this command the user must be the + owner of the procedure(s). The argument types to the + procedure(s) usually must be specified, since several different procedures can exist with the same name and different argument lists. </para> </refsect1> @@ -56,8 +56,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ <term><replaceable class="parameter">name</replaceable></term> <listitem> <para> - The name (optionally schema-qualified) of an existing procedure. If no - argument list is specified, the name must be unique in its schema. + The name (optionally schema-qualified) of an existing procedure. </para> </listitem> </varlistentry> @@ -69,7 +68,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ <para> The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>, <literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted, - the default is <literal>IN</literal>. + the default is <literal>IN</literal> (but see below). </para> </listitem> </varlistentry> @@ -82,7 +81,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ The name of an argument. Note that <command>DROP PROCEDURE</command> does not actually pay any attention to argument names, since only the argument data - types are needed to determine the procedure's identity. + types are used to determine the procedure's identity. </para> </listitem> </varlistentry> @@ -94,6 +93,7 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ <para> The data type(s) of the procedure's arguments (optionally schema-qualified), if any. + See below for details. </para> </listitem> </varlistentry> @@ -121,12 +121,81 @@ DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ </variablelist> </refsect1> + <refsect1 id="sql-dropprocedure-notes"> + <title>Notes</title> + + <para> + If there is only one procedure of the given name, the argument list + can be omitted. Omit the parentheses too in this case. + </para> + + <para> + In <productname>PostgreSQL</productname>, it's sufficient to list the + input (including <literal>INOUT</literal>) arguments, + because no two routines of the same name are allowed to share the same + input-argument list. Moreover, the <command>DROP</command> command + will not actually check that you wrote the types + of <literal>OUT</literal> arguments correctly; so any arguments that + are explicitly marked <literal>OUT</literal> are just noise. But + writing them is recommendable for consistency with the + corresponding <command>CREATE</command> command. + </para> + + <para> + For compatibility with the SQL standard, it is also allowed to write + all the argument data types (including those of <literal>OUT</literal> + arguments) without + any <replaceable class="parameter">argmode</replaceable> markers. + When this is done, the types of the procedure's <literal>OUT</literal> + argument(s) <emphasis>will</emphasis> be verified against the command. + This provision creates an ambiguity, in that when the argument list + contains no <replaceable class="parameter">argmode</replaceable> + markers, it's unclear which rule is intended. + The <command>DROP</command> command will attempt the lookup both ways, + and will throw an error if two different procedures are found. + To avoid the risk of such ambiguity, it's recommendable to + write <literal>IN</literal> markers explicitly rather than letting them + be defaulted, thus forcing the + traditional <productname>PostgreSQL</productname> interpretation to be + used. + </para> + + <para> + The lookup rules just explained are also used by other commands that + act on existing procedures, such as <command>ALTER PROCEDURE</command> + and <command>COMMENT ON PROCEDURE</command>. + </para> + </refsect1> + <refsect1 id="sql-dropprocedure-examples"> <title>Examples</title> + <para> + If there is only one procedure <literal>do_db_maintenance</literal>, + this command is sufficient to drop it: +<programlisting> +DROP PROCEDURE do_db_maintenance; +</programlisting> + </para> + + <para> + Given this procedure definition: +<programlisting> +CREATE PROCEDURE do_db_maintenance(IN target_schema text, OUT results text) ... +</programlisting> + any one of these commands would work to drop it: <programlisting> -DROP PROCEDURE do_db_maintenance(); +DROP PROCEDURE do_db_maintenance(IN target_schema text, OUT results text); +DROP PROCEDURE do_db_maintenance(IN text, OUT text); +DROP PROCEDURE do_db_maintenance(IN text); +DROP PROCEDURE do_db_maintenance(text); +DROP PROCEDURE do_db_maintenance(text, text); -- potentially ambiguous </programlisting> + However, the last example would be ambiguous if there is also, say, +<programlisting> +CREATE PROCEDURE do_db_maintenance(IN target_schema text, IN options text) ... +</programlisting> + </para> </refsect1> <refsect1 id="sql-dropprocedure-compatibility"> @@ -140,10 +209,11 @@ DROP PROCEDURE do_db_maintenance(); <para>The standard only allows one procedure to be dropped per command.</para> </listitem> <listitem> - <para>The <literal>IF EXISTS</literal> option</para> + <para>The <literal>IF EXISTS</literal> option is an extension.</para> </listitem> <listitem> - <para>The ability to specify argument modes and names</para> + <para>The ability to specify argument modes and names is an + extension, and the lookup rules differ when modes are given.</para> </listitem> </itemizedlist></para> </refsect1> |