> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# ALTER FUNCTION

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `ALTER FUNCTION` <InternalLink path="sql-statements">statement</InternalLink> applies a <InternalLink path="online-schema-changes">schema change</InternalLink> to a <InternalLink path="user-defined-functions">user-defined function</InternalLink>.

## Required privileges

Refer to the respective [subcommands](#subcommands).

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/URZZsrJ2y-tKyo7i/images/sql-diagrams/v25.1/alter_func.svg?fit=max&auto=format&n=URZZsrJ2y-tKyo7i&q=85&s=8684251af421a035e6a275d693e8bcb9" alt="alter_func syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="741" height="711" data-path="images/sql-diagrams/v25.1/alter_func.svg" />

## 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 <InternalLink path="user-defined-functions#overview">User-Defined Functions</InternalLink>.

Additional parameters are documented for the respective [subcommands](#subcommands).

## Subcommands

| Subcommand                  | Description                                                                              |
| --------------------------- | ---------------------------------------------------------------------------------------- |
| [`OWNER TO`](#owner-to)     | Change the owner of a function.                                                          |
| [`RENAME TO`](#rename-to)   | Change the name of a function.                                                           |
| [`SET SCHEMA`](#set-schema) | Change the <InternalLink path="sql-name-resolution">schema</InternalLink> 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 <InternalLink path="security-reference/authorization#object-ownership">own</InternalLink> the function.
* To alter a function, a user must have `DROP` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> 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](#synopsis).

### `RENAME TO`

`ALTER FUNCTION ... RENAME TO` changes the name of a function.

#### Required privileges

* To alter a function, a user must <InternalLink path="security-reference/authorization#object-ownership">own</InternalLink> the function.
* To alter a function, a user must have `DROP` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the schema of the function.

#### Parameters

| Parameter           | Description                   |
| ------------------- | ----------------------------- |
| `function_new_name` | The new name of the function. |

For usage, see [Synopsis](#synopsis).

### `SET SCHEMA`

`ALTER FUNCTION ... SET SCHEMA` changes the <InternalLink path="sql-name-resolution">schema</InternalLink> of a function.

<Note>
  CockroachDB supports `SET SCHEMA` as an <InternalLink path="set-vars#supported-variables">alias for setting the `search_path` session variable</InternalLink>.
</Note>

#### 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 <InternalLink path="security-reference/authorization#object-ownership">own</InternalLink> the function.
* To alter a function, a user must have `DROP` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the schema of the function.

#### Parameters

| Parameter     | Description                                  |
| ------------- | -------------------------------------------- |
| `schema_name` | The name of the new schema for the function. |

For usage, see [Synopsis](#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`.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER FUNCTION add(a INT, b INT) RENAME TO sum;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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 <InternalLink path="functions-and-operators#aggregate-functions">built-in function</InternalLink> named `sum`, you must specify the `public` schema to invoke your user-defined `sum` function:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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](#rename-a-function) to a new schema called `cockroach_labs`.

By default, <InternalLink path="sql-name-resolution#lookup-with-unqualified-names">unqualified functions</InternalLink> created in the database belong to the `public` schema:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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, <InternalLink path="create-schema">create it</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE SCHEMA IF NOT EXISTS cockroach_labs;
```

Then, change the function's schema:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER FUNCTION public.sum SET SCHEMA cockroach_labs;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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

* <InternalLink path="user-defined-functions">User-Defined Functions</InternalLink>
* <InternalLink path="create-function">`CREATE FUNCTION`</InternalLink>
* <InternalLink path="drop-function">`DROP FUNCTION`</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>
* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>