Debian Codegraph @

doc/src/sgml/ref/drop_procedure.sgml

raw
1 <!--
2 doc/src/sgml/ref/drop_procedure.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-dropprocedure">
7 <indexterm zone="sql-dropprocedure">
8 <primary>DROP PROCEDURE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>DROP PROCEDURE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>DROP PROCEDURE</refname>
19 <refpurpose>remove a procedure</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 DROP PROCEDURE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [ ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) ] [, ...]
25 [ CASCADE | RESTRICT ]
26 </synopsis>
27 </refsynopsisdiv>
28
29 <refsect1>
30 <title>Description</title>
31
32 <para>
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
37 can exist with the same name and different argument lists.
38 </para>
39 </refsect1>
40
41 <refsect1>
42 <title>Parameters</title>
43
44 <variablelist>
45 <varlistentry>
46 <term><literal>IF EXISTS</literal></term>
47 <listitem>
48 <para>
49 Do not throw an error if the procedure does not exist. A notice is issued
50 in this case.
51 </para>
52 </listitem>
53 </varlistentry>
54
55 <varlistentry>
56 <term><replaceable class="parameter">name</replaceable></term>
57 <listitem>
58 <para>
59 The name (optionally schema-qualified) of an existing procedure.
60 </para>
61 </listitem>
62 </varlistentry>
63
64 <varlistentry>
65 <term><replaceable class="parameter">argmode</replaceable></term>
66
67 <listitem>
68 <para>
69 The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
70 <literal>INOUT</literal>, or <literal>VARIADIC</literal>. If omitted,
71 the default is <literal>IN</literal> (but see below).
72 </para>
73 </listitem>
74 </varlistentry>
75
76 <varlistentry>
77 <term><replaceable class="parameter">argname</replaceable></term>
78
79 <listitem>
80 <para>
81 The name of an argument.
82 Note that <command>DROP PROCEDURE</command> does not actually pay
83 any attention to argument names, since only the argument data
84 types are used to determine the procedure's identity.
85 </para>
86 </listitem>
87 </varlistentry>
88
89 <varlistentry>
90 <term><replaceable class="parameter">argtype</replaceable></term>
91
92 <listitem>
93 <para>
94 The data type(s) of the procedure's arguments (optionally
95 schema-qualified), if any.
96 See below for details.
97 </para>
98 </listitem>
99 </varlistentry>
100
101 <varlistentry>
102 <term><literal>CASCADE</literal></term>
103 <listitem>
104 <para>
105 Automatically drop objects that depend on the procedure,
106 and in turn all objects that depend on those objects
107 (see <xref linkend="ddl-depend"/>).
108 </para>
109 </listitem>
110 </varlistentry>
111
112 <varlistentry>
113 <term><literal>RESTRICT</literal></term>
114 <listitem>
115 <para>
116 Refuse to drop the procedure if any objects depend on it. This
117 is the default.
118 </para>
119 </listitem>
120 </varlistentry>
121 </variablelist>
122 </refsect1>
123
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
170 <refsect1 id="sql-dropprocedure-examples">
171 <title>Examples</title>
172
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:
187 <programlisting>
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
193 </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></para>
198 </refsect1>
199
200 <refsect1 id="sql-dropprocedure-compatibility">
201 <title>Compatibility</title>
202
203 <para>
204 This command conforms to the SQL standard, with
205 these <productname>PostgreSQL</productname> extensions:
206 <itemizedlist>
207 <listitem>
208 <para>The standard only allows one procedure to be dropped per command.</para>
209 </listitem>
210 <listitem>
211 <para>The <literal>IF EXISTS</literal> option is an extension.</para>
212 </listitem>
213 <listitem>
214 <para>The ability to specify argument modes and names is an
215 extension, and the lookup rules differ when modes are given.</para>
216 </listitem>
217 </itemizedlist></para>
218 </refsect1>
219
220 <refsect1>
221 <title>See Also</title>
222
223 <simplelist type="inline">
224 <member><xref linkend="sql-createprocedure"/></member>
225 <member><xref linkend="sql-alterprocedure"/></member>
226 <member><xref linkend="sql-dropfunction"/></member>
227 <member><xref linkend="sql-droproutine"/></member>
228 </simplelist>
229 </refsect1>
230
231 </refentry>