ALTER FUNCTION — change the definition of a function
ALTER FUNCTIONname
( [ [argmode
] [argname
]argtype
[, ...] ] )action
[, ... ] [ RESTRICT ] ALTER FUNCTIONname
( [ [argmode
] [argname
]argtype
[, ...] ] ) RENAME TOnew_name
ALTER FUNCTIONname
( [ [argmode
] [argname
]argtype
[, ...] ] ) OWNER TOnew_owner
ALTER FUNCTIONname
( [ [argmode
] [argname
]argtype
[, ...] ] ) SET SCHEMAnew_schema
whereaction
is one of: CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT IMMUTABLE | STABLE | VOLATILE [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
ALTER FUNCTION
changes the definition of a
function.
You must own the function to use ALTER FUNCTION
.
To change a function's schema, you must also have CREATE
privilege on the new schema.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have CREATE
privilege on
the function's schema. (These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the function.
However, a superuser can alter ownership of any function anyway.)
name
The name (optionally schema-qualified) of an existing function.
argmode
The mode of an argument: either IN
, OUT
,
or INOUT
. If omitted, the default is IN
.
Note that ALTER FUNCTION
does not actually pay
any attention to OUT
arguments, since only the input
arguments are needed to determine the function's identity.
So it is sufficient to list the IN
and INOUT
arguments.
argname
The name of an argument.
Note that ALTER FUNCTION
does not actually pay
any attention to argument names, since only the argument data
types are needed to determine the function's identity.
argtype
The data type(s) of the function's arguments (optionally schema-qualified), if any.
new_name
The new name of the function.
new_owner
The new owner of the function. Note that if the function is
marked SECURITY DEFINER
, it will subsequently
execute as the new owner.
new_schema
The new schema for the function.
CALLED ON NULL INPUT
RETURNS NULL ON NULL INPUT
STRICT
CALLED ON NULL INPUT
changes the function so
that it will be invoked when some or all of its arguments are
null. RETURNS NULL ON NULL INPUT
or
STRICT
changes the function so that it is not
invoked if any of its arguments are null; instead, a null result
is assumed automatically. See CREATE FUNCTION for more information.
IMMUTABLE
STABLE
VOLATILE
Change the volatility of the function to the specified setting. See CREATE FUNCTION for details.
[ EXTERNAL ] SECURITY INVOKER
[ EXTERNAL ] SECURITY DEFINER
Change whether the function is a security definer or not. The
key word EXTERNAL
is ignored for SQL
conformance. See CREATE FUNCTION for more information about
this capability.
RESTRICT
Ignored for conformance with the SQL standard.
To rename the function sqrt
for type
integer
to square_root
:
ALTER FUNCTION sqrt(integer) RENAME TO square_root;
To change the owner of the function sqrt
for type
integer
to joe
:
ALTER FUNCTION sqrt(integer) OWNER TO joe;
To change the schema of the function sqrt
for type
integer
to maths
:
ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
This statement is partially compatible with the ALTER
FUNCTION
statement in the SQL standard. The standard allows more
properties of a function to be modified, but does not provide the
ability to rename a function, make a function a security definer,
or change the owner, schema, or volatility of a function. The standard also
requires the RESTRICT
key word, which is optional in
PostgreSQL.