1ALTER FUNCTION(7)                SQL Commands                ALTER FUNCTION(7)
2
3
4

NAME

6       ALTER FUNCTION - change the definition of a function
7
8

SYNOPSIS

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
19       where action is one of:
20
21           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
31

DESCRIPTION

33       ALTER FUNCTION changes the definition of a function.
34
35       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

PARAMETERS

45       name   The name (optionally schema-qualified) of an existing function.
46
47       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
54       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
60       argtype
61              The data type(s) of the function's arguments (optionally schema-
62              qualified), if any.
63
64       new_name
65              The new name of the function.
66
67       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
72       new_schema
73              The new schema for the function.
74
75       CALLED ON NULL INPUT
76
77       RETURNS NULL ON NULL INPUT
78
79       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
86       IMMUTABLE
87
88       STABLE
89
90       VOLATILE
91              Change  the volatility of the function to the specified setting.
92              See CREATE FUNCTION [create_function(7)] for details.
93
94       [ EXTERNAL ] SECURITY INVOKER
95
96       [ 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
102       COST execution_cost
103              Change the estimated execution cost of the function.  See CREATE
104              FUNCTION [create_function(7)] for more information.
105
106       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
111       configuration_parameter
112
113       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
122              See  SET  [set(7)] and in the documentation for more information
123              about allowed parameter names and values.
124
125       RESTRICT
126              Ignored for conformance with the SQL standard.
127

EXAMPLES

129       To rename the function sqrt for type integer to square_root:
130
131       ALTER FUNCTION sqrt(integer) RENAME TO square_root;
132
133
134       To change the owner of the function sqrt for type integer to joe:
135
136       ALTER FUNCTION sqrt(integer) OWNER TO joe;
137
138
139       To change the schema of the function sqrt for type integer to maths:
140
141       ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
142
143
144       To adjust the search path that is automatically set for a function:
145
146       ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
147
148
149       To disable automatic setting of search_path for a function:
150
151       ALTER FUNCTION check_password(text) RESET search_path;
152
153       The function will now execute with whatever search path is used by  its
154       caller.
155

COMPATIBILITY

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

SEE ALSO

166       CREATE FUNCTION [create_function(7)], DROP FUNCTION [drop_function(7)]
167
168
169
170SQL - Language Statements         2014-02-17                 ALTER FUNCTION(7)
Impressum