ALTER FUNCTION

On this page Carat arrow pointing down

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

Required privileges

Refer to the respective subcommands.

Synopsis

ALTER FUNCTION function_with_paramtypes CALLED RETURNS NULL ON NULL INPUT STRICT IMMUTABLE STABLE VOLATILE EXTERNAL SECURITY DEFINER INVOKER NOT LEAKPROOF RESTRICT RENAME TO function_new_name OWNER TO role_spec SET SCHEMA schema_name NO DEPENDS ON EXTENSION name

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.

Note:

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.

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

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

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 there is also a built-in function named sum, 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 when invoking a user-defined function, you will get an error when invoking a built-in function with the same name:

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

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

icon/buttons/copy
CREATE SCHEMA IF NOT EXISTS cockroach_labs;

Then, change the function's schema:

icon/buttons/copy
ALTER FUNCTION public.sum SET SCHEMA cockroach_labs;
icon/buttons/copy
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


Yes No
On this page

Yes No