1ALTER FUNCTION(7) PostgreSQL 14.3 Documentation ALTER FUNCTION(7)
2
6 ALTER_FUNCTION - change the definition of a function
7
9 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
10 action [ ... ] [ RESTRICT ]
11 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
12 RENAME TO new_name
13 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
14 OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
15 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
16 SET SCHEMA new_schema
17 ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
18 [ NO ] DEPENDS ON EXTENSION extension_name
19 where action is one of:
21 CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
23 IMMUTABLE | STABLE | VOLATILE
24 [ NOT ] LEAKPROOF
25 [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
26 PARALLEL { UNSAFE | RESTRICTED | SAFE }
27 COST execution_cost
28 ROWS result_rows
29 SUPPORT support_function
30 SET configuration_parameter { TO | = } { value | DEFAULT }
31 SET configuration_parameter FROM CURRENT
32 RESET configuration_parameter
33 RESET ALL
34
36 ALTER FUNCTION changes the definition of a function.
37 You must own the function to use ALTER FUNCTION. To change a function's
39 schema, you must also have CREATE privilege on the new schema. To alter
40 the owner, you must also be a direct or indirect member of the new
41 owning role, and that role must have CREATE privilege on the function's
42 schema. (These restrictions enforce that altering the owner doesn't do
43 anything you couldn't do by dropping and recreating the function.
44 However, a superuser can alter ownership of any function anyway.)
45
47 name
48 The name (optionally schema-qualified) of an existing function. If
49 no argument list is specified, the name must be unique in its
50 schema.
51 argmode
53 The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted,
54 the default is IN. Note that ALTER FUNCTION does not actually pay
55 any attention to OUT arguments, since only the input arguments are
56 needed to determine the function's identity. So it is sufficient to
57 list the IN, INOUT, and VARIADIC arguments.
58 argname
60 The name of an argument. Note that ALTER FUNCTION does not actually
61 pay any attention to argument names, since only the argument data
62 types are needed to determine the function's identity.
63 argtype
65 The data type(s) of the function's arguments (optionally
66 schema-qualified), if any.
67 new_name
69 The new name of the function.
70 new_owner
72 The new owner of the function. Note that if the function is marked
73 SECURITY DEFINER, it will subsequently execute as the new owner.
74 new_schema
76 The new schema for the function.
77 DEPENDS ON EXTENSION extension_name
79 NO DEPENDS ON EXTENSION extension_name
80 This form marks the function as dependent on the extension, or no
81 longer dependent on that extension if NO is specified. A function
82 that's marked as dependent on an extension is automatically dropped
83 when the extension is dropped.
84 CALLED ON NULL INPUT
86 RETURNS NULL ON NULL INPUT
87 STRICT
88 CALLED ON NULL INPUT changes the function so that it will be
89 invoked when some or all of its arguments are null. RETURNS NULL
90 ON NULL INPUT or STRICT changes the function so that it is not
91 invoked if any of its arguments are null; instead, a null result is
92 assumed automatically. See CREATE FUNCTION (CREATE_FUNCTION(7)) for
93 more information.
94 IMMUTABLE
96 STABLE
97 VOLATILE
98 Change the volatility of the function to the specified setting. See
99 CREATE FUNCTION (CREATE_FUNCTION(7)) for details.
100 [ EXTERNAL ] SECURITY INVOKER
102 [ EXTERNAL ] SECURITY DEFINER
103 Change whether the function is a security definer or not. The key
104 word EXTERNAL is ignored for SQL conformance. See CREATE FUNCTION
105 (CREATE_FUNCTION(7)) for more information about this capability.
106 PARALLEL
108 Change whether the function is deemed safe for parallelism. See
109 CREATE FUNCTION (CREATE_FUNCTION(7)) for details.
110 LEAKPROOF
112 Change whether the function is considered leakproof or not. See
113 CREATE FUNCTION (CREATE_FUNCTION(7)) for more information about
114 this capability.
115 COST execution_cost
117 Change the estimated execution cost of the function. See CREATE
118 FUNCTION (CREATE_FUNCTION(7)) for more information.
119 ROWS result_rows
121 Change the estimated number of rows returned by a set-returning
122 function. See CREATE FUNCTION (CREATE_FUNCTION(7)) for more
123 information.
124 SUPPORT support_function
126 Set or change the planner support function to use for this
127 function. See Section 38.11 for details. You must be superuser to
128 use this option.
129 This option cannot be used to remove the support function
131 altogether, since it must name a new support function. Use CREATE
132 OR REPLACE FUNCTION if you need to do that.
133 configuration_parameter
135 value
136 Add or change the assignment to be made to a configuration
137 parameter when the function is called. If value is DEFAULT or,
138 equivalently, RESET is used, the function-local setting is removed,
139 so that the function executes with the value present in its
140 environment. Use RESET ALL to clear all function-local settings.
141 SET FROM CURRENT saves the value of the parameter that is current
142 when ALTER FUNCTION is executed as the value to be applied when the
143 function is entered.
144 See SET(7) and Chapter 20 for more information about allowed
146 parameter names and values.
147 RESTRICT
149 Ignored for conformance with the SQL standard.
150
152 To rename the function sqrt for type integer to square_root:
153 ALTER FUNCTION sqrt(integer) RENAME TO square_root;
155 To change the owner of the function sqrt for type integer to joe:
157 ALTER FUNCTION sqrt(integer) OWNER TO joe;
159 To change the schema of the function sqrt for type integer to maths:
161 ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
163 To mark the function sqrt for type integer as being dependent on the
165 extension mathlib:
166 ALTER FUNCTION sqrt(integer) DEPENDS ON EXTENSION mathlib;
168 To adjust the search path that is automatically set for a function:
170 ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
172 To disable automatic setting of search_path for a function:
174 ALTER FUNCTION check_password(text) RESET search_path;
176 The function will now execute with whatever search path is used by its
178 caller.
179
181 This statement is partially compatible with the ALTER FUNCTION
182 statement in the SQL standard. The standard allows more properties of a
183 function to be modified, but does not provide the ability to rename a
184 function, make a function a security definer, attach configuration
185 parameter values to a function, or change the owner, schema, or
186 volatility of a function. The standard also requires the RESTRICT key
187 word, which is optional in PostgreSQL.
188
190 CREATE FUNCTION (CREATE_FUNCTION(7)), DROP FUNCTION (DROP_FUNCTION(7)),
191 ALTER PROCEDURE (ALTER_PROCEDURE(7)), ALTER ROUTINE (ALTER_ROUTINE(7))
192PostgreSQL 14.3 2022 ALTER FUNCTION(7)