Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ ALTER FUNCTION
Updated on 2025-07-22 GMT+08:00
View PDF
Share

ALTER FUNCTION

Function

ALTER FUNCTION modifies the attributes of a customized function.

Precautions

  • Only the function owner or a user granted the ALTER permission can run ALTER FUNCTION. System administrators have this permission by default.
  • To change the function owner, you must be a direct or indirect member of the new owning role.
  • To change the function owner, you must have CREATE permission on the function's schemas.
  • To change a function's schema, you must also have the CREATE permission on the new schema.
  • If a function involves operations on temporary tables, ALTER FUNCTION cannot be used.

Syntax

  • Modify the additional parameter of the custom function. 
    1
2
    		 
    ALTER FUNCTION function_name ( [ { [ argmode ] [ argname ] argtype} [, ...] ] )
    action [ ... ] [ RESTRICT ];

    The syntax of the action clause is as follows:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    {CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT}
 | {IMMUTABLE | STABLE | VOLATILE}
 | {SHIPPABLE | NOT SHIPPABLE}
 | {NOT FENCED | FENCED}
 | {MASKED | NOT MASKED}
 | [ NOT ] LEAKPROOF
 | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
 | AUTHID { DEFINER | CURRENT_USER }
 | COST execution_cost
 | ROWS result_rows
 | SET configuration_parameter { { TO | = } { value | DEFAULT }| FROM CURRENT}
 | RESET {configuration_parameter | ALL}
  • Rename a custom function. You can add the schema name before the new function name. However, you cannot change the schema name at the same time. 
    1
2
3
4
    		 
    ALTER FUNCTION funname ( [ { [ argmode ] [ argname ] argtype} [, ...] ] )
    RENAME TO new_name;
ALTER FUNCTION funname ( [ { [ argmode ] [ argname ] argtype} [, ...] ] )
    RENAME TO schema.new_name;
  • Modify the owner of the custom function. 
    1
2
    		 
    ALTER FUNCTION funname ( [ { [ argmode ] [ argname ] argtype} [, ...] ] )
    OWNER TO new_owner;
  • Modify the schema of the custom function. 
    1
2
    		 
    ALTER FUNCTION funname ( [ { [ argmode ] [ argname ] argtype} [, ...] ] )
    SET SCHEMA new_schema;

Parameter Description

Table 1 ALTER FUNCTION parameters

Parameter

Description

Value Range

function_name

Specifies the function name to be modified.

Name of an existing function.

argmode

Specifies the mode of an argument. It indicates whether a parameter is an input, output, or input/output parameter.

IN, OUT, or IN OUT.

The default value is IN.

argname

Specifies the parameter name.

A string compliant with the identifier naming rules.

argtype

Specifies the parameter type of a function.

Valid types are listed in Data Types.

new_name

Specifies the new name of a function.

A string compliant with the identifier naming rules.

new_owner

Specifies the new owner of a function.

Valid username or role name.

new_schema

Specifies the new schema of a function.

Existing schema.

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  • CALLED ON NULL INPUT declares that some parameters of a function can be invoked in normal mode if the parameter values are NULL. By default, the usage is the same as specifying the parameters.
  • STRICT indicates that the function always returns NULL whenever any of its parameters are NULL. If this parameter is specified, the function is not called when there are NULL parameters; instead a NULL result is returned automatically.
  • The usage of RETURNS NULL ON NULL INPUT is the same as that of STRICT.

-

IMMUTABLE | STABLE | VOLATILE
  • IMMUTABLE indicates that the function always returns the same result if the parameter values are the same.
  • STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same parameter values, but that its result varies by SQL statements.
  • VOLATILE indicates that the function value can change in one table scanning and no optimization is performed.

For details about IMMUTABLE, STABLE, and VOLATILE, see Three GaussDB(DWS) Function Types Based on Behaviors.

SHIPPABLE | NOT SHIPPABLE

Indicates whether the function can be pushed down to DNs for execution.

  • Functions of the IMMUTABLE type can always be pushed down to the DNs.
  • Functions of the STABLE or VOLATILE type can be pushed down to DNs only if their attribute is SHIPPABLE.

-

MASKED | NOT MASKED

Specifies whether the function performs masking when the GUC parameter enable_redactcol_computable is enabled. MASKED supports system function modification and cannot be used together with actions in other ALTER functions.

-

LEAKPROOF

Indicates that the function has no side effect and specifies that the parameter includes only the returned value. LEAKPROOF can be set only by the system administrator.

-

EXTERNAL (optional)

Used d for SQL compatibility. This function applies to all functions, including external ones.

-

SECURITY INVOKER/AUTHID CURREN_USER

Declares that the function will be executed according to the permission of the user that invokes it. By default, the usage is the same as specifying the parameters.

SECURITY INVOKER and AUTHID CURREN_USER have the same functions.

-

SECURITY DEFINER/AUTHID DEFINER

Specifies that the function is to be executed with the permissions of the user that created it. By default, the usage is the same as specifying the parameters.

The usage of AUTHID DEFINER is the same as that of SECURITY DEFINER.

-

COST execution_cost

A positive number giving the estimated execution cost for the function.

The unit of execution_cost is cpu_operator_cost.

Positive

ROWS result_rows

Estimates the number of rows returned by the function. This is only allowed when the function is declared to return a set.

A positive number. The default value is 1000.

configuration_parameter
  • value

    Sets a specified database session parameter to a specified value.

    • DEFAULT or RESET indicates that the default settings of the system are used in a new session.
    • OFF indicates that the setting is disabled.

    Specifies the default value.

  • from current

    Uses the value of configuration_parameter of the current session.

A string

Examples

Create the func_add_sql2 function for calculating the sum of two integers and get the results. If the input is null, null will be returned.

1
2
3
4
5
6
7
8
 
DROP FUNCTION IF EXISTS func_add_sql2; 
CREATE FUNCTION func_add_sql2(num1 integer, num2 integer) RETURN integer
AS
BEGIN 
RETURN num1 + num2;
END;
/
;

Alter the execution rule of the func_add_sql2 function to IMMUTABLE. That is, the same result is returned if the parameter is not changed.

1
 
ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) IMMUTABLE;

Change the name of the func_add_sql2 function to add_two_number.

1
 
ALTER FUNCTION func_add_sql2(INTEGER, INTEGER) RENAME TO add_two_number;

Change the owner of the add_two_number function to dbadmin.

1
 
ALTER FUNCTION add_two_number(INTEGER, INTEGER) OWNER TO dbadmin;

Helpful Links

CREATE FUNCTION and DROP FUNCTION

Parent Topic: DDL Syntax