On this page
The ALTER FUNCTION
statement alters a user-defined function.
Note:
The ALTER FUNCTION
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Subcommands
Subcommand | Description |
---|---|
OWNER TO |
Change the owner of a function. |
RENAME TO |
Change the name of a function. |
SET SCHEMA |
Set schema of a function. |
Required privileges
- To alter a function, a user must own the function.
- To alter a function, a user must have
DROP
privilege on the schema of the function. - To alter the owner of a function, the new owner must have
CREATE
privilege on the schema of the function. - To change the schema of a function, a user must have
CREATE
privilege on the new schema.
Synopsis
Parameters
Parameter | Description |
---|---|
function_with_argtypes |
The name of the function, with optional function arguments to alter. |
name |
The new name of the function. |
role_spec |
The role to set as the owner of the function. |
schema_name |
The name of the new schema. |
Examples
Rename a function
The following statement defines a function that computes the sum of two arguments:
> CREATE FUNCTION add(a INT, b INT) RETURNS INT IMMUTABLE LEAKPROOF LANGUAGE SQL AS 'SELECT $1 + $2';
The following statement renames the add
function to sum
:
> ALTER FUNCTION add(a INT, b INT) RENAME TO sum;
> SHOW CREATE FUNCTION sum;
The default schema for the function sum
is public
:
function_name | create_statement
----------------+---------------------------------------------------
sum | CREATE FUNCTION public.sum(IN a INT8, IN b INT8)
| RETURNS INT8
| IMMUTABLE
| LEAKPROOF
| CALLED ON NULL INPUT
| LANGUAGE SQL
| AS $$
| SELECT $1 + $2;
| $$
(1 row)
Since sum
is a built-in function, you must specify the public
schema to invoke your user-defined sum
function:
> SELECT public.sum(1,2);
sum
-------
3
If you do not specify public
, you will get an error when invoking a built-in function:
SELECT sum(1,2);
ERROR: ambiguous function class on sum
SQLSTATE: 42725