'\" t .\" Title: ALTER PROCEDURE .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets vsnapshot .\" Date: 2024 .\" Manual: PostgreSQL 16.3 Documentation .\" Source: PostgreSQL 16.3 .\" Language: English .\" .TH "ALTER PROCEDURE" "7" "2024" "PostgreSQL 16.3" "PostgreSQL 16.3 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" ALTER_PROCEDURE \- change the definition of a procedure .SH "SYNOPSIS" .sp .nf ALTER PROCEDURE \fIname\fR [ ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) ] \fIaction\fR [ \&.\&.\&. ] [ RESTRICT ] ALTER PROCEDURE \fIname\fR [ ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) ] RENAME TO \fInew_name\fR ALTER PROCEDURE \fIname\fR [ ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) ] OWNER TO { \fInew_owner\fR | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER PROCEDURE \fIname\fR [ ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) ] SET SCHEMA \fInew_schema\fR ALTER PROCEDURE \fIname\fR [ ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) ] [ NO ] DEPENDS ON EXTENSION \fIextension_name\fR where \fIaction\fR is one of: [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET \fIconfiguration_parameter\fR { TO | = } { \fIvalue\fR | DEFAULT } SET \fIconfiguration_parameter\fR FROM CURRENT RESET \fIconfiguration_parameter\fR RESET ALL .fi .SH "DESCRIPTION" .PP \fBALTER PROCEDURE\fR changes the definition of a procedure\&. .PP You must own the procedure to use \fBALTER PROCEDURE\fR\&. To change a procedure\*(Aqs schema, you must also have CREATE privilege on the new schema\&. To alter the owner, you must be able to SET ROLE to the new owning role, and that role must have CREATE privilege on the procedure\*(Aqs schema\&. (These restrictions enforce that altering the owner doesn\*(Aqt do anything you couldn\*(Aqt do by dropping and recreating the procedure\&. However, a superuser can alter ownership of any procedure anyway\&.) .SH "PARAMETERS" .PP \fIname\fR .RS 4 The name (optionally schema\-qualified) of an existing procedure\&. If no argument list is specified, the name must be unique in its schema\&. .RE .PP \fIargmode\fR .RS 4 The mode of an argument: IN, OUT, INOUT, or VARIADIC\&. If omitted, the default is IN\&. .RE .PP \fIargname\fR .RS 4 The name of an argument\&. Note that \fBALTER 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 DROP PROCEDURE (\fBDROP_PROCEDURE\fR(7)) for the details of how the procedure is looked up using the argument data type(s)\&. .RE .PP \fInew_name\fR .RS 4 The new name of the procedure\&. .RE .PP \fInew_owner\fR .RS 4 The new owner of the procedure\&. Note that if the procedure is marked SECURITY DEFINER, it will subsequently execute as the new owner\&. .RE .PP \fInew_schema\fR .RS 4 The new schema for the procedure\&. .RE .PP \fIextension_name\fR .RS 4 This form marks the procedure as dependent on the extension, or no longer dependent on the extension if NO is specified\&. A procedure that\*(Aqs marked as dependent on an extension is dropped when the extension is dropped, even if cascade is not specified\&. A procedure can depend upon multiple extensions, and will be dropped when any one of those extensions is dropped\&. .RE .PP [ EXTERNAL ] SECURITY INVOKER .br [ EXTERNAL ] SECURITY DEFINER .RS 4 Change whether the procedure is a security definer or not\&. The key word EXTERNAL is ignored for SQL conformance\&. See CREATE PROCEDURE (\fBCREATE_PROCEDURE\fR(7)) for more information about this capability\&. .RE .PP \fIconfiguration_parameter\fR .br \fIvalue\fR .RS 4 Add or change the assignment to be made to a configuration parameter when the procedure is called\&. If \fIvalue\fR is DEFAULT or, equivalently, RESET is used, the procedure\-local setting is removed, so that the procedure executes with the value present in its environment\&. Use RESET ALL to clear all procedure\-local settings\&. SET FROM CURRENT saves the value of the parameter that is current when \fBALTER PROCEDURE\fR is executed as the value to be applied when the procedure is entered\&. .sp See \fBSET\fR(7) and Chapter\ \&20 for more information about allowed parameter names and values\&. .RE .PP RESTRICT .RS 4 Ignored for conformance with the SQL standard\&. .RE .SH "EXAMPLES" .PP To rename the procedure insert_data with two arguments of type integer to insert_record: .sp .if n \{\ .RS 4 .\} .nf ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record; .fi .if n \{\ .RE .\} .PP To change the owner of the procedure insert_data with two arguments of type integer to joe: .sp .if n \{\ .RS 4 .\} .nf ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe; .fi .if n \{\ .RE .\} .PP To change the schema of the procedure insert_data with two arguments of type integer to accounting: .sp .if n \{\ .RS 4 .\} .nf ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting; .fi .if n \{\ .RE .\} .PP To mark the procedure insert_data(integer, integer) as being dependent on the extension myext: .sp .if n \{\ .RS 4 .\} .nf ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext; .fi .if n \{\ .RE .\} .PP To adjust the search path that is automatically set for a procedure: .sp .if n \{\ .RS 4 .\} .nf ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp; .fi .if n \{\ .RE .\} .PP To disable automatic setting of \fIsearch_path\fR for a procedure: .sp .if n \{\ .RS 4 .\} .nf ALTER PROCEDURE check_password(text) RESET search_path; .fi .if n \{\ .RE .\} .sp The procedure will now execute with whatever search path is used by its caller\&. .SH "COMPATIBILITY" .PP This statement is partially compatible with the \fBALTER PROCEDURE\fR statement in the SQL standard\&. The standard allows more properties of a procedure to be modified, but does not provide the ability to rename a procedure, make a procedure a security definer, attach configuration parameter values to a procedure, or change the owner, schema, or volatility of a procedure\&. The standard also requires the RESTRICT key word, which is optional in PostgreSQL\&. .SH "SEE ALSO" CREATE PROCEDURE (\fBCREATE_PROCEDURE\fR(7)), DROP PROCEDURE (\fBDROP_PROCEDURE\fR(7)), ALTER FUNCTION (\fBALTER_FUNCTION\fR(7)), ALTER ROUTINE (\fBALTER_ROUTINE\fR(7))