1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
|
'\" t
.\" Title: DROP PROCEDURE
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
.\" Manual: PostgreSQL 15.6 Documentation
.\" Source: PostgreSQL 15.6
.\" Language: English
.\"
.TH "DROP PROCEDURE" "7" "2024" "PostgreSQL 15.6" "PostgreSQL 15.6 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
DROP_PROCEDURE \- remove a procedure
.SH "SYNOPSIS"
.sp
.nf
DROP PROCEDURE [ IF EXISTS ] \fIname\fR [ ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) ] [, \&.\&.\&.]
[ CASCADE | RESTRICT ]
.fi
.SH "DESCRIPTION"
.PP
\fBDROP PROCEDURE\fR
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\&.
.SH "PARAMETERS"
.PP
IF EXISTS
.RS 4
Do not throw an error if the procedure does not exist\&. A notice is issued in this case\&.
.RE
.PP
\fIname\fR
.RS 4
The name (optionally schema\-qualified) of an existing procedure\&.
.RE
.PP
\fIargmode\fR
.RS 4
The mode of an argument:
IN,
OUT,
INOUT, or
VARIADIC\&. If omitted, the default is
IN
(but see below)\&.
.RE
.PP
\fIargname\fR
.RS 4
The name of an argument\&. Note that
\fBDROP PROCEDURE\fR
does not actually pay any attention to argument names, since only the argument data types are used to determine the procedure\*(Aqs identity\&.
.RE
.PP
\fIargtype\fR
.RS 4
The data type(s) of the procedure\*(Aqs arguments (optionally schema\-qualified), if any\&. See below for details\&.
.RE
.PP
CASCADE
.RS 4
Automatically drop objects that depend on the procedure, and in turn all objects that depend on those objects (see
Section\ \&5.14)\&.
.RE
.PP
RESTRICT
.RS 4
Refuse to drop the procedure if any objects depend on it\&. This is the default\&.
.RE
.SH "NOTES"
.PP
If there is only one procedure of the given name, the argument list can be omitted\&. Omit the parentheses too in this case\&.
.PP
In
PostgreSQL, it\*(Aqs sufficient to list the input (including
INOUT) arguments, because no two routines of the same name are allowed to share the same input\-argument list\&. Moreover, the
\fBDROP\fR
command will not actually check that you wrote the types of
OUT
arguments correctly; so any arguments that are explicitly marked
OUT
are just noise\&. But writing them is recommendable for consistency with the corresponding
\fBCREATE\fR
command\&.
.PP
For compatibility with the SQL standard, it is also allowed to write all the argument data types (including those of
OUT
arguments) without any
\fIargmode\fR
markers\&. When this is done, the types of the procedure\*(Aqs
OUT
argument(s)
\fIwill\fR
be verified against the command\&. This provision creates an ambiguity, in that when the argument list contains no
\fIargmode\fR
markers, it\*(Aqs unclear which rule is intended\&. The
\fBDROP\fR
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\*(Aqs recommendable to write
IN
markers explicitly rather than letting them be defaulted, thus forcing the traditional
PostgreSQL
interpretation to be used\&.
.PP
The lookup rules just explained are also used by other commands that act on existing procedures, such as
\fBALTER PROCEDURE\fR
and
\fBCOMMENT ON PROCEDURE\fR\&.
.SH "EXAMPLES"
.PP
If there is only one procedure
do_db_maintenance, this command is sufficient to drop it:
.sp
.if n \{\
.RS 4
.\}
.nf
DROP PROCEDURE do_db_maintenance;
.fi
.if n \{\
.RE
.\}
.PP
Given this procedure definition:
.sp
.if n \{\
.RS 4
.\}
.nf
CREATE PROCEDURE do_db_maintenance(IN target_schema text, OUT results text) \&.\&.\&.
.fi
.if n \{\
.RE
.\}
.sp
any one of these commands would work to drop it:
.sp
.if n \{\
.RS 4
.\}
.nf
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
.fi
.if n \{\
.RE
.\}
.sp
However, the last example would be ambiguous if there is also, say,
.sp
.if n \{\
.RS 4
.\}
.nf
CREATE PROCEDURE do_db_maintenance(IN target_schema text, IN options text) \&.\&.\&.
.fi
.if n \{\
.RE
.\}
.SH "COMPATIBILITY"
.PP
This command conforms to the SQL standard, with these
PostgreSQL
extensions:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
The standard only allows one procedure to be dropped per command\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
The
IF EXISTS
option is an extension\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
The ability to specify argument modes and names is an extension, and the lookup rules differ when modes are given\&.
.RE
.SH "SEE ALSO"
CREATE PROCEDURE (\fBCREATE_PROCEDURE\fR(7)), ALTER PROCEDURE (\fBALTER_PROCEDURE\fR(7)), DROP FUNCTION (\fBDROP_FUNCTION\fR(7)), DROP ROUTINE (\fBDROP_ROUTINE\fR(7))
|
