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