ALTER FUNCTION

The ALTER FUNCTION applies a to a .

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 .

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 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 the function.
• To alter a function, a user must have DROP 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 the function.
• To alter a function, a user must have DROP 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 of a function.

CockroachDB supports SET SCHEMA as an .

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 the function.
• To alter a function, a user must have DROP 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 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, 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 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