Skip to main content

ALTER FUNCTION

Redefines a function.

Syntax​

ALTER FUNCTION <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ] 
   <action> [, ... ] [RESTRICT]

ALTER FUNCTION <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   RENAME TO <new_name>

ALTER FUNCTION <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   OWNER TO { <new_owner> | CURRENT_USER | SESSION_USER }

ALTER FUNCTION <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   SET SCHEMA <new_schema>

ALTER FUNCTION <name> [ ( [ [<arg_mode>] [<arg_name>] <arg_type> [, ...] ] ) ]
   DEPENDS ON EXTENSION <extension_name>

where <action> can be:

    { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    { IMMUTABLE | STABLE | VOLATILE }
    { [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER }
    PARALLEL { UNSAFE | RESTRICTED | SAFE }
    COST <execution_cost>
    ROWS <result_rows>
    SET <config_param> { TO | = } { <value> | DEFAULT }
    SET <config_param> FROM CURRENT
    RESET <config_param>
    RESET ALL

Description​

To run ALTER FUNCTION, you must own the function that you want to redefine.

To change the schema of a function, you must have the CREATE privilege on the new schema.

To change the owner of a function, you must have the direct or indirect membership of the new owning role and have the CREATE privilege on the schema of the function.

Parameters​

  • <name>

    The name of the function. You can specify the name with the schema qualification. If the function contains no arguments, its name must be unique in the schema to which it belongs.

  • <arg_mode>

    The mode of the argument. Supported values include IN, OUT, INOUT, and VARIADIC. If this parameter is not specified, the default value IN is used. You do not need to list OUT arguments because ALTER FUNCTION uses only input arguments to identify the function.

  • <arg_name>

    The name of the argument.

    Note that ALTER FUNCTION uses only the data types of arguments to identify a function, instead of the argument names.

  • <arg_type>

    The data type of the argument.

  • <action>

    The action to perform on the function. Supported actions include:

    • CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT: determines whether a function can be invoked if it has null arguments. CALLED ON NULL INPUT indicates yes, and RETURNS NULL ON NULL INPUT or STRICT indicates no.

      For more information, see CREATE FUNCTION.

    • IMMUTABLE, STABLE, or VOLATILE: specifies the volatility of the function.

      For more information, see CREATE FUNCTION.

    • [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER: specifies whether the function is a security definer. EXTERNAL can be ignored.

      For more information, see CREATE FUNCTION.

    • PARALLEL: specifies whether the function is considered safe to execute in parallel.

      For more information, see CREATE FUNCTION.

    • COST: specifies the estimated execution cost of the function.

      For more information, see CREATE FUNCTION.

    • SET or RESET: specifies the <value> of the configuration parameter <config_param> when the function is invoked. If the value is DEFAULT for the SET option or the RESET option is used, the function-wide setting is dropped and uses the value in the environment instead. If you specify SET ... FROM CURRENT, the current value of the parameter when you run ALTER FUNCTION is used as the value for the function. If you want to clear all function-specific settings, use RESET ALL.

  • RESTRICT

    Ignored to keep conform to the SQL standard.

  • <new_name>

    The new name of the function.

  • <new_owner>

    The new owner of the function.

  • <new_schema>

    The new schema to which the function belongs.

  • <extension_name>

    The name of the extension on which the function depends.

Examples​

Rename function countall for type text to count_all:

ALTER FUNCTION countall(text) RENAME TO count_all;

Alter the owner of function count_all for type text to [email protected]:

ALTER FUNCTION count_all(text) OWNER TO "[email protected]";

Change the schema of function count_all for type text to public:

ALTER FUNCTION count_all(text) SET SCHEMA public;

Mark function count_all for type text as being dependent on exension gp_exttable_fdw:

ALTER FUNCTION count_all(text) DEPENDS ON EXTENSION gp_exttable_fdw;

Change the search path for function count_all of type text to DEFAULT:

ALTER FUNCTION count_all(text) SET search_path to DEFAULT;

SQL standard compatibility​

ALTER FUNCTION in Relyt is partially compatible with ALTER FUNCTION in the SQL standard. Standard SQL ALTER FUNCTION allows you to modify more properties of a function than ALTER FUNCTION in Relyt. However, ALTER FUNCTION in Relyt allows you to alter the name, owner, schema, and volatility of a function, change the defaults of configuration parameters for a function, and make a function a security definer. In addition, keyword RESTRICT is mandatory in the SQL standard but can be omitted in Relyt.