1ALTER PROCEDURE(7) PostgreSQL 15.4 Documentation ALTER PROCEDURE(7)
2
6 ALTER_PROCEDURE - change the definition of a procedure
7
9 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
10 action [ ... ] [ RESTRICT ]
11 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
12 RENAME TO new_name
13 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
14 OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
15 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
16 SET SCHEMA new_schema
17 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
18 [ NO ] DEPENDS ON EXTENSION extension_name
19 where action is one of:
21 [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
23 SET configuration_parameter { TO | = } { value | DEFAULT }
24 SET configuration_parameter FROM CURRENT
25 RESET configuration_parameter
26 RESET ALL
27
29 ALTER PROCEDURE changes the definition of a procedure.
30 You must own the procedure to use ALTER PROCEDURE. To change a
32 procedure's schema, you must also have CREATE privilege on the new
33 schema. To alter the owner, you must also be a direct or indirect
34 member of the new owning role, and that role must have CREATE privilege
35 on the procedure's schema. (These restrictions enforce that altering
36 the owner doesn't do anything you couldn't do by dropping and
37 recreating the procedure. However, a superuser can alter ownership of
38 any procedure anyway.)
39
41 name
42 The name (optionally schema-qualified) of an existing procedure. If
43 no argument list is specified, the name must be unique in its
44 schema.
45 argmode
47 The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted,
48 the default is IN.
49 argname
51 The name of an argument. Note that ALTER PROCEDURE does not
52 actually pay any attention to argument names, since only the
53 argument data types are used to determine the procedure's identity.
54 argtype
56 The data type(s) of the procedure's arguments (optionally
57 schema-qualified), if any. See DROP PROCEDURE (DROP_PROCEDURE(7))
58 for the details of how the procedure is looked up using the
59 argument data type(s).
60 new_name
62 The new name of the procedure.
63 new_owner
65 The new owner of the procedure. Note that if the procedure is
66 marked SECURITY DEFINER, it will subsequently execute as the new
67 owner.
68 new_schema
70 The new schema for the procedure.
71 extension_name
73 This form marks the procedure as dependent on the extension, or no
74 longer dependent on the extension if NO is specified. A procedure
75 that's marked as dependent on an extension is dropped when the
76 extension is dropped, even if cascade is not specified. A procedure
77 can depend upon multiple extensions, and will be dropped when any
78 one of those extensions is dropped.
79 [ EXTERNAL ] SECURITY INVOKER
81 [ EXTERNAL ] SECURITY DEFINER
82 Change whether the procedure is a security definer or not. The key
83 word EXTERNAL is ignored for SQL conformance. See CREATE PROCEDURE
84 (CREATE_PROCEDURE(7)) for more information about this capability.
85 configuration_parameter
87 value
88 Add or change the assignment to be made to a configuration
89 parameter when the procedure is called. If value is DEFAULT or,
90 equivalently, RESET is used, the procedure-local setting is
91 removed, so that the procedure executes with the value present in
92 its environment. Use RESET ALL to clear all procedure-local
93 settings. SET FROM CURRENT saves the value of the parameter that
94 is current when ALTER PROCEDURE is executed as the value to be
95 applied when the procedure is entered.
96 See SET(7) and Chapter 20 for more information about allowed
98 parameter names and values.
99 RESTRICT
101 Ignored for conformance with the SQL standard.
102
104 To rename the procedure insert_data with two arguments of type integer
105 to insert_record:
106 ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
108 To change the owner of the procedure insert_data with two arguments of
110 type integer to joe:
111 ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
113 To change the schema of the procedure insert_data with two arguments of
115 type integer to accounting:
116 ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
118 To mark the procedure insert_data(integer, integer) as being dependent
120 on the extension myext:
121 ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
123 To adjust the search path that is automatically set for a procedure:
125 ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
127 To disable automatic setting of search_path for a procedure:
129 ALTER PROCEDURE check_password(text) RESET search_path;
131 The procedure will now execute with whatever search path is used by its
133 caller.
134
136 This statement is partially compatible with the ALTER PROCEDURE
137 statement in the SQL standard. The standard allows more properties of a
138 procedure to be modified, but does not provide the ability to rename a
139 procedure, make a procedure a security definer, attach configuration
140 parameter values to a procedure, or change the owner, schema, or
141 volatility of a procedure. The standard also requires the RESTRICT key
142 word, which is optional in PostgreSQL.
143
145 CREATE PROCEDURE (CREATE_PROCEDURE(7)), DROP PROCEDURE
146 (DROP_PROCEDURE(7)), ALTER FUNCTION (ALTER_FUNCTION(7)), ALTER ROUTINE
147 (ALTER_ROUTINE(7))
148PostgreSQL 15.4 2023 ALTER PROCEDURE(7)