ALTER FUNCTION

On this page Carat arrow pointing down

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

ALTER FUNCTION function_with_argtypes CALLED RETURNS NULL ON NULL INPUT STRICT IMMUTABLE STABLE VOLATILE NOT LEAKPROOF RESTRICT RENAME TO NO DEPENDS ON EXTENSION name OWNER TO role_spec SET SCHEMA schema_name

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:

icon/buttons/copy
> 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:

icon/buttons/copy
> ALTER FUNCTION add(a INT, b INT) RENAME TO sum;
icon/buttons/copy
> 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:

icon/buttons/copy
> SELECT public.sum(1,2);
  sum
-------
    3

If you do not specify public, you will get an error when invoking a built-in function:

icon/buttons/copy
SELECT sum(1,2);
ERROR: ambiguous function class on sum
SQLSTATE: 42725

See also


Yes No
On this page

Yes No