'\" t .\" Title: DROP PROCEDURE .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2024 .\" Manual: PostgreSQL 16.2 Documentation .\" Source: PostgreSQL 16.2 .\" Language: English .\" .TH "DROP PROCEDURE" "7" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 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))