ALTER FUNCTION

On this page

The ALTER FUNCTION statement applies a schema change to a user-defined function.

Required privileges

Refer to the respective subcommands.

Synopsis

Parameters

For more information about the statement syntax, see User-Defined Functions.

Additional parameters are documented for the respective subcommands.

Subcommands

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

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

For usage, see Synopsis.

SET SCHEMA

ALTER FUNCTION ... SET SCHEMA changes the schema of a function.

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

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) 

See also

• User-Defined Functions
• CREATE FUNCTION
• DROP FUNCTION
• SQL Statements
• Online Schema Changes