ALTER FUNCTION

Modify the definition of the function.

Summary

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:

{CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}
{IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF}
{[EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER}
EXECUTE ON { ANY | MASTER | ALL SEGMENTS | INITPLAN }
COST execution_cost
SET configuration_parameter { TO | = } { value | DEFAULT }
SET configuration_parameter FROM CURRENT
RESET configuration_parameter
RESET ALL

describe

ALTER FUNCTION Modify the definition of the function.

You must be the owner of the function to use ALTER FUNCTION. In order to modify the function's mode, you must also have CREATE permissions in the new mode. In order to modify the owner, you must also be a direct or indirect member of the new role, and that role must have CREATE permissions in the function mode. These restrictions enforce changes to the owner will not do anything you can't do by deleting and recreating the function, however, superusers can change ownership of any function anyway.

Parameters

name

The name of an existing function (probably pattern-qualified).

argmode

Parameter mode: IN, OUT, INOUT or VARIADIC. If omitted, the default value is IN. Note that ALTER FUNCTION does not actually focus on the OUT parameter, because only the input parameters are required to determine the identity of the function. Therefore it is sufficient for listing only IN, INOUT and VARIADIC parameters

argname

The name of the parameter. Note that ALTER FUNCTION does not actually care about parameter names, because only parameter data types are required to determine the identity of the function.

argtype

Data type of function parameters (if any) (can be scheme-qualified).

new_name

The new name of the function.

new_owner

New owner of the function. Note that if the function is marked SECURITY DEFINER, it will then be executed as the new owner.

new_schema

New mode for this function.

CALLED ON NULL INPUT

RETURNS NULL ON NULL INPUT

STRICT

CALLED ON NULL INPUT Change this function to be called when some or all parameters are null. RETURNS NULL ON NULL INPUT or STRICT Change the function so that if any of its arguments are empty, the function will not be called; instead, an empty result is automatically assumed. See CREATE FUNCTION for more information.

IMMUTABLE

STABLE

VOLATILE

Change the volatility of the function to the specified setting. See CREATE FUNCTION for more information.

[ EXTERNAL ] SECURITY INVOKER

[ EXTERNAL ] SECURITY DEFINER

Change whether the function is a security definer. Keyword EXTERNAL is ignored for SQL consistency. See CREATE FUNCTION for more information about this feature.

LEAKPROOF

Change whether the function is considered leak-proof. See CREATE FUNCTION for more information about this feature.

EXECUTE ON ANY

EXECUTE ON MASTER

EXECUTE ON ALL SEGMENTS

EXECUTE ON INITPLAN

EXECUTE ON property specifies the location (main instance or segment instance) when the function is called during query execution.
EXECUTE ON ANY (default) means that the function can be executed on the main server or any segment instance and it returns the same result regardless of where the execution is performed. The YMatrix database determines where the function is executed.
EXECUTE ON MASTER means that the function must be executed only on the main instance.
EXECUTE ON ALL SEGMENTS means that for each call, the function must be executed on all main segment instances, not on the main node. The overall result of this function is the UNION ALL result from all segment instances.
For more information about the EXECUTE ON property, see CREATE FUNCTION.
EXECUTE ON INITPLAN means that the function contains SQL statements that need to be distributed to the Segment node to execute and requires special processing by Master if possible.

COST execution_cost

Change the estimated execution cost of this function. See CREATE FUNCTION for more information.

configuration_parameter

value

When the function is called, an additional or altered assignment is made to a configuration parameter. If value is DEFAULT or equivalent R ESET is used, the local settings of the function will be removed so that the function will be executed using values present in its environment. Use RESET ALL to clear local settings of all functions. SET FROM CURRENT The current value of the application session when the function is entered.

RESTRICT

Ignore the SQL standard.

Notice

The YMatrix database has restrictions such as STABLE or VOLATILE for certain defined functions. See CREATE FUNCTION for more information.

Example

Rename function sqrt of type integer to square_root:

ALTER FUNCTION sqrt(integer) RENAME TO square_root;

Change the owner of the sqrt function of integer type to joe

ALTER FUNCTION sqrt(integer) OWNER TO joe;

Change the mode of function sqrt of type integer to math:

ALTER FUNCTION sqrt(integer) SET SCHEMA math;

To adjust the automatic search path of a function:

ALTER FUNCTION check_password(text) RESET search_path;

compatibility

This statement is partially compatible with the ALTER FUNCTION statement in the SQL standard. This standard allows modifying more properties of a function, but does not provide functions such as renaming a function, marking a function as a security definer, attaching configuration parameter values to a function, or changing the owner, pattern, or stability of a function. This standard also requires the RESTRICT keyword, which is optional in the YMatrix database.