| --- |
| title: ALTER FUNCTION |
| --- |
| |
| Changes the definition of a function. |
| |
| ## Synopsis<a id="alterfunction__section2"></a> |
| |
| ``` pre |
| ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] ) |
| action [, ... ] [RESTRICT] |
| |
| ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] ) |
| RENAME TO new_name |
| |
| ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] ) |
| OWNER TO new_owner |
| |
| ALTER FUNCTION name ( [ [argmode] [argname] argtype [, ...] ] ) |
| SET SCHEMA new_schema |
| |
| ``` |
| |
| where *action* is one of: |
| |
| ``` pre |
| {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT} |
| {IMMUTABLE | STABLE | VOLATILE} |
| {[EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER} |
| ``` |
| |
| ## Description |
| |
| `ALTER FUNCTION` changes the definition of a function. |
| |
| You must own the function to use `ALTER FUNCTION`. To change a function’s schema, you must also have `CREATE` privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have `CREATE` privilege on the function’s schema. (These restrictions enforce that altering the owner does not do anything you could not do by dropping and recreating the function. However, a superuser can alter ownership of any function anyway.) |
| |
| ## Parameters<a id="alterfunction__section4"></a> |
| |
| *name* |
| The name (optionally schema-qualified) of an existing function. |
| |
| *argmode* |
| The mode of an argument: either `IN`, `OUT`, or `INOUT`. If omitted, the default is `IN`. Note that `ALTER FUNCTION` does not actually pay any attention to `OUT` arguments, since only the input arguments are needed to determine the function's identity. So it is sufficient to list the `IN` and `INOUT` arguments. |
| |
| *argname* |
| The name of an argument. Note that `ALTER FUNCTION` does not actually pay any attention to argument names, since only the argument data types are needed to determine the function's identity. |
| |
| *argtype* |
| The data type(s) of the function's arguments (optionally schema-qualified), if any. |
| |
| *new\_name* |
| The new name of the function. |
| |
| *new\_owner* |
| The new owner of the function. Note that if the function is marked `SECURITY DEFINER`, it will subsequently execute as the new owner. |
| |
| *new\_schema* |
| The new schema for the function. |
| |
| CALLED ON NULL INPUT |
| RETURNS NULL ON NULL INPUT |
| STRICT |
| `CALLED ON NULL INPUT` changes the function so that it will be invoked when some or all of its arguments are null. `RETURNS NULL ON NULL INPUT` or `STRICT` changes the function so that it is not invoked if any of its arguments are null; instead, a null result is assumed automatically. See `CREATE FUNCTION` for more information. |
| |
| IMMUTABLE |
| STABLE |
| VOLATILE |
| Change the volatility of the function to the specified setting. See `CREATE FUNCTION` for details. |
| |
| \[ EXTERNAL \] SECURITY INVOKER |
| \[ EXTERNAL \] SECURITY DEFINER |
| Change whether the function is a security definer or not. The key word `EXTERNAL` is ignored for SQL conformance. See `CREATE FUNCTION` for more information about this capability. |
| |
| RESTRICT |
| Ignored for conformance with the SQL standard. |
| |
| ## Notes |
| |
| HAWQ has limitations on the use of functions defined as `STABLE` or `VOLATILE`. See /3/4 for more information. |
| |
| ## Examples<a id="alterfunction__section6"></a> |
| |
| To rename the function `sqrt` for type `integer` to `square_root`: |
| |
| ``` pre |
| ALTER FUNCTION sqrt(integer) RENAME TO square_root; |
| ``` |
| |
| To change the owner of the function `sqrt` for type `integer` to `joe`: |
| |
| ``` pre |
| ALTER FUNCTION sqrt(integer) OWNER TO joe; |
| ``` |
| |
| To change the *schema* of the function `sqrt` for type `integer` to `math`: |
| |
| ``` pre |
| ALTER FUNCTION sqrt(integer) SET SCHEMA math; |
| ``` |
| |
| ## Compatibility |
| |
| This statement is partially compatible with the `ALTER FUNCTION` statement in the SQL standard. The standard allows more properties of a function to be modified, but does not provide the ability to rename a function, make a function a security definer, or change the owner, schema, or volatility of a function. The standard also requires the `RESTRICT` key word, which is optional in HAWQ. |
| |
| ## See Also |
| |
| /3/4, /3/4 |
| |
| |