ALTER FUNCTION

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)

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Synopsis

Parameters

Subcommands

`OWNER TO`

Required privileges

Parameters

`RENAME TO`

Required privileges

Parameters

`SET SCHEMA`

Required privileges

Parameters

Examples

Change the owner of a function

Rename a function

Change the schema of a function

See also

​Required privileges

​Synopsis

​Parameters

​Subcommands

​OWNER TO

​Required privileges

​Parameters

​RENAME TO

​Required privileges

​Parameters

​SET SCHEMA

​Required privileges

​Parameters

​Examples

​Change the owner of a function

​Rename a function

​Change the schema of a function

​See also

Required privileges

Synopsis

Parameters

Subcommands

`OWNER TO`

Required privileges

Parameters

`RENAME TO`

Required privileges

Parameters

`SET SCHEMA`

Required privileges

Parameters

Examples

Change the owner of a function

Rename a function

Change the schema of a function

See also