1ALTER FUNCTION(7) SQL Commands ALTER FUNCTION(7)
2
6 ALTER FUNCTION - change the definition of a function
7
10 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
11 action [ ... ] [ RESTRICT ]
12 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
13 RENAME TO new_name
14 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
15 OWNER TO new_owner
16 ALTER FUNCTION name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
17 SET SCHEMA new_schema
18 where action is one of:
20 CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
22 IMMUTABLE | STABLE | VOLATILE
23 [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
24 COST execution_cost
25 ROWS result_rows
26 SET configuration_parameter { TO | = } { value | DEFAULT }
27 SET configuration_parameter FROM CURRENT
28 RESET configuration_parameter
29 RESET ALL
30
33 ALTER FUNCTION changes the definition of a function.
34 You must own the function to use ALTER FUNCTION. To change a func‐
36 tion's schema, you must also have CREATE privilege on the new schema.
37 To alter the owner, you must also be a direct or indirect member of the
38 new owning role, and that role must have CREATE privilege on the func‐
39 tion's schema. (These restrictions enforce that altering the owner
40 doesn't do anything you couldn't do by dropping and recreating the
41 function. However, a superuser can alter ownership of any function
42 anyway.)
43
45 name The name (optionally schema-qualified) of an existing function.
46 argmode
48 The mode of an argument: IN, OUT, INOUT, or VARIADIC. If omit‐
49 ted, the default is IN. Note that ALTER FUNCTION does not actu‐
50 ally pay any attention to OUT arguments, since only the input
51 arguments are needed to determine the function's identity. So
52 it is sufficient to list the IN, INOUT, and VARIADIC arguments.
53 argname
55 The name of an argument. Note that ALTER FUNCTION does not
56 actually pay any attention to argument names, since only the
57 argument data types are needed to determine the function's iden‐
58 tity.
59 argtype
61 The data type(s) of the function's arguments (optionally schema-
62 qualified), if any.
63 new_name
65 The new name of the function.
66 new_owner
68 The new owner of the function. Note that if the function is
69 marked SECURITY DEFINER, it will subsequently execute as the new
70 owner.
71 new_schema
73 The new schema for the function.
74 CALLED ON NULL INPUT
76 RETURNS NULL ON NULL INPUT
78 STRICT CALLED ON NULL INPUT changes the function so that it will be
80 invoked when some or all of its arguments are null. RETURNS NULL
81 ON NULL INPUT or STRICT changes the function so that it is not
82 invoked if any of its arguments are null; instead, a null result
83 is assumed automatically. See CREATE FUNCTION [create_func‐
84 tion(7)] for more information.
85 IMMUTABLE
87 STABLE
89 VOLATILE
91 Change the volatility of the function to the specified setting.
92 See CREATE FUNCTION [create_function(7)] for details.
93 [ EXTERNAL ] SECURITY INVOKER
95 [ EXTERNAL ] SECURITY DEFINER
97 Change whether the function is a security definer or not. The
98 key word EXTERNAL is ignored for SQL conformance. See CREATE
99 FUNCTION [create_function(7)] for more information about this
100 capability.
101 COST execution_cost
103 Change the estimated execution cost of the function. See CREATE
104 FUNCTION [create_function(7)] for more information.
105 ROWS result_rows
107 Change the estimated number of rows returned by a set-returning
108 function. See CREATE FUNCTION [create_function(7)] for more
109 information.
110 configuration_parameter
112 value Add or change the assignment to be made to a configuration
114 parameter when the function is called. If value is DEFAULT or,
115 equivalently, RESET is used, the function-local setting is
116 removed, so that the function executes with the value present in
117 its environment. Use RESET ALL to clear all function-local set‐
118 tings. SET FROM CURRENT saves the session's current value of
119 the parameter as the value to be applied when the function is
120 entered.
121 See SET [set(7)] and in the documentation for more information
123 about allowed parameter names and values.
124 RESTRICT
126 Ignored for conformance with the SQL standard.
127
129 To rename the function sqrt for type integer to square_root:
130 ALTER FUNCTION sqrt(integer) RENAME TO square_root;
132 To change the owner of the function sqrt for type integer to joe:
135 ALTER FUNCTION sqrt(integer) OWNER TO joe;
137 To change the schema of the function sqrt for type integer to maths:
140 ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
142 To adjust the search path that is automatically set for a function:
145 ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
147 To disable automatic setting of search_path for a function:
150 ALTER FUNCTION check_password(text) RESET search_path;
152 The function will now execute with whatever search path is used by its
154 caller.
155
157 This statement is partially compatible with the ALTER FUNCTION state‐
158 ment in the SQL standard. The standard allows more properties of a
159 function to be modified, but does not provide the ability to rename a
160 function, make a function a security definer, attach configuration
161 parameter values to a function, or change the owner, schema, or
162 volatility of a function. The standard also requires the RESTRICT key
163 word, which is optional in PostgreSQL.
164
166 CREATE FUNCTION [create_function(7)], DROP FUNCTION [drop_function(7)]
167SQL - Language Statements 2011-09-22 ALTER FUNCTION(7)