ALTER FUNCTION

On this page

The ALTER FUNCTION statement alters a user-defined function.

Subcommands

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

Parameters

Examples

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 sum is a built-in function, 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, you will get an error when invoking a built-in function:

SELECT sum(1,2);

ERROR: ambiguous function class on sum
SQLSTATE: 42725

See also

• User-Defined Functions
• CREATE FUNCTION
• DROP FUNCTION
• SQL Statements
• Online Schema Changes

Was this helpful?