The ALTER FUNCTION
statement applies a schema change to a user-defined function.
Required privileges
Refer to the respective subcommands.
Synopsis
Parameters
Parameter | Description |
---|---|
function_with_argtypes |
The name of the function, with optional function arguments to alter. |
For more information about the statement syntax, see User-Defined Functions.
Additional parameters are documented for the respective subcommands.
Subcommands
Subcommand | Description |
---|---|
OWNER TO |
Change the owner of a function. |
RENAME TO |
Change the name of a function. |
SET SCHEMA |
Change the schema of a function. |
OWNER TO
ALTER FUNCTION ... OWNER TO
is used to change the owner of a function.
Required privileges
- To alter the owner of a function, the new owner must have
CREATE
privilege on the schema of the function. - 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.
Parameters
Parameter | Description |
---|---|
role_spec |
The role to set as the owner of the function. |
For usage, see Synopsis.
RENAME TO
ALTER FUNCTION ... RENAME TO
changes the name 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.
Parameters
Parameter | Description |
---|---|
function_new_name |
The new name of the function. |
For usage, see Synopsis.
SET SCHEMA
ALTER FUNCTION ... SET SCHEMA
changes the schema of a function.
CockroachDB supports SET SCHEMA
as an alias for setting the search_path
session variable.
Required privileges
- To change the schema of a function, a user must have
CREATE
privilege on the new schema. - 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.
Parameters
Parameter | Description |
---|---|
schema_name |
The name of the new schema for the function. |
For usage, see Synopsis.
Examples
Change the owner of a function
Suppose that the current owner of a sq
function is root
and you want to change the owner to a new user named max
.
ALTER FUNCTION sq OWNER TO max;
To verify that the owner is now max
, run a join query against the pg_catalog.pg_proc
and pg_catalog.pg_roles
tables:
SELECT rolname FROM pg_catalog.pg_proc f
JOIN pg_catalog.pg_roles r ON f.proowner = r.oid
WHERE proname = 'sq';
rolname
-----------
max
(1 row)
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 there is also a built-in function named sum
, 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
when invoking a user-defined function, you will get an error when invoking a built-in function with the same name:
SELECT sum(1,2);
ERROR: ambiguous function class on sum
SQLSTATE: 42725
Change the schema of a function
Suppose you want to add the user-defined sum
function from the preceding example to a new schema called cockroach_labs
.
By default, unqualified functions created in the database belong to the public
schema:
SHOW CREATE FUNCTION public.sum;
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)
If the new schema does not already exist, create it:
CREATE SCHEMA IF NOT EXISTS cockroach_labs;
Then, change the function's schema:
ALTER FUNCTION public.sum SET SCHEMA cockroach_labs;
SHOW CREATE FUNCTION cockroach_labs.sum;
function_name | create_statement
----------------+-----------------------------------------------------------
sum | CREATE FUNCTION cockroach_labs.sum(IN a INT8, IN b INT8)
| RETURNS INT8
| IMMUTABLE
| LEAKPROOF
| CALLED ON NULL INPUT
| LANGUAGE SQL
| AS $$
| SELECT $1 + $2;
| $$
(1 row)