1ALTER PROCEDURE(7) PostgreSQL 11.6 Documentation ALTER PROCEDURE(7)
2
3
4
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_USER | SESSION_USER }
15 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
16 SET SCHEMA new_schema
17 ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
18 DEPENDS ON EXTENSION extension_name
19
20 where action is one of:
21
22 [ 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
31 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
46 argmode
47 The mode of an argument: IN or VARIADIC. If omitted, the default is
48 IN.
49
50 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 needed to determine the procedure's
54 identity.
55
56 argtype
57 The data type(s) of the procedure's arguments (optionally
58 schema-qualified), if any.
59
60 new_name
61 The new name of the procedure.
62
63 new_owner
64 The new owner of the procedure. Note that if the procedure is
65 marked SECURITY DEFINER, it will subsequently execute as the new
66 owner.
67
68 new_schema
69 The new schema for the procedure.
70
71 extension_name
72 The name of the extension that the procedure is to depend on.
73
74 [ EXTERNAL ] SECURITY INVOKER
75 [ EXTERNAL ] SECURITY DEFINER
76 Change whether the procedure is a security definer or not. The key
77 word EXTERNAL is ignored for SQL conformance. See CREATE PROCEDURE
78 (CREATE_PROCEDURE(7)) for more information about this capability.
79
80 configuration_parameter
81 value
82 Add or change the assignment to be made to a configuration
83 parameter when the procedure is called. If value is DEFAULT or,
84 equivalently, RESET is used, the procedure-local setting is
85 removed, so that the procedure executes with the value present in
86 its environment. Use RESET ALL to clear all procedure-local
87 settings. SET FROM CURRENT saves the value of the parameter that
88 is current when ALTER PROCEDURE is executed as the value to be
89 applied when the procedure is entered.
90
91 See SET(7) and Chapter 19 for more information about allowed
92 parameter names and values.
93
94 RESTRICT
95 Ignored for conformance with the SQL standard.
96
98 To rename the procedure insert_data with two arguments of type integer
99 to insert_record:
100
101 ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
102
103 To change the owner of the procedure insert_data with two arguments of
104 type integer to joe:
105
106 ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
107
108 To change the schema of the procedure insert_data with two arguments of
109 type integer to accounting:
110
111 ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
112
113 To mark the procedure insert_data(integer, integer) as being dependent
114 on the extension myext:
115
116 ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
117
118 To adjust the search path that is automatically set for a procedure:
119
120 ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
121
122 To disable automatic setting of search_path for a procedure:
123
124 ALTER PROCEDURE check_password(text) RESET search_path;
125
126 The procedure will now execute with whatever search path is used by its
127 caller.
128
130 This statement is partially compatible with the ALTER PROCEDURE
131 statement in the SQL standard. The standard allows more properties of a
132 procedure to be modified, but does not provide the ability to rename a
133 procedure, make a procedure a security definer, attach configuration
134 parameter values to a procedure, or change the owner, schema, or
135 volatility of a procedure. The standard also requires the RESTRICT key
136 word, which is optional in PostgreSQL.
137
139 CREATE PROCEDURE (CREATE_PROCEDURE(7)), DROP PROCEDURE
140 (DROP_PROCEDURE(7)), ALTER FUNCTION (ALTER_FUNCTION(7)), ALTER ROUTINE
141 (ALTER_ROUTINE(7))
142
143
144
145PostgreSQL 11.6 2019 ALTER PROCEDURE(7)