1ALTER FUNCTION(7) PostgreSQL 15.4 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 dropped when the
83 extension is dropped, even if CASCADE is not specified. A function
84 can depend upon multiple extensions, and will be dropped when any
85 one of those extensions is dropped.
86 CALLED ON NULL INPUT
88 RETURNS NULL ON NULL INPUT
89 STRICT
90 CALLED ON NULL INPUT changes the function so that it will be
91 invoked when some or all of its arguments are null. RETURNS NULL
92 ON NULL INPUT or STRICT changes the function so that it is not
93 invoked if any of its arguments are null; instead, a null result is
94 assumed automatically. See CREATE FUNCTION (CREATE_FUNCTION(7)) for
95 more information.
96 IMMUTABLE
98 STABLE
99 VOLATILE
100 Change the volatility of the function to the specified setting. See
101 CREATE FUNCTION (CREATE_FUNCTION(7)) for details.
102 [ EXTERNAL ] SECURITY INVOKER
104 [ EXTERNAL ] SECURITY DEFINER
105 Change whether the function is a security definer or not. The key
106 word EXTERNAL is ignored for SQL conformance. See CREATE FUNCTION
107 (CREATE_FUNCTION(7)) for more information about this capability.
108 PARALLEL
110 Change whether the function is deemed safe for parallelism. See
111 CREATE FUNCTION (CREATE_FUNCTION(7)) for details.
112 LEAKPROOF
114 Change whether the function is considered leakproof or not. See
115 CREATE FUNCTION (CREATE_FUNCTION(7)) for more information about
116 this capability.
117 COST execution_cost
119 Change the estimated execution cost of the function. See CREATE
120 FUNCTION (CREATE_FUNCTION(7)) for more information.
121 ROWS result_rows
123 Change the estimated number of rows returned by a set-returning
124 function. See CREATE FUNCTION (CREATE_FUNCTION(7)) for more
125 information.
126 SUPPORT support_function
128 Set or change the planner support function to use for this
129 function. See Section 38.11 for details. You must be superuser to
130 use this option.
131 This option cannot be used to remove the support function
133 altogether, since it must name a new support function. Use CREATE
134 OR REPLACE FUNCTION if you need to do that.
135 configuration_parameter
137 value
138 Add or change the assignment to be made to a configuration
139 parameter when the function is called. If value is DEFAULT or,
140 equivalently, RESET is used, the function-local setting is removed,
141 so that the function executes with the value present in its
142 environment. Use RESET ALL to clear all function-local settings.
143 SET FROM CURRENT saves the value of the parameter that is current
144 when ALTER FUNCTION is executed as the value to be applied when the
145 function is entered.
146 See SET(7) and Chapter 20 for more information about allowed
148 parameter names and values.
149 RESTRICT
151 Ignored for conformance with the SQL standard.
152
154 To rename the function sqrt for type integer to square_root:
155 ALTER FUNCTION sqrt(integer) RENAME TO square_root;
157 To change the owner of the function sqrt for type integer to joe:
159 ALTER FUNCTION sqrt(integer) OWNER TO joe;
161 To change the schema of the function sqrt for type integer to maths:
163 ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
165 To mark the function sqrt for type integer as being dependent on the
167 extension mathlib:
168 ALTER FUNCTION sqrt(integer) DEPENDS ON EXTENSION mathlib;
170 To adjust the search path that is automatically set for a function:
172 ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
174 To disable automatic setting of search_path for a function:
176 ALTER FUNCTION check_password(text) RESET search_path;
178 The function will now execute with whatever search path is used by its
180 caller.
181
183 This statement is partially compatible with the ALTER FUNCTION
184 statement in the SQL standard. The standard allows more properties of a
185 function to be modified, but does not provide the ability to rename a
186 function, make a function a security definer, attach configuration
187 parameter values to a function, or change the owner, schema, or
188 volatility of a function. The standard also requires the RESTRICT key
189 word, which is optional in PostgreSQL.
190
192 CREATE FUNCTION (CREATE_FUNCTION(7)), DROP FUNCTION (DROP_FUNCTION(7)),
193 ALTER PROCEDURE (ALTER_PROCEDURE(7)), ALTER ROUTINE (ALTER_ROUTINE(7))
194PostgreSQL 15.4 2023 ALTER FUNCTION(7)